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