MySQL Replication and Read Replica Configuration

Enable and Test Read Replica Lag

To simulate and test replication lag on a read replica, execute the following commands:

1. Configure Slow Query Logging:

   SET GLOBAL slow_query_log = 1;
   SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';
   SET GLOBAL long_query_time = 1;

2. Create a Lag:

   SELECT SLEEP(2); -- This query will sleep for 2 seconds

Test Logging

To test logging, use the following configuration settings:

1. Enable General and Slow Query Logging:

   SET GLOBAL general_log = 1;
   SET GLOBAL slow_query_log = 1;
   SET GLOBAL long_query_time = 2;
   SET GLOBAL log_output = 'FILE';

2. Modify Parameter Group:

   • You cannot modify the parameter settings of a default DB parameter group. Instead, create and modify a custom DB parameter group if the parameter is modifiable.

   Steps to modify the DB parameter group:

   1. Open the Amazon RDS console.
   2. Choose Databases from the navigation pane.
   3. Select the instance you want to associate with the DB parameter group.
   4. Choose Modify.
   5. From the Database options section, select the DB parameter group you want to associate with the DB instance.

Manage Replication Delay

1. Stop Replication:

   CALL mysql.rds_stop_replication;

2. Set Replication Delay:

   CALL mysql.rds_set_source_delay(120); -- Set delay to 120 seconds

3. Start Replication:

   CALL mysql.rds_start_replication;

Show Logs

To view the logs, use the following queries:

1. General Log:

   SELECT * FROM mysql.general_log;

2. Slow Query Log:

   SELECT * FROM mysql.slow_log;

Make sure to replace /path/to/slow_query.log with the actual path where you want to store the slow query log.

Feel free to adjust the paths, parameters, or steps based on your specific requirements or environment!