MySQL Master Slave Replication: 7 Easy Steps

MySQL replication, specifically, MySQL master slave replication plays a vital role in ensuring data availability by enabling simultaneous copying and replication of data between servers. The MySQL master slave replication proves indispensable for data recovery, offering a reliable backup solution in the face of catastrophes or hardware failures.

This article offers an extensive exploration of the MySQL master slave replication process, providing a comprehensive step-by-step guide to help you successfully implement and achieve replication. Gain insights into the intricacies of MySQL replication setup and equip yourself with the knowledge to safeguard your data effectively.

Table of Contents

• What Is MySQL?
• What Is Master Slave Replication?
• Purpose of Master Slave Replication
• Prerequisites
• Steps to Achieve MySQL Master Slave Replication
• Using Hevo’s No-code Data Pipeline for Seamless MySQL Replication
• Conclusion

What Is MySQL?

MySQL is one of the most popular and widely used open-source RDBMS (Relational Database Management System). MySQL is available for free under the GNU public license and is also available as a premium proprietary version. MySQL was originally developed by Michael Widenius at MySQL AB, a Swedish-based company. In 2012, Sun Microsystems acquired MySQL AB, and later Oracle acquired Sun Microsystems.

MySQL is used for a variety of applications and is mainly built around SQL (Structured Query Language). MySQL is widely used in applications built using PHP. It is also being used by some of the popular websites, including Twitter, Facebook, Mediawiki, YouTube and Flickr, etc.

To gain a deeper understanding of MySQL’s high availability, we recommend consulting our comprehensive guide, MySQL High Availability, and Replication. Additionally, for insights into the simplified MySQL data load process, we have a helpful guide dedicated to explaining and streamlining the MySQL load data procedure.

What Is Master Slave Replication?

The master-slave replication process enables database administrators to replicate or copy data stored in more than one server simultaneously. This helps the database administrator to create a live backup of the database all the time. During some situations, when the master-slave is down to any issues, they can instantly switch over the slave database and keep the application up and running.

This is one of the main advantages of using MySQL master slave replication. It provides better scalability and availability for your application. By using MySQL master slave multiple servers, you can distribute the read load across multiple servers, improving the overall performance of your application. Additionally, if the master server goes down, you can quickly promote one of the slave servers to become the new master, ensuring that your application continues to function without any downtime.

In this replication, there are various types of replication processes. You can have a single master and multiple slaves or multiple masters and multiple slaves, etc.

In this process, it is always a single or one-way transmission of data. The data is stored in the master first and then copied on to the slaves. Hence, the write operation is performed only on the master database. The read operation is done in both the master and the slave. The slaves can also be used for data accessibility to reduce the load on the master database.

Purpose of Master Slave Replication

One of the main purposes of going for a master-slave replication system is to have a standby system with a live backup that can be promoted as the master when the original master server crashes. Apart from this, there are several benefits as outlined below:

• Scalability: All query requests to the database can be routed to multiple database servers to reduce the load on the server and allow faster accessibility. Most of the web applications and sites that you encounter nowadays come loaded with more read operations than write operations to the database. Hence, website administrators need to provide the perfect setup for the quick loading of details on the website. 
• Performance: All database write operations are done on the master database. Once these changes are made to the master database, they get updated from the master to the slave. But the read requests from websites can be shared across multiple slaves to increase the performance of the website.
• Backup: You can just replicate the latest snapshot of the database to another database and create a backup in just a couple of minutes. Data corruption is largely reduced as the master server runs without any issues and provides 99.9% Uptime. This allows applications to process large amounts of reading or write operations without any hassle.
• Analytics and Benchmarking: This process allows database analysts to run all kinds of data analysis tests and experiments on the slaves without disturbing the master.

Prerequisites

To set up a MySQL master slave replication, you need to have the following:

• 2 VM (Virtual Machine) or VPS (Virtual Private Server) with root access.
• Working Internet.

Steps to Achieve MySQL Master Slave Replication

For this demo purpose, you will be calling master as root@repl-master and slave as root@repl-slave.

For this demo, let’s assume the IP address for the master and slave are as follows:

Master server: 12.34.56.111
Slave server: 12.23.34.222

1. Setting Up The Master

The first thing you need to accomplish in the replication process is to install and configure the master server. If you have not installed MySQL, then you can install MySQL using the following command:

 root@repl-master:~# sudo apt-get update
 root@repl-master:~# sudo apt-get install mysql-server mysql-client -y
 root@repl-master:~# sudo mysql_secure_installation

