MySQL Master Slave Replication Setup

MySQL Master Slave Replication Setup

With MySQL master slave replication, we will sync the database between the source and destination servers in real time. We will have master (source) and slave (destination). The backup will move in one direction from the master to the slave.

a. Configure Master

1. [Master] First, we need to know which databases to sync within our MySQL master slave replication system. In our case, we will sync all databases on Master but we need to add them one by one in the 50-server.cnf configuration file as you can’t do that in a single line. So, let’s check the databases on Master by logging to the MySQL console by running the below command on PuTTY SSH client or any Linux terminal as below:

root@mail:~# mysql

Please note that you can use the command above alone in case your root user has a password and you use a CNF file at the base directory of the server. You can refer to this part: Install MariaDB on Ubuntu (part: Use CNF File to Login to MariaDB on Ubuntu Without a Password).

Master Enter MySQL

List All Databases

Now, let’s list all databases by running the command:

MariaDB [(none)]> show databases;
Master Show All Databases

So, we need to sync these databases:

amavisd
fail2ban
information_schema
iredadmin
iredapd
mysql
performance_schema
roundcubemail
sitebase_db
sogo
vmail   

Enable Binary Log and MySQL Master Slave Replication

Open 50-server.cnf File

2. [Master] Enable binary log and replication on Master. Open the 50-server.cnf file by running the command below:

root@mail:~# nano /etc/mysql/mariadb.conf.d/50-server.cnf

Then, find the Logging and Replication section in [mysqld] unit. After that, add the following lines but you need to replace the name of the databases below with the databases we you want to sync. In our case, we will sync all databases as below:

log-bin         = /var/log/mysql/master-bin
log-bin-index   = /var/log/mysql/master-bin.index
binlog_format   = mixed
server-id       = 01
replicate-do-db = amavisd
replicate-do-db = fail2ban
replicate-do-db = information_schema
replicate-do-db = iredadmin
replicate-do-db = iredapd
replicate-do-db = mysql
replicate-do-db = performance_schema
replicate-do-db = roundcubemail
replicate-do-db = sitebase_db
replicate-do-db = sogo
replicate-do-db = vmail

Your file should look like below. Don’t close the file.

Master Enable Binary Log and Replication

