MySQL DB Replication.

MySQL is one of the prominent open source RDBMSs. In the cases of traffic growth to DBMS and when in the cases of backing up data replication is there for the rescue. MySQL comes with a set of in-built replication mechanisms that can be utilized as the need arise. Mainly there are 2 replication setups, master-master replication, and master-slave replication. As the names imply master-master is used as a load balancing technique in the presence of high write load. In contrast, master-slave is utilized when read load is significantly high, and for data backup purposes. However, one can utilize both at the same time.

In this blog post, we will learn to set up and understand master-slave replication and the concept of solving issues that could arise from time to time.

Setting Up Master-Slave Replication

Master-slave replication is based on binary logs. The master writes all transactions into its binary log. The slave reads the transaction from the master’s binary log and writes them into its relay log. Only after that, the slave executes the SQL statements from its relay log.

Let’s get our hands dirty then. (The following commands are for Ubuntu 18.04, however, can follow the steps with your OSs corresponding commands)

Setting Up the Master

1. log in to the machine where MySQL master server is running and open ‘mysqld.cnf’ file in an editor.

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

2. Set bind-address to 0.0.0.0 to allow any connection. This automatically allows remote connections.

bind-address = 0.0.0.0

3. Add the following lines. Usually, these lines can be found in the mysqld.cnf but they are commented out.

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

Note: Here we have used /var/log/mysql directory (this directory is owned by the ‘mysql’ user). Anyhow you can use any directory. If a different location is used make sure that the ‘mysql’ user owns the specified directory.

5. Specify the DBs that need to be logged for replication. It should also be specified in mysqld.cnf file as follows,

binlog-do-db = db_name

Note: Here you can specify more than one DB. Just add as many as you want separating them by commas. Another advantage is, binlog-do-db makes sure that the master writes only the statements corresponding to the specified DBs.

4. Restart the MySQL server.

$ sudo systemctl restart mysql.service

5. Now we need to create a replication user in the MySQL server and grant slave replication privileges to that user.

$ mysql -u root -p
mysql > create user ‘replica’@’%’ identified by ‘password’;
mysql > grant replication slave on *.* to ‘replica’@’%’;
mysql > flush privileges;

6. Now go to the DB that we wanna replicate. Lock the tables. See the master status and dump the DB. Here what we are trying to achieve is to get a snapshot of the DB of which we know the replication log status (i.e. binary log position).

mysql > use db_name;
mysql > flush tables with read lock;
mysql > show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |       db_name|                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

7. In a new terminal using mysqldump tool dump the DB.

$ mysqldump -u root -p --opt db_name > db_name.sql

8. Once dumping is finished. Unlock the tables and quit from mysql console.

mysql > unlock tables;
mysql > quit;

Setting Up the Slave

1. Get the sql dump file and restore the DB in slave MySQL server. For that you might need to download the db_name.sql file into the machine where you have the slave MySQL server. Then log into the mysql console and source the db_name.sql file as follows,

$ mysql -u root -p
mysql > source <absolute_path>/db_name.sql

Note: If you want to replicate more than one DBs you need to get the dumps of those DBs as well and restore them in the slave server.

2. Repeat the steps 1, 2, 3 and 4 of Setting Up master. This time as the server-id use a different positive integer. Typically masters get the first integers and slaves get the following integers. For an example, for the slave use 2 as the server-id because we used 1 as the master server-id.

If you want to filter out a particular set of DBs in the slave then you need to add the following line in the mysqld.cnf. Add the DBs that you want to replicate, comma separated.

replicate-do-db = db_name

Note: replicate-do-db makes the slave just read statements from the relay log, that are for the specified DB. Furthermore, replicate-do-db has no effect on the master.

3. Login to slave MySQL server and configure the slave replication as follows,

$ mysql -u root -p
mysql > stop slave; 
mysql > CHANGE MASTER TO MASTER_HOST = 'master-ip', MASTER_USER = 'replica', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 
mysql > start slave;

4. To see the replication status and verify whether the replication is working fine execute the following command in the slave MySQL console.

mysql > show slave status\G;

Here \G is used to get a formatted output. In the output, you need to check 4 main things.

   Slave_IO_State: Waiting for master to send event           
      Master_Host: 103.188.0.84
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If you see something similar to the above then all the best! your replication is working fine. However, most of the times it is not the case you’ll not see a smoothly working replication. Okay then let’s see how we can overcome these issues.

Troubleshooting

Recovering from SQL errors

MySQL comes with a set of utility tools. ‘mysqldump’ is one such tool. So is the ‘mysqlbinlog’. This tool is used to decode the content of the binary log files to MySQL statements.

Login to slave mysql console and issue the show slave status \G as follows;

mysql > show slave status\G;

When you see an error status against Slave_SQL_Running, first you should check the Last_Error. For further details see Last_SQL_Error also. Also, note the Relay_Master_Log_File and Exec_Master_Log_Pos. This specifies the last successfully executed binary log position.

Get the corresponding log file and decode it using the ‘mysqlbinlog’ tool. An example is given in the following,

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000011 > ~/DECODE-11.log

Then in the decoded file (as per the example it’s DECODE-11.log) locate the corresponding position and you can see the SQL statement that is giving the error. Tackling the problem is up to you :).

Once you did make sure you skip the required no of steps so that the same SQL statement does not get executed twice leading to replication failure again. Skipping can be achieved as follows,

 mysql > STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Recovering from other errors

It is really difficult to cover all the troubleshooting in one blog post. You have to tackle them as they arise. The followings are a set of posts that I found helpful in tackling some of the issues I got down the line.

MySQL Replication: 'Got fatal error 1236' Causes and Cures - DZone Database

Mysql replication error 1594 - Relay log read failure - Could not parse relay log event entry

After all these, I should mention one more thing. There is a lot more to learn in replication and you would not know when replication will fail. So, my advice is to keep learning and do modifications to the configurations as you learn better ways. Happy replication! :)