MySQL Database Synchronisation / Replication

Synchronize or Replicate a database protects the integrity of the data over the different servers, provides high availability and improves capacity, reliability, fault-tolerance, and accessibility.

Models

Master-to-Slave: Master feeds Slave with real-time updates.

Master-to-Master: Both servers feed each other with the latest updates.

Configuring Master-to-Slave

Master IP: 10.0.0.1
Slave IP: 10.0.0.2

BOTH

sudo apt update
sudo apt install mysql-server mysql-client -y

MASTER

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

Under [mysqld] change the following lines and uncomment or add the other lines if necessary:

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

Restart and check if it is running.

sudo systemctl restart mysql
sudo systemctl status mysql

Create a user to the slave connect to the master:

sudo mysql -u root -p
mysql> CREATE USER 'repli_user'@'%' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli_user'@'%';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Note the two values acquired (mysql-bin.000004 and 731):

SLAVE

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

Under [mysqld] change the following lines and uncomment or add the other lines if necessary:

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

Restart and check if it is running.

sudo systemctl restart mysql
sudo systemctl status mysql

Log into MySQL shell, configure the slave to connect to the master with the created credentials, and insert the preview acquired data:

sudo mysql -u root -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repli_user', MASTER_PASSWORD='strong_password', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=731;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

Check if the status:

Up to this point, it will work everything should work perfectly for Ubuntu 18.04 with MySQL 5.7 (if you got it successful just to Testing step) but for Ubuntu 20.04 MySQL 8.0 you should get the following result:

This issue may be fixed by issuing the following command on the console of the slave server to import the public key from the server:

mysql --ssl-mode=DISABLED -h 10.0.0.1 -u repli_user -p --get-server-public-key

Then check it again:

sudo mysql -u root -p
mysql> SHOW SLAVE STATUS\G;

Testing:

Create a database on the Master and showing it on the Slave.

mysql> CREATE DATABASE ABC;
mysql> SHOW DATABASES;

mysql> SHOW DATABASES;

Configuring Master-to-Master

Do all the steps from Master-to-Slave and ensure it was successful to replicate.

SECOND MASTER (previously called SLAVE)

To transform the slave into a second master create a user to the first master connect to it:

sudo mysql -u root -p
mysql> CREATE USER 'repli_user'@'%' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli_user'@'%';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

FIRST MASTER

sudo mysql -u root -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.2', MASTER_USER='repli_user', MASTER_PASSWORD='strong_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1045;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

If necessary apply the same solution used on the Second Master (Slave) server to the First Master:

mysql --ssl-mode=DISABLED -h 10.0.0.2 -u repli_user -p --get-server-public-key