MySQL 5.7復制的一個小bug-XA事務

線上一個5.7從庫復制中斷：

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

查詢具體報錯：

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

看下本地的relay log，找到這個事務的gtid

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

出錯了。

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

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

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

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

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

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

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

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

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

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

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

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

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

5.5或者5.6假設下面一個場景：

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

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

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

MySQL出現錯誤怎么辦？

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

2、然后檢查MySQL服務是否已經在系統服務列表中，如果不在則需要手動添加MySQL服務。

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

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

拓展：

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

mysql復制表文件錯誤

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

處理方式：

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

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

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

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

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

--觸發器定義CREATE DEFINER=`root`@`%` trigger t_test

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

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

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

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

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

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

mysql2016數據庫表復制粘貼報錯,如何終止導入？

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

場景 1

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

修復步驟：

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

恢復到從庫；

啟動復制。

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

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

正確的修復步驟：

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

2. 設置復制過濾，過濾表 t：

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

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

START SLAVE UNTIL SQL_AFTER_GTIDS = 'aaaa:10000';

4. 刪除復制過濾，正常啟動復制。

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

場景 2

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

修復步驟：

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

停止從庫復制，GTID為 aaaa:1-20000；

恢復表 t 到從庫；

啟動復制。

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

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

正確修復步驟：

對表 t 加讀鎖；

在主庫上備份表 t；

停止從庫復制，恢復表 t；

啟動復制；

解鎖表 t。

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