Replication between master and slave MySQL databases involves replicating and copying database files across multiple servers in a network. Using this setup, the master server serves as a redundancy and fault-tolerance mechanism in the event of a failure, the slave server provides a mechanism for recovering the databases in the event of a failure in the Master server. With this feature, users are assured that no data will be lost in the event of a loss of a server since a replica of the databases can still be retrieved from a different location.
Requirements
The setup will consist of two servers running Ubuntu 20.04 with the following IP addresses.
Master server: 192.168.1.30
Slave server: 192.168.1.31
In this section, we will explore how to configure a Master-slave replication setup on Ubuntu.
Step 1: Install MySQL on both Master and Slave servers
MySQL version 5.7 is available in the Ubuntu repositories. It is recommended that you install the latest MySQL version in order to take advantage of any new features and avoid any potential issues.
Before proceeding, update both servers using the following apt command
# sudo apt update
Now install MySQL on both servers and execute the below command.
# sudo apt install mysql-server mysql-client
Now, Open the mysql configuration file.
# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
On the Master server, find the bind-address attribute as shown below.
bind-address =127.0.0.1
Change it to the master server IP.
bind-address =192.168.1.30
In the [mysqld] section, specify a value for the server-id attribute. It is important that the number you choose does not correspond to any other server-id number. We will assign the value 1 to the variable.
server-id =1
Copy and paste the following lines at the end of the configuration file.
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
Upon exiting the configuration file, restart MySQL service on the Master server to ensure that the changes take effect.
# sudo systemctl restart mysql
Run the following command to verify that the MySQL server is functioning as expected.
# sudo systemctl status mysql
Step 2: Create a new replication user on the master server
Our objective in this section is to create a replication user on the master server. This can be achieved by logging into the MySQL server as shown below.
# sudo mysql -u root -p
After creating a replica user, proceed with the steps below and grant access to the replica slave by executing the queries below. Make sure you use your slave server IP address.
mysql> CREATE USER 'replica_root'@'192.168.1.31' IDENTIFIED BY 'navi@54321';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_root '@'192.168.1.31';
The next step is to run the following command.
mysql> SHOW MASTER STATUS\G
As you can see below, the output should be similar.
Make sure to note down the mysql-bin.000002 value and the Position ID 1643. These values will be crucial when setting up the slave server.
Step 3: Configure the MySQL Slave Server
Open the MySQL configuration file on the slave server, just as we did on the master server.
# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Proceed to edit the following lines in the same manner as the master server.
bind-address = 192.168.1.31
In the [mysqld] section, specify a value for the server-id attribute. Select a different value this time. Let’s choose option 2.
server-id=2
The lines below should be pasted at the very end of the configuration file once again.
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
Upon exiting the configuration file, restart the MySQL service on the Slave server to ensure that the changes take effect.
# sudo systemctl restart mysql
The next step is to log into the MySQL shell as shown below.
# sudo mysql -u root -p
The slave server will need to be configured in this step so that it can communicate with the master server. The slave threads must first be stopped as shown.
mysql> STOP SLAVE;
Run the following command on the slave server to allow it to replicate the master server.
mysql> CHANGE MASTER TO MASTER_HOST ='192.168.1.30', MASTER_USER ='replica_root ', MASTER_PASSWORD ='navi@54321', MASTER_LOG_FILE = 'mysql-bin.000002', MASTER_LOG_POS = 1643;
mysql> START SLAVE;
Step 4: Verify the MySQL Master-Slave Replication
Our next step will be to create a new database on the master and verify that it has been replicated on the MySQL slave server in order to determine whether the setup is working as expected.
Log into MySQL in the Master server.
# sudo mysql -u root -p
Creating a test database is the first step. We will create a database called replicatest_db in this instance.
mysql> CREATE DATABASE replicatest_db;
You will now need to log into your MySQL instance on the slave server.
# sudo mysql -u root -p
Now list the databases
mysql> SHOW DATABASES;
Upon logging into the slave, you will notice that the database you created on the master has been replicated. Great work! The replication between the MySQL master and slave is working as expected. Now that copies of the database files are replicated to the slave server, you can rest assured that data will be protected in case of failure.