How to Configure MySQL Master-Slave Replication on Ubuntu 20.04

MySQL master-slave replication is a process that allows database files to be replicated one or more server in a network. This setup provides redundancy and fault tolerance such that in the event of a failure in the Master node, the databases can still be recovered in the Slave Server.

We will use two servers (server 1 as known as Master and server 2 as known as Slave with the IP addresses of 192.168.100.111 and 192.168.100.122

Step 1) Install MySQL Server On Server 1 (Master)

Next, installing MySQL server on server 1 with IP address 192.168.100.111

$ sudo apt-get update
$ sudo apt-get install mysql-server

Step 2) Edit MySQL Configuration File on Server 1 (Master)

Next, edit the MySQL configuration file
and replace bind-address = 127.0.0.1 Change to bind-address = 0.0.0.0

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

Next, add the below lines near the end of that configuration file:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Next, save and exit from the file.

Restart MySQL server for the changes to take effect:

$ sudo service mysql restart    

 

Step 3) Create a New User for Replication Services on Server 1

Next, we are going to create new user that will handle replication service on server 1. Log into Mysql Server as Below.

$ sudo mysql -u root -p

mysql > create user 'replication_user'@'%' identified by 'replication_password';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
mysql > FLUSH PRIVILEGES;

Next, Enter the command below to view information about the position and file of the Master Server

mysql> show master status; 

Here, you need to note the file name, 'mysql-bin.000001' and Position '154'. You will need these details when configuring server 2. Please note, these values might be different on your server.

Step 4) Install MySQL On Server 2 (Slave)

Installing MySQL server on server 2 with IP address 192.168.100.122

$ sudo apt-get update
$ sudo apt-get install mysql-server

Step 5) Modify the Configuration File on Server 2

Next, edit the MySQL configuration file on Server 2 and replace bind-address = 127.0.0.1 Change to bind-address = 0.0.0.0

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

Next, add the below lines near the end of that configuration file:
Note: this time server-id is '2'.

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

Next, save and exit from the file.

Restart MySQL server for the changes to take effect:

$ sudo service mysql restart  

Step 6) Change Master-Slave Configuration on Server 2

Next, login to MySQL on server 2:

$ sudo mysql -u root -p

Replace the 'MASTER_LOG_FILE' and 'MASTER_LOG_POS' value with the information you obtained from server 1.
The MySQL username and password should also match the values you created on server 1.

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.100.111', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'replication_password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;

mysql > start slave;
mysql > Quit;

 

Step 7) Test the Configuration

Now we are going to test the configuration and check whether MySQL replication is working as expected.
To confirm this, log in to MySQL on server 1

$ sudo mysql -u root -p

On the MySQL prompt that appears, create a database name as (e.g. replication_db ):

mysql> create database replication_db;

Step 8) Log in to MySQL on server 2

$ sudo mysql -u root -p

Next, see that replication_db is appearing here which we created in server 1.

> show databases;

Finished, now we have successfully