Set Up MySQL 8 Replication with Linux Nodes

https://arctype.com/blog/mysql-8-create-master-slave-replication/

MySQL is an open-source and one of the most widely used relational database management systems. Setting up a separate MySQL backup server is essential in the event of failure, as the backup server contains everything necessary for a successful recovery.

Replication features in MySQL allow you to maintain multiple copies of MySQL data. All data in the master server will be synced to the slave server automatically. If your master server fails, you can promote a Slave to a Master for commit operations.

Prerequisites

• Two servers running Ubuntu 20.04.

• A root password is configured on both servers.

For this tutorial, we will use the following configuration:

Install MySQL 8

First, you will need to install the MySQL 8 server on both servers.

Run the following command to install the MySQL 8 server on both servers.

apt install mysql-server -y

After installing the MySQL server, you will need to set a MySQL root password on both servers.

Run the following command to set a root password:

mysql_secure_installation

You will be asked the following:

Press y|Y for Yes, any other key for No:

Just press Enter to set a root password:

Please set the password for root here.
New password: 
Re-enter new password:

Set your MySQL root password and answer the remaining questions as shown below:

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 Master Server

First, you will need to edit the MySQL default configuration file on the Master server to allow remote access and enable the binary log.

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

Add or modify the following lines:

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address            = 0.0.0.0  
log_error = /var/log/mysql/error.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 500M
slow_query_log = 1

Save and close the file, then restart the MySQL service to apply the changes.

systemctl restart mysql

Create a Replication User on Master Server

Next, you will need to create a replication user on the Master server to manage the replication.

To do so, connect to the MySQL shell with the following command:

mysql -u root -p

Provide your MySQL root password, then run the following command to create a replication user:

mysql> CREATE USER slaveuser@45.58.40.60 IDENTIFIED WITH mysql_native_password BY 'password';

Next, grant REPLICATION SLAVE privileges to replication user:

mysql> grant replication slave on *.* to slaveuser@45.58.40.60;

Next, flush the privileges to apply the changes:

mysql> flush privileges;

Next, check the privileges using the following command:

mysql> show grants for slaveuser@45.58.40.60;

Sample output:

+-------------------------------------------------------------+
| Grants for slaveuser@45.58.40.60                            |
+-------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `slaveuser`@`45.58.40.60` |
+-------------------------------------------------------------+

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

mysql> exit;

Note: Replace the 45.58.40.60 with the IP address of the Slave server.

Configure Slave Server

Next, you will need to edit the MySQL main configuration file and make some changes:

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

Add or modify the following lines:

pid-file        = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
bind-address            = 0.0.0.0  
datadir = /var/lib/mysql
log_bin = /var/log/mysql/mysql-bin.log
server-id = 2
read_only = 1
max_binlog_size = 500M
slow_query_log   = 1

Save and close the file, then restart the MySQL service to apply the changes:

systemctl restart mysql

Initialize Replication on Slave Server

Next, you will need to start the Replication process on the slave server.

First, connect to the MySQL shell on the Master server with the following command:

mysql -u root -p

Next, check the Master status with the following command:

mysql> show master status\G

Sample output:

*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1047
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

From the above output, note down the master log file and position number

Next, go to the Slave server and connect to the MySQL shell:

mysql -u root -p

Next, use the information obtained from the Master server and configure the Slave server with the following command:

mysql> CHANGE MASTER TO MASTER_HOST='45.58.41.25', MASTER_USER='slaveuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1047;

Next, start the Slave using the following command:

Note: replace IP 45.58.41.25 with the IP address of the Master server.

mysql> start slave;

Next, verify the Slave status with the following command:

mysql> show slave status\G

You should get the following output:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 45.58.41.25
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1047
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Verify Master-Slave Replication

At this point, MySQL Master-Slave replication is configured. Now, it's time to test whether the replication is working or not.

First, go to the Master server and create a database with the following command:

mysql -u root -p
mysql> create database replicadb;

Next, verify all databases using the following command:

mysql> show databases;

Sample output:

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

Next, exit from the MySQL shell:

mysql> exit;

Next, go to the Slave server and connect to the MySQL

mysql -u root -p

Next, list all databases using the following command:

mysql> show databases;

Sample output:

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

The above output indicates that MySQL Master-Slave replication is working as expected because the database 'replicadb' was replicated on server 2.

Conclusion

In the above guide, you learned how to set up a two-node MySQL 8 Master-Slave replication on Ubuntu 20.04. You can now implement this setup in the production environment to perform live copies of your databases.