3. [Master] In the same file: 50-server.cnf which is still open, find the line: bind-address = 127.0.0.1, then comment it by adding a hash symbol (#) in front of it, like this:

# bind-address            = 127.0.0.1

It should look like this:

Master Comment Bind Address Line

Enable Error Log

4. [Master] Keep the 50-server-cnf file open because we will enable error log. So, find the below lines in [mysqld] unit:

general_log_file       = /var/log/mysql/mysql.log
general_log            = 1
log_error = /var/log/mysql/error.log

Then, uncomment them. That is to say, remove the hash symbol (#) at the beginning of each line if it’s commented (inactive). It should look like below eventually:

Enable Error Log

To check the error log later, you need to run the below command:

root@mail:~# tail -100 /var/log/mysql/error.log

In case you want to check MySQL log, run the below command:

root@mail:~# tail -100 /var/log/mysql/mysql.log

Note that the number: 100, is the number of recent lines, you can change it. Now, you can close the 50-server-cnf file. In case the /var/log/mysql/error.log file was empty, it means a log-rotate happened. So, you need to list all the files in the directory: /var/log/mysql/ to see the other error.log files. You might see error.log.1, error.log.2, etc. Therefore, you need to check their logs too.

5. [Master] Restart MariaDB server by running the below command:

root@mail:~# service mariadb restart
Master Restart MariaDB

Create MySQL Master Slave Replication User

6. [Master] To create MySQL master slave replication user on Master server, enter MySQL console from root user by running the below command:

root@mail:~# mysql

Then, run the below MySQL command to create a replication_user MySQL user on Master. Note that you should replace the password below which is 12345678 with your own password. The password is simple because it’s just an example, you should add a strong password.

MariaDB [(none)]> create user 'replication_user'@'%' identified by '12345678';
Master Create Replication MySQL User

After that, we should make sure that replication_user has been created. So, run the below MySQL command:

MariaDB [(none)]> select user from mysql.user;

As you see, it’s there.

Master Verify Replication User Has Been Created

7. [Master] Grant “replication slave” privilege to replication_user MySQL user which we’ve just created earlier by running the below command:

MariaDB [(none)]> grant replication slave on *.* to replication_user require ssl;

We should use ssl connection since our servers are on the public internet.

Master Grant Replication Slave Privilege to Replication User

If you don’t want to use SSL but would like to connect over private network, you can run the below command to cancel the SSL requirements:

MariaDB [(none)]> grant replication slave on *.* to replication_user require none;

8. [Master] Flush privileges table by running the command:

MariaDB [(none)]> flush privileges;
Master Flush Privileges

b. Configure Slave

1. [Slave] Install the latest version of MariaDB on Slave (destination server). For this purpose, follow the instructions in this article: Install MariaDB on Ubuntu

Sometimes during the upgrade process apt upgrade command, you get popups like the one below about updating the packages configuration. So, don’t change anything, just hit Enter key and keep it as default. Do this for any similar popup.

Apt Upgrade Popup SSHD Config

Enable Binary Log and MySQL Master Slave Replication

2. [Slave] Enable binary log and replication on Slave. Open the 50-server.cnf file by running the following command:

root@vpsprof-backup:~# nano /etc/mysql/mariadb.conf.d/50-server.cnf

Then, find the Logging and Replication section in [mysqld] unit. After that, add the following lines but you need to replace the name of the databases below with the databases you want to sync.

server-id       = 02 
relay-log-index = /var/log/mysql/slave-relay-bin.index
relay-log       = /var/log/mysql/slave-relay-bin
replicate-do-db = amavisd
replicate-do-db = fail2ban
replicate-do-db = information_schema
replicate-do-db = iredadmin
replicate-do-db = iredapd
replicate-do-db = mysql
replicate-do-db = performance_schema
replicate-do-db = roundcubemail
replicate-do-db = sitebase_db
replicate-do-db = sogo
replicate-do-db = vmail

Your file should look like below:

Slave Enable Binary Log Replication

Don’t close the file, wait…

Enable Error Log

3. [Slave] Enable error log, same as what we did on Master earlier.

Prevent from Duplication Error

4. [Slave] Add the below two lines to the same file which is open now to prevent from duplication error on Slave when running MySQL master slave replication:

slave-skip-errors=1062
skip-slave-start

The file should look like below. Close and save the file.

Slave Skip Errors

5. [Slave – not required] We didn’t enable binary logging on Slave, but if the Slave will act as master for another server, we can enable it by adding these 3 lines below the lines we’ve just added above:

log-bin       	= /var/log/mysql/02-bin 
log-bin-index 	= /var/log/mysql/02-bin.index 
binlog_format 	= mixed

Also, add the following line as well below the lines above:

log_slave_updates = ON

6. [Slave] Restart MariaDB server by running the below command:

root@vpsprof-backup:~# service mariadb restart

c. Secure Connection wtih SSL

1. [Master] Now, we need to add any of your website’s Letsencrypt SSL certificates to Master to have a secure connection with Slave. For this purpose, we will add the TLS certificate of the email server project: mail.vpsprof.com which we already obtained here: TLS Certificate Installation (part: a. Install Certbot and Obtain TLS Certificate). In case you didn’t install the email server, you can use the SSL certificate of the website’s domain you hosted on your server. In our tutorial, our website is gainbybrain.com and we explained how to obtain its certificate here: Nginx Virtual Hosts Configuration (part: b. Obtain SSL Certificate). To list the available certificate folders, run the below command:

root@mail:~# ls /etc/letsencrypt/live
Master List SSL Certificates

Then, open 50-server.cnf file by running the command below:

root@mail:~# nano /etc/mysql/mariadb.conf.d/50-server.cnf

After that, find Security Features section in [mysqld] unit. Then, add the following lines (make sure to replace the website below with the name of your website):

ssl-ca = /etc/letsencrypt/live/mail.vpsprof.com/chain.pem 
ssl-cert = /etc/letsencrypt/live/mail.vpsprof.com/cert.pem 
ssl-key = /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem

Note that the file should look like below:

Add SSL Connection

2. [Master] Grant MySQL the permission to access the SSL files using the following two commands:

root@mail:~# setfacl -R -m "u:mysql:rx" /etc/letsencrypt/archive/
root@mail:~# setfacl -R -m "u:mysql:rx" /etc/letsencrypt/live/
Grant MySQL Permission Access SSL

In case the ACL service is not installed, you can install it easily by running the command:

root@mail:~# apt install acl
Install ACL

3. [Master] Restart MariaDB server by running the below command:

root@mail:~# service mariadb restart

4. [Master] Log to Master MariaDB console (MySQL console) by the running the command:

root@mail:~# mysql

Check if SSL Connection is Enabled

Then, check if it uses SSL now by running the command:

MariaDB [(none)]> show global variables like "%ssl%";
Master SSL Connection is Disabled

As you see, SSL connection is still disabled. Usually it’s because the certificate Private Key is in PKCS8 format and we should convert it to PKCS1 format. The PKCS1 key starts with:

-----BEGIN PRIVATE KEY-----

So, let’s verify that by checking the file content by running the command below to open it:

 root@mail:~# nano /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem

Then, notice what’s on top which means it’s PKSC8 key.

Master Private Key PKSC8

Convert Certificate’s Private Key from PKCS8 to PKCS1

Note that the general command to convert the key to PKCS1 is like below:

openssl rsa -in /path/to/pkcs8 -out /path/to/pkcs1

So, let’s apply the command above on our Private Key (privkey.pem). Don’t forget to replace the domain below with your own domain.

root@mail:~# openssl rsa -in /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem -out /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem
Master Convert PKCS1 to PKSC8

Now, let’s check the key after the conversion, open it:

root@mail:~# nano /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem

As you see, it beings with:

-----BEGIN RSA PRIVATE KEY-----
Master Private Key PKCS1

Now, restart MariaDB server for the changes to take the effect:

root@mail:~# service mariadb restart

Then, login to MariaDB Console and check the SSL if it’s enabled by running:

MariaDB [(none)]> show global variables like "%ssl%";
Master Verify SSL Connection

As you see, it works now! The SSL is enabled on Master.

Note that enabling SSL on Master might cause problems with logging to your email address on Roundcube. That is to say, if you try to login to: postmaster@vpsprof.com you won’t get a response. This happens because Ubuntu 20 improved the security level. In order to solve this problem, open the OpenSSL configuration file by running the command:

root@mail:~# nano /usr/lib/ssl/openssl.cnf

Then, put at the beginning of the file this line:

openssl_conf = default_conf

After that, put at the end of the file these lines:

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = ssl_default_sect

[ssl_default_sect]

MinProtocol = TLSv1
CipherString = DEFAULT:@SECLEVEL=1

Then, close and save the file. Don’t forget to restart MariaDB Server for the changes to take effect:

root@mail:~# service mariadb restart

d. Test SSL Connection

SSL Login from Slave to Master

1. [Slave] To test SSL connection, we need to login from Slave to Master’s user: replication_user. The command is like below:

root@vpsprof-backup:~# mysql -h Master_IP_Address -u replication_user -p --ssl;

Note that the IP address of the master is: 144.91.72.55, so we need to run the below command on Slave.

root@vpsprof-backup:~# mysql -h 144.91.72.55 -u replication_user -p --ssl
Slave SSL Login to Master

2. [Slave] You will be asked to enter the password of the replication_user. So, enter the password then hit Enter key.

SSL Login from Slave to Master Unsupported Protocol Error

Opps! We got the following error:

ERROR 2026 (HY000): SSL connection error: unsupported protocol

But don’t worry. This happens because Ubuntu 20 improved the security level, as we mentioned earlier. These security improvement prevented from logging to Roundcube after enabling SSL on Master. To solve this problem, we will do the same steps we applied on Master earlier. Open the OpenSSL configuration file by running the command:

root@vpsprof-backup:~# nano /usr/lib/ssl/openssl.cnf

Then, put at the beginning of the file this line:

openssl_conf = default_conf

After that, put at the end of the file these lines:

[ default_conf ]

ssl_conf = ssl_sect

[ssl_sect]

system_default = ssl_default_sect

[ssl_default_sect]

MinProtocol = TLSv1
CipherString = DEFAULT:@SECLEVEL=1

Don’t forget to restart MariaDB Server for the changes to take effect:

root@vpsprof-backup:~# service mariadb restart

Now, let’s try again:

root@vpsprof-backup:~# mysql -h 144.91.72.55 -u replication_user -p --ssl
Slave SSL Login to Master Success

And here we go! We’re able to access Master‘s database from Slave via SSL connection.

3. [Slave] Let’s check the status of the Master from the Slave by running the command:

MariaDB [(none)]> status;
Slave Check Master Status

e. Export All Databases from Master and Import Into Slave

Lock Master's Database

1. [Master] Run this MySQL command on Master to prevent any change to database before copying the database from the Master to Slave.

MariaDB [(none)]> flush tables with read lock;
Master Lock Tables

If you’re on live server, the website will go offline and when you exist MariaDB console (MySQL console) by typing exit, quit or pressing Ctrl+C, the lock will be released. You can also use the command: unlock tables to release the lock but don’t exist MariaDB Console now because we need to keep the lock until we transfer the files to the slave. You need to start new PuTTY session or new Terminal session now for the Master.

2. [Master] We need to send all databases from Master to Slave, we’ve to do three easy things:

Generate Backup ZIP Files on Master via backup.sh Script

First: we need to run the backup.sh bash script (from new PuTTY session or new Terminal session) to generate new zip file for all databases on Master. Simply, you need to run the below command but you should be sure that the backup.sh bash script is setup alright to backup all databases.

root@mail:~# bash /var/master_backup/backup.sh

We already explained this here: Backup MySQL and Site Files on Source Server

Sync master_backup Directory to Send Backup to Slave

Second: we need to send the generated zip files from Master to Slave by using the Rsync utility to sync the master_backup directory on both servers. As we use iNotifyWait utility which we explained here: Real Time Sync via iNotifyWait. The backup zip files that we generated in the previous step via the backup.sh script should be sent automatically to Slave. However, you can force that manually if any delay happen by any reason by running the command below which is explained here: Rsync Linux User Creation and Test (Sync Backup Directory Between Source and Destination Servers part). Note that the Slave IP address is: 37.184.33.153, you should replace it with the IP address with your Slave.

root@mail:~# flock -n lock_file -c "rsync -vzru -e 'ssh -p 22' --rsync-path='sudo rsync' --delete /var/master_backup/ rsyncuser@137.184.33.153:/var/master_backup/"

Import the Zipped Backup File

Third: import the zip file for all databases that you sent from Master to Slave. This can be done easily by checking the latest zip file for all databases by running this command:

root@vpsprof-backup:~# ls -la /var/master_backup/databases/all

Then, run the below command but make sure to replace all-databases-backup-zip-file-without-zip-extension with the backup zip file for all databases without adding the .zip extension in the command.

root@vpsprof-backup:~# bash /var/master_backup/restore_db.sh all-databases-backup-zip-file-without-zip-extension all

We already explained this here: Restore MySQL on Destination Server from Backup (part b. Restore All MySQL Databases via restore_db.sh Bash Script).

Lock Slave's Database

3. [Slave] Once we’re done with the data transfer from Master to Slave and to keep the data on Slave consistent with the master, we will disable writing to Slave database (read-only).

Open again the 50-server.cnf file by running the command:

root@vpsprof-backup:~# nano /etc/mysql/mariadb.conf.d/50-server.cnf

Then, add the below line inside the [mysqld] section:

read-only = 1

The file will look like below, close and save the file.

Slave Read Only Database

4. [Slave] Restart MariaDB server by running the below command:

root@vpsprof-backup:~# service mariadb restart

f. Connect Slave to Master Mysql

1. [Master] Let’s check the binary log numbers which are the name of the binary log file in use and the position number of the last written event by running the command on MySQL console:

MariaDB [(none)]> show master status;
Master Show Status

Establish MySQL Master Slave Replication Connection

2. [Slave] Run the below command to establish the connection between the Master and the Slave:

MariaDB [(none)]> change master 'master01' to master_host='Master_IP_Address',
master_user='Replication_User_In_Use',
master_password='Password_of_Replication_User',
master_port=3306,
master_log_file='Master_Binary_Log_File_Name',
master_log_pos=Master_Binary_Log_File_Position,
master_connect_retry=10,
master_use_gtid=slave_pos,
master_ssl=1;

And here when we replace the variables with our own variables:

MariaDB [(none)]> change master 'master01' to master_host='144.91.72.55',
master_user='replication_user',
master_password='12345678',
master_port=3306,
master_log_file='master-bin.000061',
master_log_pos=504933,
master_connect_retry=10,
master_use_gtid=slave_pos,
master_ssl=1;
Slave Connect to Master

Start Slave

3. [Slave] Start Slave by running the command:

MariaDB [(none)]> start slave 'master01';
Slave Start MySQL Master Slave Replication

Check MySQL Master Slave Replication Status

4. [Slave] Check the replication status by running the below command:

MariaDB [(none)]> show slave 'master01' status\G;

To make sure that the replication is successful, the values of the two variables below must be Yes.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave Show Replication Status

Also, you can check the gtid (Global Transaction ID) for both the Master and the Slave, they should be the same. This number changes whenever any operation done on Master database, for example, you can try sending an email or opening an email, it will change.

Find gtid on Master

Headline connect master to slave

To check the gtid on Master, run the below command:

MariaDB [(none)]> show variables like 'gtid_binlog_pos';
Master gtid-binlog-pos

Find gtid on Slave

To check the gtid on Slave, run the below command:

MariaDB [(none)]> show variables like 'gtid_slave_pos';
Slave gtid-slave-pos

Unlock Master's Database

5. [Master] Now, you should release the lock by exiting MariaDB Console on first session that we used to lock the database by typing exitquit or pressing Ctrl+C. Or you can run the below command on same session that locked the database:

MariaDB [(none)]> unlock tables;

In case you lost the connection with the first session that you used to lock tables, you can unlock the database by restarting MariaDB on Master:

root@mail:~# service mariadb restart

With that being said, you shouldn’t restart your MariaDB server on Master after locking the Master’s database.

Stop Slave

6. [Slave] If the replication wasn’t successful for any reason or if you want to restart it, first, you need to stop the Slave by running the below command:

MariaDB [(none)]> stop slave 'master01';

Reset MySQL Master Slave Replication Connection

Then you need to reset the whole connection by running the below commands:

MariaDB [(none)]> reset slave 'master01' all;

After that, fix the problem and Establish the connection again. Note that restarting MariaDB server (service mariadb restart) on Slave will result in stopping the replication process while restarting MariaDB on Master will not stop the replication process.

To resume the replication after restarting MariaDB server on Slave, just start the Slave as below.

MariaDB [(none)]> start slave 'master01';

If you want to add a new database to the replication, you should follow our super easy article here: Add New Database to Replication.

Leave a Comment

Your email address will not be published.