Set up Master-Slave replication in MySQL Server 5.7

In this guide, I will show you how you can set up master-slave replication in MySQL. However, Let’s first understand what exactly is MySQL and it’s capabilities.

MySQL is one of the most popular relational database management system. It is used on small applications like a personal time tracker to gigantic web applications like Wikipedia.

MySQL is a really powerful database management system that supports almost all the websites based on WordPress. By default, WordPress uses MySQL RDBMS to manage and operate on the data.

Some websites grow faster than expected, which means, we need more resources to run MySQL queries and to provide highest possible uptime. In any case, we can take advantage of Replication in MySQL Server 5.7.

In this guide, I will start with explaining the advantages of MySQL replication and then we will actually set up a Master-Slave replication in MySQL Server 5.7.

So, let’s see what benefits we can get by using Master-Slave MySQL replication.

Advantages of MySQL Replication

There are very few fundamental advantages that we get by using MySQL replication.

Scaling out

There are two ways to scale your application or database or frontend. Either provide more resources to MySQL on a same server by increasing the RAM, CPU cores and Storage, which is called scaling up.

And there is another way to scale, In which we create multiple servers and distribute the load on all the servers. This is the advantage we get when we replicate our MySQL server or MySQL database.

In Master-Slave replication, Master handles all the write operations like Create, Update and Delete. While Slave handles all the Read operations like Select.

This way, we can tell our application to Read from the Slave MySQL replication and Write on the Master.

Improved Performance

As we can distribute the load on both Master and Slave, we can get better overall performance. It is because Master has to handle only Write queries and Slave has to handle only Read queries.

Also, Read queries, if not so complex, uses less resources than Write queries. And as we are going to use Slave for Read queries, We can get data faster from the server to display it to the user.

So, these are some advantages of Master-Slave replication in MySQL. Now, we can get started with the set up process.

Prerequisites

As it’s a Master-Slave replication, we need two VPS(Virtual private server) or VM(Virtual machine). One will work as a master and another as a slave. We also need root access to both the servers as we have to update some lines in MySQL configuration file.

We have to work on two servers right now. I will indicate Master as “root@mysql-master:~#” and Slave as “root@mysql-slave:~#” so that you can avoid running commands on a wrong server.

If you have two servers and root access to both of them, we are good to go!

Set up Master-Slave replication in MySQL

I have divided this tutorial into simple and understandable steps. First of all, we have to configure the Master.

Configure the Master

Considering you already have MySQL installed on a Master server, we can get started by updating few lines in MySQL configuration file. However, if you do not have MySQL installed on a Master server, Execute the following commands to install MySQL on master.

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 the MySQL is installed on the server, Execute the following command to edit the MySQL configuration file.

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

Now, find a line containing bind-address and. It should look like:

bind-address = 127.0.0.1

And replace it with:

bind-address = MASTER_IP_ADDRESS

Next, find a line containing server-id and log_bin and uncomment both the lines. After performing this task, make sure both the lines look like the following two lines.

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

It’s time to create a MySQL user with replication privilege so that a slave user can replicate data on the slave server.

Create a user for Slave and Record Position

On Master, login 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, turn on the lock on your databases to prevent the change in data.

mysql> FLUSH TABLES WITH READ LOCK;

As the tables are now locked, we can record the current log position so that our Slave server can start reading data from that log position. To know the current status of master server, execute the following command in your MySQL interface.

mysql> SHOW MASTER STATUS;

You will see a table with single row just like following image.

MySQL Master status

Record this position as we still have to tell our slave where to start from. Now, we have to dump our MySQL databases to import them on a slave with master data.

Transfer Data from Master to Slave

Now, It’s time to move our current MySQL data to slave, after that, it can start replicating from the log position that we just recorded. To move our data from Master to Slave, we first have 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 from Master to Slave, we can use SCP command.

scp data.sql root@SLAVE_IP:~

To migrate the file, you will have to enter the SSH password of slave. After migration is done, Unlock tables on master.

mysql> UNLOCK TABLES;

Once migrated, we can move on to configuration of Slave. The hardest part of the tutorial is over, now we just have to configure our slave and test the replication.

Configure the Slave

First of all, we have to install MySQL on slave. You can use the same above given commands to install MySQL on slave. If you already have installed MySQL on slave, Open the MySQL configuration file to update some configuration.

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

Next, press CTRL+W, type bind-address and then hit the Enter key. Replace the IP address in bind-address directive from “127.0.0.1” to Slave’s IP address. The updated line should look like this.

bind-address = SLAVE_IP_ADDRESS

Now, find a line containing server-id, uncomment the line and set the server-id to “2”. Also, do not forget to update the line following “server-id” which is “log_bin” directive. Updated server-id and log_bin should look like this:

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

The next step is to import the database dump we created from the master.

Import the data dump

We have to import the data in slave. To import data, execute the following command on your slave server.

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

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

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

Now we can change the master so that our slave can know which server to replicate. Update 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;

Do not forget 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 master server. And you have LOG_FILE_NAME and LOG_FILE_POSITION from the Master server. I mentioned to record this information.

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

If you can enable the Slave, You are good to go! You have set up a master-slave replication in MySQL server 5.7. Now, It’s time to test the replication.

Test MySQL Replication

It is very easy to test if MySQL master-slave replication is working or not. We already know that slave will basically keep replicating the data from master.

We can test the replication by creating a test database on master server. As a result, it should appear automatically on slave. To create a test databases on master, Login to MySQL and execute the following command.

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

Now, Open up MySQL command line interface on your slave server and check if the new database exist. To get the list of database, execute the following command on slave.

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

In the list, you must see a database named “test”. If you can see the database, MySQL replication is working. If not, Replication is not working. In that case, you can follow the guide from start again.

So, this is how you can set up Master-Slave replication in MySQL server 5.7. If you are facing any issues following this guide, Please contact our support staff for help!

One Response to “Set up Master-Slave replication in MySQL Server 5.7”

  1. Stefan Andonov says:

    That is a good tutorial, thank you!

    Works in a case, where you sync all the databases, but still, good one, works with me 😉

Leave a Reply