故障分析 | MySQL 從機故障重啟后主從同步報錯案例分析

MySQL 從庫所在主機故障重啟后，sql_thread 線程報錯：

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務領域包括：成都做網(wǎng)站、網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務，滿足客戶于互聯(lián)網(wǎng)時代的宜豐網(wǎng)站設計、移動媒體設計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡建設合作伙伴！

通過報錯信息可知，worker 線程在回放事務 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 時，由于要插入的記錄主鍵沖突報錯。

主機重啟前，主從同步正常，主機重啟后，主從同步由于主鍵沖突報錯，對比了沖突主鍵所在行記

錄在主從庫是一致的，初步分析事務 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 在主機故

障前已經(jīng)在從庫進行了回放，那為何事務會重復回放呢？

在開啟gtid模式下，如果指定 master_auto_position=1，start slave 時，從庫會把

Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集發(fā)送給主庫，主庫將收到的并集和自己的

gtid_executed 比較，把從庫 gtid 集合里缺失的事務全都發(fā)送給從庫。

主機重啟后，事務重復回放，表明 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集中有 GTID 事務

丟失，導致重復獲取事務執(zhí)行引發(fā)主鍵沖突錯誤。Retrieved_Gtid_Set 和 Executed_Gtid_Set 均為內(nèi)存變

量，MySQL 重啟后，Retrieved_Gtid_Set 初始化為空值，從而推斷出 Executed_Gtid_Set 有 GTID 事務丟

失。

Executed_Gtid_Set 來源于 gtid_executed 變量，gtid_executed 變量持久化介質有

mysql.gtid_executed 表和 binlog ，其中 mysql.gtid_executed 表是 MySQL 5.7 后引入的，在 MySQL 5.6 中，從庫要使用 GTID ，必須要先設置 log_bin=on,log_slave_updates=on ，因為從庫執(zhí)行過的 GTID 只保留在 binlog 中。

gtid_executed 變量值陳舊，推斷出 binlog 未實時持久化，我們看一下參數(shù) sync_binlog :

通過以上分析，此次故障來龍去脈就清楚了：

Worker 線程報 1062 主鍵沖突錯誤 -- gtid_executed 信息陳舊 -- binlog 未實時持久化

搭建一主一從測試環(huán)境，通過 sysbench 模擬主庫并發(fā)插入，從庫主機暴力關機后，故障復現(xiàn)：

既然錯誤原因是事務重復執(zhí)行，那跳過錯誤就好了，有如下兩種方式，根據(jù)需要選取其中一種方式執(zhí)行：

如果最新 binglog 丟失的 GTID 較多，手工執(zhí)行比較繁瑣，需要不斷試錯?？蓪懸粋€存儲過程批量執(zhí)行：

待主從同步正常后，再取消參數(shù) slave_skip_errors 設置重啟 MySQL 。

mysql 如果出現(xiàn)主從數(shù)據(jù)不一致情況怎么弄

用 pt-table-checksum 時，會不會影響業(yè)務性能？

實驗

實驗開始前，給大家分享一個小經(jīng)驗：任何性能評估，不要相信別人的評測結果，要在自己的環(huán)境上測試，并（大概）知曉原理。

我們先建一對主從：

然后用 mysqlslap跑一個持續(xù)的壓力：

開另外一個會話，將 master 上的 general log 打開：

然后通過 pt-table-checksum 進行一次比較：

查看 master 的 general log，由于 mysqlslap 的影響，general log 中有很多內(nèi)容，我們找到與 pt-table-checksum 相關的線程：

將該線程的操作單獨列出來：

操作比較多，我們一點一點來說明：

這里工具調(diào)小了 innodb 鎖等待時間。使得之后的操作，只要在 innodb 上稍微有鎖等待，就會馬上放棄操作，對業(yè)務影響很小。

另外工具調(diào)小了 wait_timeout 時間，倒是沒有特別的作用。

工具將隔離級別調(diào)整為了 RR 級別，事務的維護代價會比 RC 要高，不過后面我們會看到工具使用的每個事務都很小，加上之前提到 innodb 鎖等待時間調(diào)到很小，對線上業(yè)務產(chǎn)生的成本比較小。

RR 級別是數(shù)據(jù)對比的基本要求。

工具通過一系列操作，了解表的概況。工具是一個數(shù)據(jù)塊一個數(shù)據(jù)塊進行校驗，這里獲取了第一個數(shù)據(jù)塊的下邊界。

接下來工具獲取了下一個數(shù)據(jù)塊的下邊界，每個 SQL前都會 EXPLAIN 一下，看一下執(zhí)行成本，非常小心翼翼。

之后工具獲取了一個數(shù)據(jù)塊的 checksum，這個數(shù)據(jù)塊不大，如果跟業(yè)務流量有沖突，會馬上出發(fā) innodb 的鎖超時，立刻退讓。

以上是 pt-table-checksum 的一些設計，可以看到這幾處都是精心維護了業(yè)務流量不受影響。

工具還設計了其他的一些機制保障業(yè)務流量，比如參數(shù) --max-load 和 --pause-file 等，還有精心設計的數(shù)據(jù)塊劃分方法，索引選擇方法等。大家根據(jù)自己的情況配合使用即可達到很好的效果。

總結

