How To Set Up MySQL Master Slave Replication Easily?

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. This replication proves indispensable for data recovery, offering a reliable backup solution for catastrophes or hardware failures.

This article extensively explores the replication process, providing a comprehensive step-by-step guide to help you successfully implement and achieve replication.

What Is MySQL Replication?

Replication in MySQL is the process of copying data from one database server (master) to another (slave). The master server handles all the write operations, while the slave replicates these changes to maintain an identical copy of the data. This setup enhances data availability, load balancing, and disaster recovery. MySQL supports different replication types, including asynchronous, semi-synchronous, and group replication, allowing flexibility based on use cases.

What Is MySQL 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 Server(s): 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: If 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.

Why Is Master-Slave Replication Important?

1. High Availability: Ensures data remains accessible even if the master server fails by allowing read access from the slave server.
2. Load Balancing: Distributes read operations to the slave, reducing the load on the master server and improving performance.
3. Backup Support: Enables backups from the slave without affecting the master server’s operations.
4. Disaster Recovery: Provides a failover mechanism to quickly recover data in case of server crashes.
5. Scalability: Facilitates scaling by adding more slave servers to handle increased traffic.

How To Set Up MySQL Master Slave Replication?

Prerequisites

• Two servers running Ubuntu 20.04. Both should have a non-root administrative user with sudo Privileges and a firewall configured with UFW. Follow our initial server setup guide for Ubuntu 20.04 to set up both servers.
• MySQL installed on each server. This guide assumes that you’re using the latest version of MySQL available from the default Ubuntu repositories which, as of this writing, is version  8.0.25. To install this on both servers, follow our guide on How To Install MySQL on Ubuntu 20.04.

For this tutorial, let’s assume the master is called root@repl-master and the slave is called root@repl-slave.

For this 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

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 step 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

You can also 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 also tells 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 now 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 Replication Master Slave

To test if your MySQL master slave replication works, just create a database on your master server and see if it is replicated on 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.

Configure MySQL Source To Replicate Your Data

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.

Advantages of MySQL Master Slave Replication

• Data Redundancy and Reduced Downtime: The slave server keeps a backup of your data. If the master server fails, the slave server can take over, ensuring uninterrupted access and minimizing 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 the 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 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.

Conclusion

This article provided you with the steps on how to set up MySQL master-slave replication. Understanding this 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 manually configure many details. 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

Sign up for a free 14-day trial to streamline your data integration process. You may examine Hevo’s pricing plans and decide on the best plan for your business needs.

FAQs