前言
在發生故障切換後,經常遇到的問題就是同步報錯,資料庫很小的時候,dump完再導入很簡單就處理好了,但線上的資料庫都150G-200G,如果用單純的這種方法,成本太高,故經過一段時間的摸索,總結了幾種處理方法。
生産環境架構圖
目前現網的架構,儲存着兩份資料,通過異步複制做的高可用叢集,兩台機器提供對外服務。在發生故障時,切換到slave上,并将其變成master,壞掉的機器反向同步新的master,在處理故障時,遇到最多的就是主從報錯。下面是我收錄下來的報錯資訊。
常見錯誤
最常見的3種情況
這3種情況是在HA切換時,由于是異步複制,且sync_binlog=0,會造成一小部分binlog沒接收完導緻同步報錯。
第一種:在master上删除一條記錄,而slave上找不到。
Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254
第二種:主鍵重複。在slave已經有該記錄,又在master上插入了同一條記錄。
Last_SQL_Error: Could not execute Write_rows event on table hcy.t1;
Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924
第三種:在master上更新一條記錄,而slave上找不到,丢失了資料。
Last_SQL_Error: Could not execute Update_rows event on table hcy.t1;
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263
異步半同步差別
異步複制
簡單的說就是master把binlog發送過去,不管slave是否接收完,也不管是否執行完,這一動作就結束了.
半同步複制
簡單的說就是master把binlog發送過去,slave确認接收完,但不管它是否執行完,給master一個信号我這邊收到了,這一動作就結束了。(谷歌寫的代碼,5.5上正式應用。)
異步的劣勢
當master上寫操作繁忙時,目前POS點例如是10,而slave上IO_THREAD線程接收過來的是3,此時master當機,會造成相差7個點未傳送到slave上而資料丢失。
特殊的情況
slave的中繼日志relay-bin損壞。
Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number;
It's not a binary log file that can be used by this version of MySQL
這種情況SLAVE在當機,或者非法關機,例如電源故障、主機闆燒了等,造成中繼日志損壞,同步停掉。
人為失誤需謹慎:多台slave存在重複server-id
這種情況同步會一直延時,永遠也同步不完,error錯誤日志裡一直出現上面兩行資訊。解決方法就是把server-id改成不一緻即可。
Slave: received end packet from server, apparent master shutdown:
Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000012' at postion 106
問題處理
删除失敗
在master上删除一條記錄,而slave上找不到。
Can't find record in 't1',
解決方法:
由于master要删除一條記錄,而slave上找不到故報錯,這種情況主上都将其删除了,那麼從機可以直接跳過。可用指令:
stop slave;
set global sql_slave_skip_counter=1;
start slave;
如果這種情況很多,可用我寫的一個腳本skip_error_replcation.sh,預設跳過10個錯誤(隻針對這種情況才跳,其他情況輸出錯誤結果,等待處理),這個腳本是參考maakit工具包的mk-slave-restart原理用shell寫的,功能上定義了一些自己的東西,不是無論什麼錯誤都一律跳過。)
主鍵重複
在slave已經有該記錄,又在master上插入了同一條記錄。
? |
在slave上用desc hcy.t1; 先看下表結構:
|
删除重複的主鍵
|
在master上和slave上再分别确認一下。
更新丢失
在master上更新一條記錄,而slave上找不到,丢失了資料。
|
在master上,用mysqlbinlog 分析下出錯的binlog日志在幹什麼。
|
在slave上,查找下更新後的那條記錄,應該是不存在的。
mysql> select * from t1 where id=2;
Empty set (0.00 sec)
然後再到master檢視
|
把丢失的資料在slave上填補,然後跳過報錯即可。
|
中繼日志損壞
|
手工修複
解決方法:找到同步的binlog和POS點,然後重新做同步,這樣就可以有新的中繼日值了。
例子:
|
Slave_IO_Running :接收master的binlog資訊
Master_Log_File
Read_Master_Log_Pos
Slave_SQL_Running:執行寫操作
Relay_Master_Log_File
Exec_Master_Log_Pos
以執行寫的binlog和POS點為準。
|
各種大招都用上了,無奈slave資料丢失過多,ibbackup(需要銀子)該你登場了。
Ibbackup熱備份工具,是付費的。xtrabackup是免費的,功能上一樣。
Ibbackup備份期間不鎖表,備份時開啟一個事務(相當于做一個快照),然後會記錄一個點,之後資料的更改儲存在ibbackup_logfile檔案裡,恢複時把ibbackup_logfile 變化的資料再寫入到ibdata裡。
Ibbackup 隻備份資料( ibdata、.ibd ),表結構.frm不備份。
下面一個示範例子:
備份:ibbackup /bak/etc/my_local.cnf /bak/etc/my_bak.cnf
恢複:ibbackup --apply-log /bak/etc/my_bak.cnf
|
把ibdata1 ib_logfile0 ib_logfile1拷貝到從,把.frm也拷貝過去,啟動MySQL後,做同步,那個點就是上面輸出的:
ibbackup: Last MySQL binlog file position 0 1191, file name ./mysql-bin.000010
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=1191;
Maatkit工具包
http://www.maatkit.org/
簡介
maatkit是一個開源的工具包,為mysql日常管理提供了幫助。目前,已被Percona公司收購并維護。其中:
mk-table-checksum是用來檢測master和slave上的表結構和資料是否一緻。
mk-table-sync是發生主從資料不一緻時,來修複的。
這兩個工具包,沒有在現網實際操作的經驗,這裡僅僅是新技術探讨和學術交流,下面展示下如何使用。
http://www.actionsky.com/products/mysql-others/maatkit.jsp
|
提示缺少perl-DBI子產品,那麼直接 yum install perl-DBI。
|
如果表資料不一緻,CHECKSUM的值是不相等的。
解釋下輸出的意思:
DATABASE:資料庫名
TABLE:表名
CHUNK:checksum時的近似數值
HOST:MYSQL的位址
ENGINE:表引擎
COUNT:表的行數
CHECKSUM:校驗值
TIME:所用時間
WAIT:等待時間
STAT:MASTER_POS_WAIT()傳回值
LAG:slave的延時時間
如果你想過濾出不相等的都有哪些表,可以用mk-checksum-filter這個工具,隻要在後面加個管道符就行了。
|
知道有哪些表不一緻,可以用mk-table-sync這個工具來處理。
注:在執行mk-table-checksum時會鎖表,表的大小取決于執行的快慢。
MASTER上的t2表資料:
SLAVE上的t2表資料:
|
它的工作原理是:先一行一行檢查主從庫的表是否一樣,如果哪裡不一樣,就執行删除,更新,插入等操作,使其達到一緻。表的大小決定着執行的快慢。
|
MySQL複制監控
MySQL常見錯誤類型
1005:建立表失敗
1006:建立資料庫失敗
1007:資料庫已存在,建立資料庫失敗
1008:資料庫不存在,删除資料庫失敗
1009:不能删除資料庫檔案導緻删除資料庫失敗
1010:不能删除資料目錄導緻删除資料庫失敗
1011:删除資料庫檔案失敗
1012:不能讀取系統表中的記錄
1020:記錄已被其他使用者修改
1021:硬碟剩餘空間不足,請加大硬碟可用空間
1022:關鍵字重複,更改記錄失敗
1023:關閉時發生錯誤
1024:讀檔案錯誤
1025:更改名字時發生錯誤
1026:寫檔案錯誤
1032:記錄不存在
1036:資料表是隻讀的,不能對它進行修改
1037:系統記憶體不足,請重新開機資料庫或重新開機伺服器
1038:用于排序的記憶體不足,請增大排序緩沖區
1040:已到達資料庫的最大連接配接數,請加大資料庫可用連接配接數
1041:系統記憶體不足
1042:無效的主機名
1043:無效連接配接
1044:目前使用者沒有通路資料庫的權限
1045:不能連接配接資料庫,使用者名或密碼錯誤
1048:字段不能為空
1049:資料庫不存在
1050:資料表已存在
1051:資料表不存在
1054:字段不存在
1065:無效的SQL語句,SQL語句為空
1081:不能建立Socket連接配接
1114:資料表已滿,不能容納任何記錄
1116:打開的資料表太多
1129:資料庫出現異常,請重新開機資料庫
1130:連接配接資料庫失敗,沒有連接配接資料庫的權限
1133:資料庫使用者不存在
1141:目前使用者無權通路資料庫
1142:目前使用者無權通路資料表
1143:目前使用者無權通路資料表中的字段
1146:資料表不存在
1147:未定義使用者對資料表的通路權限
1149:SQL語句文法錯誤
1158:網絡錯誤,出現讀錯誤,請檢查網絡連接配接狀況
1159:網絡錯誤,讀逾時,請檢查網絡連接配接狀況
1160:網絡錯誤,出現寫錯誤,請檢查網絡連接配接狀況
1161:網絡錯誤,寫逾時,請檢查網絡連接配接狀況
1062:字段值重複,入庫失敗
1169:字段值重複,更新記錄失敗
1177:打開資料表失敗
1180:送出事務失敗
1181:復原事務失敗
1203:目前使用者和資料庫建立的連接配接已到達資料庫的最大連接配接數,請增大可用的資料庫連接配接數或重新開機資料庫
1205:加鎖逾時
1211:目前使用者沒有建立使用者的權限
1216:外鍵限制檢查失敗,更新子表記錄失敗
1217:外鍵限制檢查失敗,删除或修改主表記錄失敗
1226:目前使用者使用的資源已超過所允許的資源,請重新開機資料庫或重新開機伺服器
1227:權限不足,您無權進行此操作
1235:MySQL版本過低,不具有本功能
複制監控腳本
參考原文修改。
原腳本
|
修改後腳本
隻做了簡單的整理,修正了Behind為NULL的判斷,但均未測試;
應可考慮增加:
對修複執行結果的判斷;多條錯誤的循環修複、檢測、再修複?
取消SlaveStatusFile臨時檔案。
Errno、Behind兩種告警分别發郵件,告警正文增加show slave結果原文。
增加PATH,以便加到crontab中。
考慮crontab中周期執行(加鎖避免執行沖突、執行周期選擇)
增加執行日志?
|
以上這篇線上MYSQL同步報錯故障處理方法總結(必看篇)就是小編分享給大家的全部内容了,希望能給大家一個參考,也希望大家多多支援腳本之家。
原文網址:https://www.jb51.net/article/109107.htm