解決mysql使用GTID主從複製錯誤問題

做MySQL主從的話肯定會遇到很多同步上的問題, 大多數都是由於機器當機,重啟,或者是主鍵衝突等引起的從伺服器停止工作, 這裡專門收集類似問題並提供整理解決方案,僅供參考!

1、主從網路中斷，或主伺服器重啟，或從伺服器重啟，從會根據配置檔案中的時間（預設1分鐘）去自動重連主伺服器，直到網路和服務均可正常連線，連線正常後可自動繼續同步之前檔案，不需要任何人工干預！

2、當主從因為人為原因出現不同步的時候，可以用下面命令進行同步：

程式碼如下	複製程式碼
LOAD DATA FROM MASTER; LOAD TABLE TBLNAME FROM MASTER;

注意，上面命令會對主資料庫進行鎖操作，如果資料庫極大，建議在停機的時候進行，或者用短鎖備份檢視
show master status; 後，拷貝資料庫的方式進行。

3、當 BIN-LOG 裡面出現 SQL 級別錯誤導致主從不能同步的時候，可以用下面方法掠過該錯誤語句行，繼續同步：

程式碼如下	複製程式碼
stop slave; set global sql_slave_skip_counter=1; start slave;

4、.當 set global sql_slave_skip_counter=1;是可能會出現一下錯誤
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

原因也說的很清楚了 不支援GTID_MODE 模式執行的資料庫
那怎麼辦呢?
下面就講一下GTID模式的主從錯誤跳過方法

多餘的話不說了 直接上方法, 按順序執行即可
首先確定GTID點,也就是同步出錯的點記錄下來,方法如下, 在檢視之前您必須先登入MySQL

程式碼如下	複製程式碼
mysql> show slave statusG;

檢視一下資訊並記錄下來
Executed_Gtid_Set: 7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-260

接下來重置 slave上的 master和slave的
NOTE:
(注意這裡說的是從伺服器上的master 和 slave,如果是主主複製就會很麻煩)
(這裡注意了,reset master會導致此slave上所有的slave重置,reset master的主要目的是使gtid_executed為空。這裡不能簡單的使用change master to來切換,這樣做表面上不會報錯，但是實際上slave並不會更新,伺服器會參考show slave statusG中的Executed_Gtid_Set引數來獲取資料.)

程式碼如下	複製程式碼
mysql> reset master; Query OK, 0 rows affected (0.20 sec) mysql> stop slave; Query OK, 0 rows affected (0.05 sec) mysql> reset slave; Query OK, 0 rows affected (0.42 sec)

下面我們需要重新設定GTID以跳過錯誤的資訊 記得在第一步我們記錄下來的Executed_Gtid_set嗎? 沒錯執行它的時候粗錯了, 那麼保守起見直接跳過這一條即可, 在其ID上加1即可

程式碼如下	複製程式碼
mysql> set global gtid_purged=’7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-261′; Query OK, 0 rows affected (0.18 sec)

由於我們剛才重置了Master和Slave,所以這裡需要重新CHANGE MASTER:

程式碼如下	複製程式碼
CHANGE MASTER TO MASTER_HOST=’192.168.1.136′, MASTER_PORT=3306, MASTER_USER=’dbadmin’,MASTER_PASSWORD=’123456′, master_auto_position=1;

然後重啟slave

程式碼如下	複製程式碼
start slave; show slave statusG;

怎麼樣? 問題解決了吧? 什麼? 還報錯? 那你仔細看一下報錯的是不是和上一條不一樣了呢? 就證明已經跳過上條錯誤了, 您需要做的就是繼續重複上面操作, 直到跳過所有錯我,別嫌麻煩,畢竟資料很重要哦!

同步複製錯誤

下午搭了一主三從的mysql複製，結果所有伺服器都配置好後，發現從上報如下的錯誤

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL
server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

意思就是從上的server_id和主的一樣的，經檢視發現從上的/etc/my.cnf中的server_id=1這行我沒有註釋掉（在下面複製部分我設定了server_id），於是馬上把這行註釋掉了，然後重啟mysql，發現還是報同樣的錯誤。

