解決mysql使用GTID主從復制錯誤問題

下面本文章給各位同學整理了一些在mysql主從配置時碰到的一些問題解決方法，希望這文章對各位朋友做主從配置時帶一一些幫助哦。

做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的語句需要從二進制日志中取得兩個事件.