2

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 ,

  • 1
    Could it be that some errors are being skipped? Run this please to check: show variables like "slave_skip_errors"; – Jehad Keriaki Apr 4 '17 at 20:32
  • Please provide more details -- such as SHOW CREATE TABLE and whether they are the same on both servers. – Rick James Apr 4 '17 at 20:35
  • @Je I just ran CALL mysql.rds_skip_repl_error; and got "Slave is running normally. No errors detected to skip." – Zeedia Apr 4 '17 at 20:41
  • @Rick usually when i got table created related error "I can see them on AWS console" with a sign that something is wrong. but currently looks fine just not replicating. and logs still moving – Zeedia Apr 4 '17 at 20:43
  • That sounds like a dangerous procedure to call without first discovering the cause. Is it possible to run SHOW SLAVE STATUS on the Slave when the problem happens? (Probably too late now.) – Rick James Apr 5 '17 at 1:53
0

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.

  • I cant run above command because I am using AWS RDS which do not give super user privileges. I posted the slave status above – Zeedia Apr 10 '17 at 17:10
  • With RDS you should be able to create a Parameter Group and set the value for binlog_format in there. Have a look at docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… for details. – thatsaru Apr 11 '17 at 13:44
  • I checked the Parameter Group I am using for the Slave and its set to to 'Mixed'. – Zeedia Apr 12 '17 at 18:09
  • Well in that case can you check if there are any limits on CPU cycles for your RDS instance? I can only assume that the RDS instance is unable to process the volume generated by data load. – thatsaru Apr 12 '17 at 18:22
0

It turned out the issue is Slave need time to catch up. we stopped migration of the legacy system during the weekend and in the beginning is this week. which lead slave to sync up with Master with no issues.

Thanks everyone for your input.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.