本期我們介紹了簡單分析 pt-table-checksum 是否會影響業(yè)務流量，坊間會流傳工具的各種參數(shù)建議或者不建議使用，算命的情況比較多，大家都可以用簡單的實驗來分析其中機制。

還是那個觀點，性能測試不能相信道聽途說，得通過實驗去分析。

mysql主從復制發(fā)生誤操作怎么恢復

mysql主從同步常見異常及恢復方法

1. 一般的異常只需要跳過一步即可恢復

slave stop;

SET GLOBAL sql_slave_skip_counter = 1;

slave start;

2.斷電導致主從不能同步時，通主庫的最后一個bin-log日志進行恢復

在主庫服務器上，mysqlbinlog mysql-bin.xxxx binxxxx.txt

tail -n 100000 binxxxx.txt tail-binxxxx.txt

vim tail-binxxxx.txt 打開tail-binxxxx.txt文件找到最后一個postion值

然后在從庫上，change host to 相應正確的值

slave stop;

change master to master_host='ip', master_user='username', master_password='password', master_log_file='mysql-bin.xxxx', master_log_pos=xxxx;

slave start;

show slave status\G;

3.主鍵沖突、表已存在等錯誤代碼如1062,1032,1060等，可以在mysql主配置文件指定

略過此類異常并繼續(xù)下條sql同步，這樣也可以避免很多主從同步的異常中斷

[mysqld]

slave-skip-errors = 1062,1032,1060

關于mysql 主從復制的錯誤

項目上 MySQL?還原 SQL 備份經(jīng)常會碰到一個錯誤如下，且通常出現(xiàn)在導入視圖、函數(shù)、存儲過程、事件等對象時，其根本原因就是因為導入時所用賬號并不具有SUPER 權限，所以無法創(chuàng)建其他賬號的所屬對象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常見場景：1. 還原 RDS 時經(jīng)常出現(xiàn)，因為 RDS 不提供 SUPER 權限；2. 由開發(fā)庫還原到項目現(xiàn)場，賬號權限等有所不同。

處理方式：

1. 在原庫中批量修改對象所有者為導入賬號或修改?SQL SECURITY?為?Invoker；2. 使用 mysqldump 導出備份，然后將 SQL 文件中的對象所有者替換為導入賬號。

二、問題原因我們先來看下為啥會出現(xiàn)這個報錯，那就得說下 MySQL 中一個很特別的權限控制機制，像視圖、函數(shù)、存儲過程、觸發(fā)器等這些數(shù)據(jù)對象會存在一個?DEFINER?和一個?SQL SECURITY?的屬性，如下所示：

--視圖定義CREATE?ALGORITHM?=?UNDEFINED?DEFINER?=?`root`@`%`?SQL?SECURITY?DEFINER?VIEW?v_test

--函數(shù)定義CREATE?DEFINER=`root`@`%`?FUNCTION?`f_test()`?RETURNS?varchar(100)?SQL?SECURITY?DEFINER

--存儲過程定義CREATE?DEFINER=`root`@`%`?PROCEDURE?`p_test`()?SQL?SECURITY?DEFINER

--觸發(fā)器定義CREATE DEFINER=`root`@`%` trigger t_test

--事件定義CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER：對象定義者，在創(chuàng)建對象時可以手動指定用戶，不指定的話默認為當前連接用戶；

SQL SECURITY：指明以誰的權限來執(zhí)行該對象，有兩個選項，一個為?DEFINER，一個為?INVOKER，默認情況下系統(tǒng)指定為 DEFINER；DEFINER：表示按定義者的權限來執(zhí)行；?INVOKER：表示按調(diào)用者的權限來執(zhí)行。

如果導入賬號具有 SUPER 權限，即使對象的所有者賬號不存在，也可以導入成功，但是在查詢對象時，如果對象的?SQL SECURITY?為?DEFINER，則會報賬號不存在的報錯。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

三、改寫內(nèi)容上述這個 DEFINER 問題，個人想到最簡單的解決方式就是 mysqldump 導出時直接摘除掉相關屬性，但是 mysqldump 本身并不提供對應參數(shù)，所以比較蛋疼，無論是原庫走腳本變更或是備份后修改 SQL 文件都不是非常方便，尤其是觸發(fā)器的 DEFINER，只能先 DROP 再 CREATE 才可以變更。只能看下是否可以從?mysqldump 源碼中去掉 DEFINER 定義。本次?mysqldump 改寫主要有 2 個目的：1. 摘取備份中視圖、函數(shù)、存儲過程、觸發(fā)器等對象的 DEFINER 定義；2. 嘗試加上比較簡單的備份進度顯示（原生 mysqldump 的?verbose?參數(shù)不是非常清晰，想要實現(xiàn) navicate 備份時的那種行數(shù)顯示）。

改寫好處：1. 可以避免還原時遇到 DEFINER 報錯相關問題；2. 根據(jù)輸出信息知道備份是否正常進行，防止備份中遇到元數(shù)據(jù)鎖無法獲取然后一直卡住的情況。

本文標題：mysql主從錯誤怎么辦 mysql57主從
瀏覽路徑：http://chinadenli.net/article12/dogepdc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、App設計、搜索引擎優(yōu)化、Google、企業(yè)建站、微信公眾號

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)