How to Setup MySQL Master Slave Replication on Ubuntu 18.04

Simon Bennett · Mar 10, 2020

If you are a Database administrator and responsible for managing the MySQL server, you must have a knowledge of the replication concept as this will helps you in terms of Data Security, Fail-over Solution and Database Backup from Slave.

MySQL replication is the process that enables data from one MySQL server to be copied to one or more MySQL servers. By default, replication is asynchronous by default and slaves do not need to be connected permanently to receive updates from the master. It is primarily used to read access on multiple servers for scalability and also used for failover.

In Master-Slave replication, the Master server is responsible for write operations while read operations are spread on multiple slave servers. The Master server saves database modification in binary log and Slaves request the database modification when they are ready.

In this tutorial, we will show you how to setup MySQL Master Slave replication on Ubuntu 18.04 server.

Prerequisites

Two servers running Ubuntu 18.04.
A static IP address 45.58.32.120 is configured on the Master server and 104.245.36.31 is configured on the Slave server.
A root password is configured on both servers.

Install MySQL Server

First, you will need to install the MySQL server package on both the Master and Slave server. You can install it by running the following command on both servers:

apt-get update -y

apt-get install mysql-server -y

After installing the MySQL server, start MySQL service and enable it to start after system reboot with the following command:

systemctl start mysql

systemctl enable mysql

Next, it is a good idea to secure your MySQL install and set your MySQL root password. You can do it with the following command:

mysql_secure_installation

Answer all questions as shown below:

Press y|Y for Yes, any other key for No: Just press Enter Please set the password for root here. New password: Re-enter new password: Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

Configure the Master Server

By default, MySQL server is configured to listen on localhost. So you will need to configure it to listen on the private IP address, set a unique ID and enable the binary logging. You can do it by editing the file /etc/mysql/mysql.conf.d/mysqld.cnf

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the following lines:

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

Save and close the file when you are finished. Then, restart the MySQL service to implement the changes.

systemctl restart mysql

Next, you will need to create a replica user and grant the REPLICATION SLAVE privilege to the user.

First, login to MySQL shell with the following command:

mysql -u root -p

Provide your root MySQL password when prompt then run the following command to create a replica user and grant replication slave privileges to the user:

mysql> CREATE USER 'replica'@'104.245.36.31' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'104.245.36.31';

Next, check the status of the Master server with the following command:

mysql> SHOW MASTER STATUS\G

You should get the following output:

*************************** 1. row *************************** File: mysql-bin.000001 Position: 625 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec)

Please note down mysql-bin.000001 and 625 from the above output as you will need it on the Slave server.

Finally, exit from the MySQL shell with the following command:

mysql> EXIT;

Configure the Slave Server

Next, login to the Slave server and configure MySQL server to listen on the private IP address, set a unique ID and enable the binary logging.

You can do it by editing the following file:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Make the following changes:

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

Save and close the file when you are finished. Then, restart the MySQL service to apply the changes:

systemctl restart mysql

Connect Slave Server to the Master Server

At this point, the Master server is configured for replication. It's time to connect the Slave server to the Master server.

Go to the Slave server and login to the MySQL shell with the following command:

mysql -u root -p

Provide your MySQL root password when prompt then stop the Slave threads with the following command:

mysql> STOP SLAVE;

Next, set up the slave server to replicate the master server with the following command that match with the master log file and log position:

mysql> CHANGE MASTER TO MASTER_HOST='45.58.32.120', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=625;

45.58.32.120 : Ip address of the Master server.

replica : MySQL user of the Master server.

password : MySQL replica user password of the Master server.

mysql-bin.000001 : Master log file of the Master server.

625 : Master log position of the Master server.

Next, activate the slave server with the following command:

mysql> START SLAVE;

You can also check the Slave status with the following command:

mysql> show slave status\G

You should see the following screen:

Finally, exit from the MySQL shell with the following command:

mysql> EXIT;

Verify Master Slave Replication

At this point, the Master and the Slave server are configured to replicate each other. It's time to test whether the replication is working or not.

On the Master server, login to the MySQL shell with the following command:

mysql -u root -p

Provide your root password and create a database with name testdb;

mysql> create database testdb;

Next, verify the database with the following command:

mysql> show databases;

You should see the following output:

+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+

On the Slave server, login to the MySQL shell with the following command:

mysql -u root -p

Provide your root password then run the following command to list all databases:

mysql> show databases;

You should see the testdb database which you have created on the Master server is replicated on the Slave server:

+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+

Conclusion

Congratulations! you have successfully set up MySQL Master-Slave replication on Ubuntu 18.04 server. Now all databases on the Master server will be replicated automatically to the Slave server. For more information, read the MySQL official replication documentation.