Unlock the power of delayed replication in Amazon RDS for MySQL! This blog post dives deep into enabling and leveraging this feature for data recovery, backups, and ETL/analytics workflows.

Delayed replication is one of the important features which were being supported in MySQL from 5.6 for a very long time. This induces an intentional lag in the slave, making it lag by the defined time interval.

For a long time this was not available with the RDS version of MySQL provided by AWS, Recently from the version 5.6.40, 5.722 and later versions this feature is available with all the regions.

I will give a small intro on Amazon RDS, Which is DBAAS provided by Amazon, where you will be given an end-point for all your DB operations and major of admin task of server and DB is taken care by Amazon, To know more you can view our presentation here

I will demonstrate, how to have a delayed slave with Amazon RDS for MySQL

Note: If you are running with older versions of MySQL, please patch-up to 5.6.40 and 5.7.22. It’s pretty simple and straightforward.

Below is the environment for this demo.

Instance Type: M4.large (8GB & 2 VCPU)
MySQL Version : 5.6.40 Source distribution

Am inducing intentional lag in an existing slave, which is already in sync with the master.

Stopping the replication

 
mysql> CALL mysql.rds_stop_replication;
+---------------------------+
| Message |
+---------------------------+
| Slave is down or disabled |
+---------------------------+
1 row in set (1.02 sec)
Query OK, 0 rows affected (1.02 sec)
	

Add delay-interval in secs:

 
mysql> call mysql.rds_set_source_delay (3600);
+-----------------------------------+
| Message |
+-----------------------------------+
| source delay is set successfully. |
+-----------------------------------+
1 row in set (0.35 sec)
Query OK, 0 rows affected (0.35 sec)
	

Once After adding the delay, you can see a stall in the SQL thread and lag starting to build up in the slave status.

 
Exec_Master_Log_Pos: 24399479
Relay_Log_Space: 124626119
Seconds_Behind_Master: 244
SQL_Delay: 3600
SQL_Remaining_Delay: 3356
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed even
	

Once the slave is delayed with the required time interval of 1 hour, you can find the status as below.

 
Exec_Master_Log_Pos: 16928918
Relay_Log_Space: 2670652450
Seconds_Behind_Master: 3600
SQL_Delay: 3600
SQL_Remaining_Delay: 0
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed even.
	

You can also check the status as below.

 
mysql> call mysql.rds_show_configuration;
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| name | value | description |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| binlog retention hours | 4 | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
| source delay | 3600 | source delay specifies replication delay in seconds between current instance and its master. |
| target delay | 0 | target delay specifies replication delay in seconds between current instance and its future read-replica. |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
	

Benefits:

Below are some of the benefits of having a delayed slave.

• Recovery of data during a human error, Such as accidental delete or drop of a table
• As a backup server
• For ETL/ Analytics with acceptable delay.

Thanks for your time and attention. Next waiting for the filtered replication to be implemented with RDS, for solving many of the use-cases.

Empower your MySQL administration with Mydbops' open-source database management tools. Streamline tasks, optimize performance, and manage replication with ease.

{{cta}}