How to lag a MySQL slave behind to avoid data corruption ?

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

About Shiv Iyer 36 Articles
WebScale Database Infrastructure Operations Expert in MySQL, MariaDB, PostgreSQL and ClickHouse with core interests in performance, scalability, high availability and database reliability engineering. Shiv currently is the Founder and Principal of MinervaDB, an independent and vendor neutral Consulting, 24*7 Support and Remote DBA Services provider for MySQL, MariaDB, PostgreSQL and ClickHouse serving approximately 300 customers globally.
UA-155183614-1