MySQL 5.7復(fù)制的一個小bug-XA事務(wù)

線上一個5.7從庫復(fù)制中斷：

成都創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站設(shè)計制作、網(wǎng)站設(shè)計、紅山網(wǎng)絡(luò)推廣、小程序制作、紅山網(wǎng)絡(luò)營銷、紅山企業(yè)策劃、紅山品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供紅山建站搭建服務(wù)，24小時服務(wù)熱線：18980820575，官方網(wǎng)址：www.cdcxhl.com

查詢具體報錯：

第一感覺很奇怪，為什么會rollback失敗呢？于是根據(jù)gtid去對應(yīng)的主庫binlog去看了下，并沒有任何rollback語句：

看下本地的relay log，找到這個事務(wù)的gtid

到這里，這個relay log日志文件結(jié)束了。很顯然問題也找到了，就是執(zhí)行

出錯了。

首先 我們看到 這個rollback是MySQL自己加上去的，那么為什么要加呢？

mysql為了保證一個事務(wù)只在一個binlog里，所以當Binlog或者relay log發(fā)生截斷時，最后一個事務(wù)要么commit，要么rollback，如果rollback，那么下一個binlog或者relay log會把這個事務(wù)重做一遍，保證這個事務(wù)不會丟。

由于xa事務(wù)無法直接rollback，而需要xa rollback ‘XXX’，所以復(fù)制就停了。

怎么修復(fù)？是不是直接跳過這個rollback就行了？

我們來試一下，跳過這個rollback:

這里GTID_NEXT值不能用show slave status的里executed值，得用具體報錯的停止的gtid

但是，show slave status看到，還是有報錯：

為什么又報這個事務(wù)commit找不到XID呢？

之前說過，在relaylog截斷的時候，如果事務(wù)沒有commit,會自動在最后加rollback，在下一個relay log開始的時候重新做一次這個事務(wù)，按理說我們跳過這個rollback，在下個relaylog會被重做，為啥會在commit的時候找不到xid呢？

我們看到我們跳過的gtid原來就是重做這個事務(wù)到PREPARE階段的gtid，原來，rollback是沒有g(shù)tid的，所以我們實際上就是把這個事務(wù)到PREPARE階段的gtid給跳過了，commit的時候肯定會找不到xid，接著怎么修復(fù)？

為什么這是5.7的一個bug呢？5.7之前的版本因為relaylog被截斷并不會出現(xiàn)這個bug。

5.7對xa事務(wù)的binlog記錄方式做了修改，把 xa start,xa end,xa prepare放到一個event里，xa commit又是另外一個event。而在之前的MySQL版本中，整個xa事務(wù)從start到commit都是在一個event中，所以其他版本并沒有問題。

最后一個問題：5.7為啥要把xa事務(wù)拆成兩個event？簡單的講是為了數(shù)據(jù)安全性。

5.5或者5.6假設(shè)下面一個場景：

MySQL在某個分布式事務(wù)prepare成功后宕機，宕機前操作該事務(wù)的連接并沒有斷開（如果在宕機前斷開連接，事務(wù)會被MySQL自動回滾），這個時候已經(jīng)prepare的事務(wù)并不會被回滾，所以在MySQL重新啟動后，引擎層通過recover機制能恢復(fù)該事務(wù)。當然該事務(wù)的Binlog已經(jīng)在宕機過程中被丟失，這個時候，如果去提交，則會造成主從數(shù)據(jù)的不一致， 即提交沒有記錄Binlog，從上丟失該條數(shù)據(jù)。

正因為5.7之前版本的xa事務(wù)存在這個bug，5.7后做了修復(fù)。從XA START到XA PREPARE之間的操作都被記錄到了Master的Binlog中，然后通過復(fù)制關(guān)系傳到了Slave上。也就是說5.7開始，MySQL對于XA事務(wù)，在prepare的時候就完成了寫B(tài)inlog的操作，通過新增一種叫 XA_prepare_log_event 的event類型來實現(xiàn)。

其實 MySQL5.7在xa事務(wù)上遠不止這個bug，后面再來慢慢總結(jié)。

MySQL出現(xiàn)錯誤怎么辦？

1、首先檢查是否已安裝MySQL服務(wù)，如果沒有安裝，則需要安裝MySQL服務(wù)。

2、然后檢查MySQL服務(wù)是否已經(jīng)在系統(tǒng)服務(wù)列表中，如果不在則需要手動添加MySQL服務(wù)。

3、檢查MySQL的配置文件my.ini是否正確，如果不正確則需要修改配置文件。

4、檢查Windows服務(wù)管理器中MySQL服務(wù)的狀態(tài)是否處于“運行”狀態(tài)，如果不是，則需要手動啟動MySQL服務(wù)。

拓展：