使用如下命令檢視了一下server_id

 

程式碼如下	複製程式碼
mysql> show variables like `server_id`; +—————+——-+ | Variable_name | Value | +—————+——-+ | server_id | 1 | +—————+——-+ 1 row in set (0.00 sec)

發現，mysql並沒有從my.cnf檔案中更新server_id，既然這樣就只能手動修改了

 

程式碼如下	複製程式碼
mysql> set global server_id=2; #此處的數值和my.cnf裡設定的一樣就行 mysql> slave start;

如此執行後，slave恢復了正常。

不過稍後蚊子使用/etc/init.d/mysqld restart重啟了mysql服務，然後檢視slave狀態，發現又出現了上面的錯誤，然後檢視server_id發現這個數值又恢復到了1。

之後蚊子又重新檢視了一下/etc/my.cnf的內容，確認應該不是這個檔案的問題，於是去google查了一下，看到mysql在啟動的時候會查詢/etc/my.cnf、DATADIR/my.cnf，USER_HOME/my.cnf。

於是我執行了

程式碼如下	複製程式碼
find / -name “my.cnf”

居然在/usr/local/mysql這個目錄下發現了my.cnf檔案，於是蚊子將這個檔案刪除了，然後再重啟mysql服務，發現一切恢復了正常

一些錯誤處理和日常維護

檢查從伺服器一般使用show slave status命令來檢查

程式碼如下

複製程式碼

mysql> SHOW SLAVE STATUSG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.100 Master_User: root Master_Port: 3306 Connect_Retry: 3  Master_Log_File: mysql-bin.003  Read_Master_Log_Pos: 79 Relay_Log_File: mysql -relay-bin. 003 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql -bin. 003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Last_Errno: 0 …..

在上面這些資訊中我們主要關注的是Slave_IO_Running和Slave_SQL_Running
Slave_IO_Running:從伺服器正從主伺服器上讀取BINLOG日誌,並寫入從伺服器的中繼日誌
Slave_SQL_Running:程式正在讀取從伺服器的BINLOG中繼日誌，並轉化為SQL執行
以前有一個程式是no狀態，表示複製的程式停止，在Last_Errno會看到是什麼情況

有時候因為主伺服器的更新過於頻繁,造成了從伺服器更新速度較慢,當然問題是多種多樣,有可能是網路搭建的結構不好或者硬體的效能較差,從而使得主從伺服器之間的差距越來越大,最終對某些應用產生了影響,在這種情況下,我們需要定期進行主從伺服器的資料同步,具體步驟如下
在主伺服器上

程式碼如下	複製程式碼
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.03 sec) mysql> show master statusG; *************************** 1. row *************************** File: mysql-bin.000004 Position: 102 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

記錄出日誌的名字和偏移量,這些是從伺服器複製的目的目標

在從伺服器上,使用MASTER_POS_WAIT()函式得到複製座標值

程式碼如下	複製程式碼
mysql> select master_pos_wait(`mysql-bin.000004`,`102`); +——————————————-+ | master_pos_wait(`mysql-bin.000004`,`102`) | +——————————————-+ |                                      0                         | +——————————————-+ 1 row in set (0.00 sec)

這個select 語句會阻塞直到從伺服器達到指定日誌檔案和偏移量後,返回0,如果是-1,則表示超時推出,查詢是0時,表示從伺服器與主伺服器已經同步

在某些情況下,會出現從伺服器更新失敗,首先需要確定是否從伺服器的表與主伺服器的不同造成的,如果是表結構造成的,則需要修改從伺服器的表和主伺服器一致,然後重新執行start slave
如果不是表結構不同造成的更新失敗，則需要確認手動更新是否安全，然後忽視來自主伺服器的更新失敗語句，跳過來來自主伺服器的語句，命令為SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n,其中,n=1表示來自主伺服器的更新語句不使用AUTO_INCREMENT或LAST_INSERT_ID(),n=2時則反之,原因是使用AUTO_INCREMENT或LAST_INSERT_ID的語句需要從二進位制日誌中取得兩個事件.