背景
MySQL從庫因為機器卡頓原因,重啟機器,機器起來后需要重新啟動從庫
結(jié)果開啟同步后報錯
Last_SQL_Error: Could not execute Delete_rows event on table pay_gateway.tbl_accounting_notifyinfo; Can't find record in 'tbl_accounting_notifyinfo', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.003660, end_log_pos 66840371
原因分析
- 因為db復制情況的信息記錄在relay log info、master info文件中(通過參數(shù)可以設置為文件,或者表)
公司master-info-repository = TABLE 、relay-log-info-repository = TABLE ,表分別為mysql.slave_master_info和 mysql.slave_relay_log_info
relay log :即讀取過來的master 的binlog ,內(nèi)容與格式與master 的binlog 一致
relay log info :記錄SQL Thread 應用的relay log 的位置、文件號等信息
master info :記錄IO Thread 讀取master 的binlog 的位置、文件號、延遲等信息 - 因此當這三個文件如果不及時落地,則主機crash后會導致數(shù)據(jù)不一致(即使雙一也會有問題,因為要考慮復制信息神馬時候保存到表中)
- master info 與relay info 有如下3 個參數(shù)控制刷新:
| 參數(shù) | 存儲在FILE中 | 存儲在table中 |
|---|---|---|
| sync_relay_log | 默認為10000 ,即每10000 次sync_relay_log events會刷新到磁盤(using fdatasync())。為0 則表示不刷新,交由OS 的cache 控制。大于0 則表示N個events寫到Relay log后,刷到磁盤.IO線程復制到緩存后寫入磁盤 | |
| sync_master_info | 當設置為0 ,則與events無關(guān),由OS處理;如果大于0,則表示在sync_master_info次transactions后刷到磁盤,默認為10000 次刷新到磁盤 | 當設置為0 ,則表不做任何更新,設置為1 ,則每次事件會更新表 # 默認為10000 |
| sync_relay_log_info | 當設置為0 ,交由OS 刷新磁盤;如果大于0,則sync_relay_log_info次transactions后刷到磁盤.默認為10000 次刷新到磁盤 | INNODB 存儲(支持事務),則無論為任何值,則都每次event 都會更新表(無論此參數(shù)值為多少)。 如果是非事務存儲引擎,則為0時不更新表,大于0時則在N次events后更新.默認值都是10000 |
- 使用命令查看IO線程與SQL線程執(zhí)行情況
mysql> select Master_log_name,Master_log_pos,'IO Thread 讀取Master的positon' comments from mysql.slave_master_info
union select Master_log_name,Master_log_pos,'SQL Thread 執(zhí)行Master的positon' comments from mysql.slave_relay_log_info;
- 導致復制出錯的原因
在mysql意外crash的情況下,默認參數(shù)配置slave_relay_log_info記錄的Master_log_name、Master_log_pos 要大于或等于 mysql.slave_master_info記錄的Master_log_name、Master_log_pos
而重啟復制后，IO線程拉主庫binlog的postion是從slave_master_info記錄的點開始的，會導致重復拉取
slave真正執(zhí)行的點是slave_relay_log_info記錄的點,由于重復拉取binlog，relay log會有重復，自然sql執(zhí)行時會有沖突。
解決問題
- 修改參數(shù)配置 sync_relay_log = 1 sync_master_info = 1 sync_relay_log_info = 1,機器斷電重啟后DB復制正常,問題解決(但是會增加很大的負擔)
- 跳過錯誤 change,或者設置skip_errors等
- 有個參數(shù)可以讓mysql自動從最新的點開始同步
延伸問題
如果mysql.slave_master_info 的記錄的點要比mysql. slave_relay_log_info點大 機器意外crash,重啟后依然會有錯誤
問題分析
- SQL thread啟動的位置和slave_relay_log_info 記錄的位置一致(478386666) 。 IO thread 啟動的位置由于mysql.slave_master_info 表記錄被更新所以實際啟動的點要大。
當db非正常關(guān)閉時,mysql.slave_master_info 表記錄由于sync_master_info=10000 的設置導致部分event 信息沒有記錄到表中。
但是實際已經(jīng)生成relay log 。那啟動的時候IO Thread 是根據(jù)表中的記錄開始啟動IO ,這樣就會導致沒有記錄到表中的relay log 被重復拉取了一次,SQL thread 重復應用
這段relay log 導致復制中斷。那么這種問題是執(zhí)行了重復拉取的一段relay log 導致主鍵沖突,跳過錯誤即可,并且數(shù)據(jù)不會丟失。
解決方法
- 設置sync_master_info=1 讓表實時更新記錄即可解決問題
- 手動重新change 一次也可避免復制中斷
并且手動change 的點應該是按照mysql.slave_relay_log_info 表記錄的點
為什么都是sql thread 都是同一個位置啟動,為什么需要手動change 呢?
因為重新change 是會清空現(xiàn)有的relay log ,從指定位置重新拉取,而直接啟動則是續(xù)寫之前的relay log 。
解決方案匯總
- DB 復制正常關(guān)閉情況直接啟動復制即可,如果非正常關(guān)閉則需要以mysql.slave_relay_log_info表記錄的點重新change,記得一定要重新change,這樣
可以清空之前生成的relay log重新拉取。 - 使用GTID復制,無需file、positon ,兩張表中不會記錄任何信息
- 設置參數(shù)為1,實時更新表記錄。
sync_relay_log = 1
sync_master_info = 1 sync_relay_log_info = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE - 每次啟動check一下slave_master_info和slave_relay_log_info 記錄的positon是否一致,如果不一致按照slave_relay_log_info的點重新change后重啟復制,切記需要重新change
- 明白這種復制中斷的原因,跳過即可。不推薦設置為全1,IO壓力加大會導致性能瓶頸,從監(jiān)控看從20飆到60,機器還是單實例、無業(yè)務流量的情景。
- 啟動時加上 --relay-log-recovery 參數(shù) ,會在server啟動時新創(chuàng)建一個Relay log文件，并讓SQL線程指向新的Relay log，讓IO線程的位置指向SQL線程執(zhí)行的master位置然后繼續(xù)讀取Master的binlog