如果以上步驟都無法解決問題，可以嘗試更新MySQL安裝包，或者重新安裝MySQL服務(wù)。如果仍然無法解決，則可以嘗試檢查MySQL的數(shù)據(jù)庫文件是否損壞，如果損壞則需要進行修復(fù)。

mysql復(fù)制表文件錯誤

項目上 MySQL?還原 SQL 備份經(jīng)常會碰到一個錯誤如下，且通常出現(xiàn)在導(dǎo)入視圖、函數(shù)、存儲過程、事件等對象時，其根本原因就是因為導(dǎo)入時所用賬號并不具有SUPER 權(quán)限，所以無法創(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 權(quán)限；2. 由開發(fā)庫還原到項目現(xiàn)場，賬號權(quán)限等有所不同。

處理方式：

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

二、問題原因我們先來看下為啥會出現(xiàn)這個報錯，那就得說下 MySQL 中一個很特別的權(quán)限控制機制，像視圖、函數(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：指明以誰的權(quán)限來執(zhí)行該對象，有兩個選項，一個為?DEFINER，一個為?INVOKER，默認情況下系統(tǒng)指定為 DEFINER；DEFINER：表示按定義者的權(quán)限來執(zhí)行；?INVOKER：表示按調(diào)用者的權(quán)限來執(zhí)行。

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

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

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

mysql2016數(shù)據(jù)庫表復(fù)制粘貼報錯,如何終止導(dǎo)入？

如果從庫上表 t 數(shù)據(jù)與主庫不一致，導(dǎo)致復(fù)制錯誤，整個庫的數(shù)據(jù)量很大，重做從庫很慢，如何單獨恢復(fù)這張表的數(shù)據(jù)？通常認為是不能修復(fù)單表數(shù)據(jù)的，因為涉及到各表狀態(tài)不一致的問題。下面就列舉備份單表恢復(fù)到從庫會面臨的問題以及解決辦法：

場景 1

如果復(fù)制報錯后，沒有使用跳過錯誤、復(fù)制過濾等方法修復(fù)主從復(fù)制。主庫數(shù)據(jù)一直在更新，從庫數(shù)據(jù)停滯在報錯狀態(tài)（假設(shè) GTID 為 aaaa:1-100）。

修復(fù)步驟：

在主庫上備份表 t （假設(shè)備份快照 GTID 為 aaaa:1-10000）；

恢復(fù)到從庫；

啟動復(fù)制。

這里的問題是復(fù)制起始位點是 aaaa:101，從庫上表 t 的數(shù)據(jù)狀態(tài)是領(lǐng)先其他表的。aaaa:101-10000 這些事務(wù)中只要有修改表 t 數(shù)據(jù)的事務(wù)，就會導(dǎo)致復(fù)制報錯 ，比如主鍵沖突、記錄不存在（而 aaaa:101 這個之前復(fù)制報錯的事務(wù)必定是修改表 t 的事務(wù)）

解決辦法：啟動復(fù)制時跳過 aaaa:101-10000 這些事務(wù)中修改表 t 的事務(wù)。

正確的修復(fù)步驟：

1. 在主庫上備份表 t （假設(shè)備份快照 GTID 為 aaaa:1-10000），恢復(fù)到從庫；

2. 設(shè)置復(fù)制過濾，過濾表 t：

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db_name.t');

3. 啟動復(fù)制，回放到 aaaa:10000 時停止復(fù)制（此時從庫上所有表的數(shù)據(jù)都在同一狀態(tài)，是一致的）;

START SLAVE UNTIL SQL_AFTER_GTIDS = 'aaaa:10000';

4. 刪除復(fù)制過濾，正常啟動復(fù)制。

注意事項：這里要用 mysqldump --single-transaction --master-data=2，記錄備份快照對應(yīng)的 GTID

場景 2

如果復(fù)制報錯后，使用跳過錯誤、復(fù)制過濾等辦法修復(fù)了主從復(fù)制。主、從庫數(shù)據(jù)一直在更新。

修復(fù)步驟：

在主庫上備份表 t （假設(shè)備份快照 GTID為 aaaa:1-10000）；

停止從庫復(fù)制，GTID為 aaaa:1-20000；

恢復(fù)表 t 到從庫；

啟動復(fù)制。

這里的問題是復(fù)制起始位點是 aaaa:20001，aaaa:10000-20000 這些事務(wù)將不會在從庫上回放，如果這里面有修改表 t 數(shù)據(jù)的事務(wù)，從庫上將丟失這部分數(shù)據(jù)。

解決辦法：從備份開始到啟動復(fù)制，鎖定表 t，保證 aaaa:10000-20000 中沒有修改表 t 的事務(wù)。

正確修復(fù)步驟：

對表 t 加讀鎖；

在主庫上備份表 t；

停止從庫復(fù)制，恢復(fù)表 t；

啟動復(fù)制；

解鎖表 t。

如果是大表，這里可以用可傳輸表空間方式備份、恢復(fù)表，減少鎖表時間。