MySQL Delayed Slave Replication
MySQL replication works great and indeed an great way to scale reads as well for high availability but what are pain points if you have not planned for a delayed slave ? What is delayed slave ? Before I answer this question, Have you ever thought about how an wrong transaction can corrupt MySQL master and all connected slaves ? The answer for this problem is a delayed slave replication, It’s actually you are intentionally lagging MySQL slave to a master by few minutes / hours so that you have always a MySQL instance far from damage. There are two ways to do this, 1. MySQL delayed slave replication , 2. Percona Toolkit pt-slave-delay (from Percona – https://www.percona.com/software/database-tools/percona-toolkit)
In this exercise I have used MySQL 5.7 on CentOS 7.4 with Percona Toolkit . I have written a blog on step-by-step installation of MySQL Master-Slave replication on CentOS here – https://minervadb.com/index.php/2018/01/24/step-by-step-mysql-master-slave-replication-on-centos/
MySQL delayed slave replication
Step 1: Login with MySQL root user in Slave server
[root@localhost ~]# mysql -u root -p Enter password: mysql>
Step 2: Stop the slave
mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql>
Step 3: Now set delay time for replication in MySQL slave server. Here in this example, I am setting for 60 minutes or 3600 seconds.
mysql> CHANGE MASTER TO MASTER_DELAY = 3600;
Step 4: Start the mysql slave
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Step 5: Now check the SLAVE status
mysql> show slave status\G Master_Server_Id: 500 Master_UUID: 5ea8a1ea-f45e-11e7-bf7b-080027169869 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 3600 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
How to reset back to delay seconds Zero or default delay setting ?
You just need to stop and start MySQL slave
mysql> stop slave; Query OK, 0 rows affected (0.00 sec)
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Using pt-slave-delay for implementing delayed slave
pt-slave-delay is bundled with Percona Toolkit (https://www.percona.com/software/database-tools/percona-toolkit) . The steps here are quite direct and simple (here also we have delayed slave by 60 minutes), I have explained same below :
[root@localhost ~]# pt-slave-delay --delay 60m --user root --password PasswordComplex001 2018-01-29T16:07:28 slave running 0 seconds behind 2018-01-29T16:07:28 STOP SLAVE until 2018-01-29T17:07:28 at master position mysql-bin.000008/154
When you are building MySQL replication, please plan for delayed slaves to rollback if needed