今天在MySQL同步中再次遇到了令人討厭的 1062與1032錯(cuò)誤。
對(duì)于MySQL的replication，感覺(jué)有點(diǎn)不太靠譜，，我不知道其他DBA都是用哪些同步方案。
在思考是否需要換個(gè)同步方案，隨著訪問(wèn)量的增加，一主一備看來(lái)也比較脆弱了。宕機(jī)風(fēng)險(xiǎn)也高。
先前態(tài)度比較樂(lè)觀。根據(jù)錯(cuò)誤的提示：
20131128_14:56:09mysql> show slave status/G;20131128_14:56:09*************************** 1. row ***************************20131128_14:56:09 Slave_IO_State: Waiting for master to send event20131128_14:56:09 Master_Host: 192.168.101.21020131128_14:56:09 Master_User: backup20131128_14:56:09 Master_Port: 330620131128_14:56:09 Connect_Retry: 6020131128_14:56:09 Master_Log_File: mysql-bin.00147220131128_14:56:09 Read_Master_Log_Pos: 33932892420131128_14:56:09 Relay_Log_File: hostname-relay-bin.00451320131128_14:56:09 Relay_Log_Pos: 6663598520131128_14:56:09 Relay_Master_Log_File: mysql-bin.00147220131128_14:56:09 Slave_IO_Running: Yes20131128_14:56:09 Slave_SQL_Running: No20131128_14:56:09 Replicate_Do_DB: 20131128_14:56:09 Replicate_Ignore_DB: mysql,test20131128_14:56:09 Replicate_Do_Table: 20131128_14:56:09 Replicate_Ignore_Table: 20131128_14:56:09 Replicate_Wild_Do_Table: 20131128_14:56:09 Replicate_Wild_Ignore_Table: 20131128_14:56:09 Last_Errno: 103220131128_14:56:09 Last_Error: Could not execute Update_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '20551928' fo20131128_14:56:09r key 'PRIMARY', Error_code: 1062; Can't find record in 'uc_member_info', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the ev20131128_14:56:09ent's master log mysql-bin.001472, end_log_pos 6663664620131128_14:56:09 Skip_Counter: 020131128_14:56:09 Exec_Master_Log_Pos: 6663583920131128_14:56:09 Relay_Log_Space: 33933628120131128_14:56:09 Until_Condition: None20131128_14:56:09 Until_Log_File: 20131128_14:56:09 Until_Log_Pos: 020131128_14:56:09 Master_SSL_Allowed: No20131128_14:56:09 Master_SSL_CA_File: 20131128_14:56:09 Master_SSL_CA_Path: 20131128_14:56:09 Master_SSL_Cert: 20131128_14:56:09 Master_SSL_Cipher: 20131128_14:56:09 Master_SSL_Key: 20131128_14:56:09 Seconds_Behind_Master: NULL20131128_14:56:09Master_SSL_Verify_Server_Cert: No20131128_14:56:09 Last_IO_Errno: 020131128_14:56:09 Last_IO_Error: 20131128_14:56:09 Last_SQL_Errno: 103220131128_14:56:09 Last_SQL_Error: Could not execute Update_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '20551928' fo20131128_14:56:09r key 'PRIMARY', Error_code: 1062; Can't find record in 'uc_member_info', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the ev20131128_14:56:09ent's master log mysql-bin.001472, end_log_pos 6663664620131128_14:56:09 Replicate_Ignore_Server_Ids: 20131128_14:56:09 Master_Server_Id: 1
發(fā)現(xiàn)時(shí)主鍵重復(fù)與更新失敗。
主鍵重復(fù)的情況按照常理說(shuō)可以直接跳過(guò)，所以我寫(xiě)好了命令：
命令1：stop slave sql_thread;set global sql_slave_skip_counter=1;start slave sql_thread;
Mysql 的Replication主要兩個(gè)線程：1:IO_Thread 2:SQL_Thread;
網(wǎng)上都是建議直接stop slave或者start slave.這里主要是sql_thread的異常中斷，所以我只重啟sql_thread;
然后執(zhí)行過(guò)命令1之后，發(fā)現(xiàn)這種情況不停的發(fā)生。不停的一次次跳過(guò)太過(guò)繁瑣，通過(guò)問(wèn)題來(lái)看主要是針對(duì)表
feiliu_bbs.uc_member_info
最終實(shí)在忍受不了，就打算從主庫(kù)dump一份最新的數(shù)據(jù)到備份。于是我做了如下操作：
0：停止slave的同步進(jìn)程
stop slave;
1：在master端手動(dòng)備份目標(biāo)表：uc_member_info,改名為uc_member_info_bak
create table uc_member_info_bak
select * from uc_member_info where 1=2;
2：導(dǎo)出表
mysqldump -uroot -p dbname tablename > tablename.sql
3:scp到備庫(kù)，然后導(dǎo)入
mysql>source tablename.sql;
4:然后切換表名
切換之前，在新表uc_member_info_bak中補(bǔ)加原表uc_member_info 中的索引；
補(bǔ)加表中原有的索引：
create index idx_xxxx on uc_member_info_bak(cloumn_name);
切換表名：
alter table uc_member_info rename to uc_member_info_old;
alter table uc_member_info_bak rename to uc_member_info;5:開(kāi)啟同步進(jìn)程
start slave;
由于my.cnf中已經(jīng)設(shè)置跳過(guò)主鍵重復(fù)錯(cuò)誤。
slave-skip-errors = 1062
error錯(cuò)誤日志中會(huì)不停彈出如下錯(cuò)誤信息。
130922 12:23:20 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564914' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694223737, Error_code: 1062130922 12:23:21 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564915' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694274279, Error_code: 1062130922 12:23:21 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564916' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694278383, Error_code: 1062130922 12:23:21 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564917' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694326934, Error_code: 1062130922 12:23:21 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564918' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694340338, Error_code: 1062
備庫(kù)現(xiàn)有的的uc_member_info是從主庫(kù)dump過(guò)來(lái)的，比原有的備庫(kù)表uc_member_info_old 進(jìn)度要快。因?yàn)閭鋷?kù)與主庫(kù)之間存在較長(zhǎng)時(shí)間的間隔，主庫(kù)的binlog，在同步停止的時(shí)間內(nèi)，沒(méi)有及時(shí)發(fā)到備庫(kù)導(dǎo)致。
解決方法：
首先停止同步，然后根據(jù)最后一條錯(cuò)誤信息：
130922 12:23:21 [Warning] Slave SQL: Could not execute Write_rows event on table feiliu_bbs.uc_member_info; Duplicate entry '17564918' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001185, end_log_pos 694340338, Error_code: 1062
刪除之后，恢復(fù)同步。就不在出現(xiàn)主鍵重復(fù)的錯(cuò)誤提示了。
等待同步，大約半個(gè)小時(shí)之后，仍然會(huì)出現(xiàn)1032錯(cuò)誤。仍舊是表uc_member_info.現(xiàn)在懷疑應(yīng)該不單單是單表的問(wèn)題，而是數(shù)據(jù)庫(kù)本身的一致性已經(jīng)處理問(wèn)題。準(zhǔn)備放棄，進(jìn)行重做。但是目前不能讓備庫(kù)停止，不能影響其他數(shù)據(jù)庫(kù)的正常讀取業(yè)務(wù)。故準(zhǔn)備采用殺手锏！
準(zhǔn)備進(jìn)行設(shè)置數(shù)據(jù)庫(kù)跳過(guò)一般的錯(cuò)誤異常，使之不會(huì)輕易停止同步。
使用：slave_exec_mode參數(shù)
（具體參數(shù)說(shuō)明 http://blog.csdn.net/zhangbiaobiaobiao/article/details/17072199 這里記錄過(guò)大概。）
然后準(zhǔn)備后續(xù)的主備的一致性驗(yàn)證與備份重做。
bitsCN.com聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 51dongshi.com 版權(quán)所有
違法及侵權(quán)請(qǐng)聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬(wàn)商天勤律師事務(wù)所王興未律師提供法律服務(wù)
