解決mysql使用GTID主從複制錯誤問題 解決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

show slave statusg;

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

同步複制錯誤

下午搭了一主三從的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;

file: mysql-bin.000004

position: 102

binlog_do_db:

binlog_ignore_db:

記錄出日志的名字和偏移量,這些是從伺服器複制的目的目标

在從伺服器上,使用master_pos_wait()函數得到複制坐标值

+-------------------------------------------+

| master_pos_wait('mysql-bin.000004','102') |

|                                      0                         |

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