Once the MySQL installation process is completed, use the following command to edit the MySQL configuration file:

root@repl-master:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Next, in the same file, find the line containing bind-address = 127.0.0.1 and replace that IP address with the IP address of your master replication server. So, the line will look like:
bind-address = 12.34.56.111

Next, find the following lines in the file:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

You will see that the above lines have been commented, just uncomment these lines and exit the edit interface by clicking CTRL + X. Save the changes and restart the MySQL service for the changes to take effect.

Restart MySQL service using the following command:

root@repl-master:~# sudo service mysql restart

2. Create A New User For Slave

The next step is to create a new user for your slave server. Use the following command to create it:

root@repl-master:~# mysql -uroot -p;
mysql> CREATE USER ‘slave’@’12.34.56.789‘ IDENTIFIED BY ‘SLAVE_PASSWORD‘;
mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@’12.34.56.222 ‘;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;

You will use the following command to know the current status of the master server:

mysql> SHOW MASTER STATUS;

This command will also tell the slave to follow the master from this position.

3. Move Data From Master To Slave

Now that you have marked the position, you can start moving the data from the master to the slave. You need to create a MySQL dump file to move the data. Use the following command to create the dump file:

root@repl-master:~# mysqldump -u root -p –all-databases –master-data > data.sql

To copy the dump file to the slave, use the following command:

scp data.sql root@12.34.56.222

Unlock the tables using the following command:

mysql> UNLOCK TABLES;	

4. Configure Slave Server

Now, all you need to do is configure the slave server and test if replication is working. Ensure MySQL is installed.
Open the configuration file in your slave server and update these lines:

root@repl-slave:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the same way that you did for the master server, you need to bind the IP address and uncomment those two lines for the slave server.
Now, restart the MySQL server using the following command:

root@repl-slave:~# sudo service mysql restart

5. Import Data Dump

Use the following command to import the dump file to the slave server:

root@repl-slave:~# mysql -uroot -p < data.sql

Once the data is imported, you need to stop MySQL in the slave server using the following command:

root@repl-slave:~# mysql -uroot -p;
mysql> STOP SLAVE;

You have finally imported the dump files and updated the master IP address, password, log file name, and position, to enable the master to communicate with the slave without any issues.

6. Start Slave Server

Next, use the “Start Slave” command to start operating the slave server.

START SLAVE;

7. Test MySQL Master Slave Replication

To test if your MySQL master slave replication works, just create a database in your master server and see if it is replicated in the slave server. If you can see the database in the slave, then it is working fine.

Create a test database in a master server called ‘sampledb’.

CREATE DATABASE sampledb;

Now login to your slave server and list the databases, and if you see the “sampledb” there, then the master slave replication process is working fine.

Login to your slave server and use the following command to list all databases:

show databases;

Using Hevo’s No-code Data Pipeline for Seamless MySQL Replication

Hevodata, an automated no-code data pipeline provides a hassle-free solution and helps you replicate your data from MySQL to any database, data warehouse, or destination of your choice within minutes. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Hevo’s pre-built integration with MySQL and 150+ data sources across databases, files, analytics engines, etc. gives users the flexibility to bring in data of all different kinds in a way that’s as smooth as possible, without having to write a single line of code. Hevo takes full charge of your data transfer process and lets you focus your engineering bandwidth on key business activities.

More reasons to love Hevo:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from MySQL files and maps it to the destination schema.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Pre and Post-load Transformations: Using Transformations in Hevo, you can prepare the data in multiple ways before loading it to the Destination. Hevo offers numerous data transformations including Cleansing, Re-expression, Data Filtering, Normalization, etc.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

With continuous real-time data movement, load your data from MySQL to your destination warehouse with Hevo’s easy-to-setup and No-code interface. Try our 14-day full-access free trial.

Get Started with Hevo for free

Conclusion

The above article has provided you with enough information about how to set up a MySQL master slave replication. The step-by-step guide is specially provided to help beginners understand the process and start testing the replication process in their environment. But this process requires extensive hard work as you have to manually configure many details. So, if you want to overlook this hardship and experience a hassle-free process, then give Hevo a try.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. You can leverage Hevo to seamlessly replicate your MySQL data in real time without writing a single line of code.

Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure manner. Hevo caters to 150+ data sources (including 50+ free sources) and can directly transfer data to Data Warehouses, Business Intelligence Tools, or any other destination of your choice in a hassle-free manner. It will make your life easier and make data migration hassle-free.

Learn more about Hevo

You can post your experience and comments about the MySQL master slave replication process in the comment section below.