MySQL RDS – Slave Stopped Replicating

amazon-rdsMySQLreplication

I have a master DB running on internal network and an AWS RDS slave replica from the Master DB. When I upload a fresh copy of the master, the slave will replicate for few days with no issues. After a couple of days, the slave status says its correct but records will be missing on the DB.
I ran both 

show master status

AND 

show slave status

The Master_log_file and Read_Master_log_Pos are matching.
One thing to mention we ran a huge dump on the master daily from a legacy system.

What could be the reason for the discrepancy between the two and how to fix it ?

Thanks in Advance.

Update: Today we got yesterday data. It is possible that the amount of data we are replicating everyday cause the slave to get clogged and take time to get update ?

Update 2

After leaving Slave during the weekend. the Seconds_behind_Master went
down to zero. Also here the result of show slave status

Slave_IO_State: Waiting for master to send event,
Master_Host: master_host_url,
Master_User: replica,
Master_Port: 3306,
Connect_Retry: 60,
Master_Log_File: mysql-bin.000325,
Read_Master_Log_Pos: 33015975,
Relay_Log_File: relaylog.003679,
Relay_Log_Pos: 54412,
Relay_Master_Log_File: mysql-bin.000325,
Slave_IO_Running: Yes,
Slave_SQL_Running: Yes,
Replicate_Do_DB: ,
Replicate_Ignore_DB: ,
Replicate_Do_Table: ,
Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies,
Replicate_Wild_Do_Table: ,
Replicate_Wild_Ignore_Table: ,
Last_Errno: ,
Last_Error: ,
Skip_Counter: ,
Exec_Master_Log_Pos: 33015975,
Relay_Log_Space: 142937,
Until_Condition: None,
Until_Log_File: ,
Until_Log_Pos: ,
Master_SSL_Allowed: No,
Master_SSL_CA_File: ,
Master_SSL_CA_Path: ,
Master_SSL_Cert: ,
Master_SSL_Cipher: ,
Master_SSL_Key: ,
Seconds_Behind_Master: ,
Master_SSL_Verify_Server_Cert: No,
Last_IO_Errno: ,
Last_IO_Error: ,
Last_SQL_Errno: ,
Last_SQL_Error: ,
Replicate_Ignore_Server_Ids: ,
Master_Server_Id: 1,
Master_UUID: ,
Master_Info_File: mysql.slave_master_info,
SQL_Delay: ,
SQL_Remaining_Delay: ,
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it,
Master_Retry_Count:Master_Bind: 86400,
Last_IO_Error_Timestamp: ,
Last_SQL_Error_Timestamp: ,
Master_SSL_Crl: ,
Master_SSL_Crlpath: ,
Retrieved_Gtid_Set: ,
Executed_Gtid_Set: ,
Auto_Position ,

Best Answer

Judging from the comments and your update to the question, it looks like the case where the data being loaded from legacy system is taking too long to be applied to the SLAVE.

Without the information on what your binlog_format is set to, I can only assume that it is set to ROW. You can try setting this to MIXED and then the SLAVE updates should run a lot quicker and not lag behind the master unless there are other factors affecting the SLAVE being slow.

When logged in as a privileged user, run the following from MySQL client:

SET GLOBAL binlog_format='MIXED';

If the above resolves your problem, then add this to your /etc/my.cnf to make it permanent across MySQL restarts.

Related Question