How to Perform MySQL Master Slave Replication in 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.

What is Master-Slave Replication?

Master-slave replication is a data replication technique used in databases to distribute and synchronize data across multiple servers. In this setup:

• Master Server: The primary database where all changes, such as write, update, and delete operations, occur. It is responsible for processing the original data.
• Slave Servers: One or more secondary databases that replicate the data from the master server. These servers receive a copy of the data and are kept in sync with the master.

Key Features:

• Read/Write Separation: Typically, the master server handles all the write operations, while the slave servers handle read operations. This improves system performance by distributing the workload.
• Failover: In case the master server fails, a slave can be promoted to master to ensure system availability.
• Data Consistency: Slaves are continuously updated with the changes from the master, ensuring data consistency across the system.

Master-slave replication is widely used for load balancing, high availability, and backup purposes in large-scale applications.

How to Achieve MySQL Master-Slave Replication?

For this MySQL master-slave tutorial, we will call master as root@repl-master and slave as root@repl-slave.

For this MySQL replication setup, 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

The 7 Steps To Achieve MySQL Master Slave Replication are given below:

1. Setting Up The Master
2. Create A New User For Slave
3. Move Data From Master To Slave
4. Configure Slave Server
5. Import Data Dump
6. Start Slave Server
7. Test MySQL Master Slave Replication

Step 1. Setting Up The Master

The first thing you need to accomplish in the MySQL master-slave 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

Read through Installing MySQL on Ubuntu 20.04: 6 Easy Steps for more insights.

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

Step 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.

Step 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;	

Step 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

Step 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.

Step 6. Start Slave Server

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

START SLAVE;

Step 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

Hevo Data, 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 in a cost-effective way.

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.

Advantages of MySQL Master-Slave Replication

• Data Redundancy and Reduced Downtime: The slave server gives redundancy to your data by maintaining a copy of it. If, in any case, the master server fails, slave servers can take over, ensuring continuous data access and minimizing server downtime.
• Load Balancing: The read-intensive queries can be distributed among multiple slave servers, which creates ample space for write operations to be carried out in the master server, boosting overall performance of the servers.
• Better Read Performance: Since the read queries are offloaded to multiple slave servers so that the master server can focus on write queries, this read and write separation optimizes the performance, especially in a read-heavy workload.
• Horizontal Scaling: The MySQL master-slave replication setup allows for horizontal scaling by adding more slave servers to the architecture. This scalability is useful in cases of increased read loads without altering the database system.
• Disaster Recovery: The slave servers act as hot backups. In the event of data loss or corruption on the master server, a slave server can be promoted to master, minimizing the impact and catalyzing the recovery process.

Additional Resources on MYSQL Master Slave Replication

• More about Replicate MYSQL Database

Conclusion

The above article has provided you with enough information about how to set up a MySQL master slave replication. Understanding MySQL Master-Slave Replication is essential, especially when planning to migrate data from platforms like Salesforce to MySQL to ensure data consistency.

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 configure many details manually. So, if you want to overlook this hardship and experience a hassle-free process, then give Hevo a try.

Here are some essentials for a deeper dive into MySQL Replication:

• MySQL Master Master Replication: 4 Easy Steps
• MySQL GTIDs Replication Set Up: 8 Easy Steps
• MySQL BinLog Based Replication: 2 Easy Methods

FAQ on MySQL Master Slave Replication