AWS RDS MySQLのレプリケーションが止まったので復旧

Posted by: ytyng 5 years, 4 months ago
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!
復旧できない
【AWS,RDS,MySQL】RDS(MySQL)のリードレプリカでレプリケーションエラーが出た際の対処 - へろへろもへじ
こちらを参考に
CALL mysql.rds_skip_repl_error;
"Slave_IO_Running": "Yes",
"Slave_SQL_Running": "Yes",
なおった
Currently unrated

Comments

Archive

2022
2021
2020
2019
2018
2017
2016
2015
2014
2013
2012
2011