To set up Master-Slave replication in MySQL Server 5.7

This tutorial will guide you through the process of setting up master-slave replication in MySQL. Before we dive into the setup, let’s first understand the concept of MySQL and its capabilities.

MySQL is a widely used relational database management system that ranges from small applications like personal time trackers to massive web applications like Wikipedia.

MySQL is a powerful database management system that supports a majority of WordPress-based websites. WordPress relies on MySQL RDBMS by default for data management.

In some cases, websites experience unexpected growth, requiring additional resources to handle MySQL queries and ensure high uptime. This is where MySQL Server 5.7 replication becomes advantageous.

In this guide, we will explore the benefits of MySQL replication and proceed to set up master-slave replication in MySQL Server 5.7.

Let us now explore the advantages of using MySQL replication.

Benefits of MySQL Replication

Using MySQL replication offers a few key benefits.

Scalability

Scaling an application or database frontend can be achieved in two ways. One approach is to allocate more resources to MySQL on a single server, such as increasing RAM, CPU cores, and storage. This method is known as scaling up.

Another option is to create multiple servers and distribute the workload among them. This is the advantage offered by MySQL server or MySQL database replication.

In master-slave replication, the master handles write operations like create, update, and delete, while the slave handles read operations like select.

By directing our application to read from the slave MySQL replication and write on the master, we can effectively scale our system.

Improved Performance

Distributing the workload between the master and slave allows for better overall performance. The master only needs to handle write queries, whereas the slave handles read queries.

Read queries typically require fewer resources compared to write queries, assuming they are not overly complex. Utilizing the slave for read queries enables faster data retrieval from the server for display to the user.

These are some advantages of implementing master-slave replication in MySQL. Now, let’s proceed with the setup process.

Prerequisites

Since this is a master-slave replication, we will need two virtual private servers (VPS) or virtual machines (VM). One will function as the master, and the other as the slave. Additionally, root access to both servers is required to update certain lines in the MySQL configuration file.

Throughout this process, we will work on two servers. The master server will be referenced as “root@mysql-master:~#”, and the slave server as “root@mysql-slave:~#”. This way, you can avoid running commands on the wrong server.

If you have two servers with root access, we are ready to proceed.

Setting up Master-Slave Replication in MySQL

This tutorial is divided into simple steps for easy comprehension. Let’s begin by configuring the master.

Configuring the Master

Assuming you already have MySQL installed on the master server, we can start by updating a few lines in the MySQL configuration file. If MySQL is not yet installed on the master server, execute the following commands to install it.

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

Once MySQL is installed, execute the following command to edit the MySQL configuration file.

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

Locate the line that contains bind-address. It should resemble the following:

bind-address = 127.0.0.1

Replace it with:

bind-address = MASTER_IP_ADDRESS

Next, find the lines containing server-id and log_bin and uncomment both lines. After completing this task, ensure that both lines resemble the following examples.

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

Once done, press CTRL+X, followed by Y, and restart the MySQL service.

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

Now, let’s create a MySQL user with replication privileges to allow the slave to replicate data on the slave server.

Creating a User for the Slave and Recording Position

On the master server, log in to MySQL and execute the following commands to create a user.

root@mysql-master:~# mysql -uroot -p;
mysql> CREATE USER ‘slave’@’SLAVE_IP_ADDRESS‘ IDENTIFIED BY ‘SLAVE_PASSWORD‘;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’SLAVE_IP_ADDRESS‘;
mysql> FLUSH PRIVILEGES;

Next, lock your databases to prevent any data changes.

mysql> FLUSH TABLES WITH READ LOCK;

With the tables locked, we can now record the current log position. The slave server will start reading data from this log position. To determine the current master server status, execute the following command in your MySQL interface.

mysql> SHOW MASTER STATUS;

A table with a single row will appear, resembling the image below.

Record this position as we will need to specify where the slave should start from. We also need to dump our MySQL databases to import them onto the slave with master data.

Transferring Data from Master to Slave

Now, it’s time to transfer the existing MySQL data to the slave. Following this transfer, the slave can begin replicating from the recorded log position. To move our data from the master to the slave, we first need to dump it into an SQL file.

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

To transfer this database dump file from the master to the slave, use the SCP command.

scp data.sql root@SLAVE_IP:~

During migration, you will be prompted to enter the SSH password for the slave. Once the migration is complete, unlock the tables on the master.

mysql> UNLOCK TABLES;

After successful migration, we can move on to configuring the slave. The most challenging part of the tutorial is behind us. Now, we just need to configure the slave and test the replication.

Configuring the Slave

First, we need to install MySQL on the slave. The same commands provided earlier can be used to install MySQL on the slave. If MySQL is already installed on the slave, open the MySQL configuration file to update some configurations.

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

Next, press CTRL+W, type bind-address, and hit Enter. Replace the IP address in the bind-address directive from “127.0.0.1” to the slave’s IP address. The updated line should resemble the following.

bind-address = SLAVE_IP_ADDRESS

Now, locate the line containing server-id, uncomment it, and set the server-id to “2”. Additionally, update the line following “server-id,” which is the log_bin directive. The updated server-id and log_bin should resemble the following:

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

Restart the MySQL server using the following command.

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

Next, we need to import the database dump we created from the master.

Importing the Data Dump

To import the data dump into the slave, execute the following command on your slave server.

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

Once the data is imported, log in to MySQL on the slave and stop the slave using the following command.

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

Now, we can change the master settings so that the slave knows which server to replicate from. Update the master information using the following command in MySQL.

mysql> CHANGE MASTER TO MASTER_HOST=’MASTER_IP‘, MASTER_USER=’slave’, MASTER_PASSWORD=’SLAVE_PASSWORD‘, MASTER_LOG_FILE=’LOG_FILE_NAME‘, MASTER_LOG_POS=LOG_FILE_POSITION;

Remember to replace the variables in this command. MASTER_IP is the IP address of the master server, SLAVE_PASSWORD is the password you set for the slave user on the master server, and you obtained the LOG_FILE_NAME and LOG_FILE_POSITION from the master server.

After executing the command, start the slave by using the START SLAVE command on the slave server.

If the slave is successfully enabled, you are good to go! You have established master-slave replication in MySQL Server 5.7. Now, it’s time to test the replication.

Testing MySQL Replication

Testing whether MySQL master-slave replication is functioning is straightforward. Since the slave replicates data from the master, we can create a test database on the master server, which should automatically appear on the slave. To create a test database on the master, log in to MySQL and execute the following command.

root@mysql-master:~# mysql -uroot -p;
mysql> CREATE DATABASE test;

Next, open the MySQL command line interface on your slave server and check if the new database exists. To retrieve the list of databases, execute the following command on the slave.

root@mysql-slave:~# mysql -uroot -p;
mysql> SHOW DATABASES;

In the list, you should see a database named “test.” If the database appears, MySQL replication is functioning correctly. If not, replication is not working. In that case, you can restart the guide from the beginning.

This is how you can establish master-slave replication in MySQL Server 5.7. If you encounter any difficulties while following this guide, please contact our support staff for assistance!