AWS RDS MySQLのレプリケーションが止まったので復旧
2017-01-03 21:34 (7年前)
ytyng
AWS RDS MySQL のレプリケーションの様子がおかしい
MyIsam テーブルがありますが、
CHECK TABLE xxxx_xxxx;
してみると
Table Op Msg_type Msg_text
xxxx.xxxx_xxxx check error Corrupt
Msg_text が Corrupt。このテーブルは腐ってると言われます。マスターの方は問題無し。
AWS コンソールの RDS ページを見てみると
レプリケーションが失敗している
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
}
]
}
強制停止したクエリで引っかかっている
"Slave_IO_Running": "Yes",
"Slave_SQL_Running": "No",
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
→ Access Denied!
復旧できない
こちらを参考に
CALL mysql.rds_skip_repl_error;
"Slave_IO_Running": "Yes",
"Slave_SQL_Running": "Yes",
なおった
コメント