Recovery of Stopped AWS RDS MySQL Replication
2017-01-03 21:34 (7 years ago)
ytyng
Something is wrong with AWS RDS MySQL replication
There is a MyIsam table,
CHECK TABLE xxxx_xxxx;
When I tried
Table Op Msg_type Msg_text
xxxx.xxxx_xxxx check error Corrupt
The Msg_text says Corrupt. It tells me that this table is corrupted. The master side has no issues.
When I look at the RDS page on the AWS console
Replication is failing
Looking at SHOW SLAVE STATUS
{
"data":
[
{
"Slave_IO_State": "Waiting for master to send event",
"Master_Host": "10.7.1.169",
"Master_User": "rdsrepladmin",
"Master_Port": 3306,
"Connect_Retry": 60,
"Master_Log_File": "mysql-bin-changelog.131739",
"Read_Master_Log_Pos": 743597,
"Relay_Log_File": "relaylog.000242",
"Relay_Log_Pos": 496325,
"Relay_Master_Log_File": "mysql-bin-changelog.131676",
"Slave_IO_Running": "Yes",
"Slave_SQL_Running": "No",
"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": 1053,
"Last_Error": "Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'UPDATE xxxxx SET xxxxxxxxxxxxxxxxxxx'",
"Skip_Counter": 0,
"Exec_Master_Log_Pos": 496152,
"Relay_Log_Space": 428001772,
"Until_Condition": "None",
"Until_Log_File": "",
"Until_Log_Pos": 0,
"Master_SSL_Allowed": "No",
"Master_SSL_CA_File": "",
"Master_SSL_CA_Path": "",
"Master_SSL_Cert": "",
"Master_SSL_Cipher": "",
"Master_SSL_Key": "",
"Seconds_Behind_Master": null,
"Master_SSL_Verify_Server_Cert": "No",
"Last_IO_Errno": 0,
"Last_IO_Error": "",
"Last_SQL_Errno": 1053,
"Last_SQL_Error": "Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: 'UPDATE xxxxx SET xxxxxxxxxxxxxxxxxxx'",
"Replicate_Ignore_Server_Ids": "",
"Master_Server_Id": 1498488124,
"Master_UUID": "e247ce2a-13ee-11e5-8f81-0adacdd7d587",
"Master_Info_File": "mysql.slave_master_info",
"SQL_Delay": 0,
"SQL_Remaining_Delay": null,
"Slave_SQL_Running_State": "",
"Master_Retry_Count": 86400,
"Master_Bind": "",
"Last_IO_Error_Timestamp": "",
"Last_SQL_Error_Timestamp": "160913 09:37:11",
"Master_SSL_Crl": "",
"Master_SSL_Crlpath": "",
"Retrieved_Gtid_Set": "",
"Executed_Gtid_Set": "",
"Auto_Position": 0
}
]
}
It's stuck on a force-stopped query
"Slave_IO_Running": "Yes", "Slave_SQL_Running": "No",
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
→ Access Denied!
Cannot recover
Referring to this
CALL mysql.rds_skip_repl_error;
"Slave_IO_Running": "Yes", "Slave_SQL_Running": "Yes",
Fixed
Comments