做MySQL主從的話肯定會(huì)遇到很多同步上的問題,大多數(shù)都是由于機(jī)器宕機(jī),重啟,或者是主鍵沖突等引起的從服務(wù)器停止工作,這里專門收集類似問題并提供整理解決方案,僅供參考.

1、主從網(wǎng)絡(luò)中斷,或主服務(wù)器重啟,或從服務(wù)器重啟,從會(huì)根據(jù)配置文件中的時(shí)間,默認(rèn)1分鐘,去自動(dòng)重連主服務(wù)器,直到網(wǎng)絡(luò)和服務(wù)均可正常連接,連接正常后可自動(dòng)繼續(xù)同步之前文件,不需要任何人工干預(yù).

2、當(dāng)主從因?yàn)槿藶樵虺霈F(xiàn)不同步的時(shí)候,可以用下面命令進(jìn)行同步:

LOAD DATA FROM MASTER;

LOAD TABLE TBLNAME FROM MASTER;

注意,上面命令會(huì)對(duì)主數(shù)據(jù)庫進(jìn)行鎖操作,如果數(shù)據(jù)庫極大,建議在停機(jī)的時(shí)候進(jìn)行,或者用短鎖備份查看 show master status; 后,拷貝數(shù)據(jù)庫的方式進(jìn)行.

3、當(dāng) BIN-LOG 里面出現(xiàn) SQL 級(jí)別錯(cuò)誤導(dǎo)致主從不能同步的時(shí)候,可以用下面方法掠過該錯(cuò)誤語句行,繼續(xù)同步:

stop slave;

set global sql_slave_skip_counter=1;

start slave;

4、.當(dāng) set global sql_slave_skip_counter=1;是可能會(huì)出現(xiàn)一下錯(cuò)誤

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 模式運(yùn)行的數(shù)據(jù)庫,那怎么辦呢?

下面就講一下GTID模式的主從錯(cuò)誤跳過方法,多余的話不說了,直接上方法,按順序執(zhí)行即可,首先確定GTID點(diǎn),也就是同步出錯(cuò)的點(diǎn)記錄下來,方法如下,在查看之前您必須先登錄MySQL,代碼如下:

mysql> show slave statusG;

查看一下信息并記錄下來:

Executed_Gtid_Set: 7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-260

接下來重置 slave上的 master和slave的.

NOTE:注意這里說的是從服務(wù)器上的master 和 slave,如果是主主復(fù)制就會(huì)很麻煩,這里注意了,reset master會(huì)導(dǎo)致此slave上所有的slave重置,reset master的主要目的是使gtid_executed為空。這里不能簡單的使用change master to來切換,這樣做表面上不會(huì)報(bào)錯(cuò),但是實(shí)際上slave并不會(huì)更新,服務(wù)器會(huì)參考show slave statusG中的Executed_Gtid_Set參數(shù)來獲取數(shù)據(jù),代碼如下:

1. mysql> reset master; 
2. Query OK, 0 rows affected (0.20 sec) 
3. mysql> stop slave; 
4. Query OK, 0 rows affected (0.05 sec) 
5. mysql> reset slave; 
6. Query OK, 0 rows affected (0.42 sec) 

下面我們需要重新設(shè)置GTID以跳過錯(cuò)誤的信息,記得在第一步我們記錄下來的Executed_Gtid_set嗎? 沒錯(cuò)執(zhí)行它的時(shí)候粗錯(cuò)了,那么保守起見直接跳過這一條即可,在其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;

怎么樣? 問題解決了吧? 什么? 還報(bào)錯(cuò)? 那你仔細(xì)看一下報(bào)錯(cuò)的是不是和上一條不一樣了呢? 就證明已經(jīng)跳過上條錯(cuò)誤了, 您需要做的就是繼續(xù)重復(fù)上面操作, 直到跳過所有錯(cuò)我,別嫌麻煩,畢竟數(shù)據(jù)很重要哦!

同步復(fù)制錯(cuò)誤:下午搭了一主三從的mysql復(fù)制,結(jié)果所有服務(wù)器都配置好后,發(fā)現(xiàn)從上報(bào)如下的錯(cuò)誤.

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和主的一樣的，經(jīng)查看發(fā)現(xiàn)從上的/etc/my.cnf中的server_id=1這行我沒有注釋掉（在下面復(fù)制部分我設(shè)置了server_id），于是馬上把這行注釋掉了，然后重啟mysql，發(fā)現(xiàn)還是報(bào)同樣的錯(cuò)誤。

使用如下命令查看了一下server_id,代碼如下:

1. mysql> show variables like 'server_id'; 
2. +---------------+-------+ 
3. | Variable_name | Value | 
4. +---------------+-------+ 
5. | server_id | 1 | 
6. +---------------+-------+ 
7. 1 row in set (0.00 sec) 

發(fā)現(xiàn),mysql并沒有從my.cnf文件中更新server_id,既然這樣就只能手動(dòng)修改了,代碼如下:

mysql> set global server_id=2; #此處的數(shù)值和my.cnf里設(shè)置的一樣就行

mysql> slave start;

如此執(zhí)行后,slave恢復(fù)了正常,不過稍后蚊子使用/etc/init.d/mysqld restart重啟了mysql服務(wù),然后查看slave狀態(tài),發(fā)現(xiàn)又出現(xiàn)了上面的錯(cuò)誤,然后查看server_id發(fā)現(xiàn)這個(gè)數(shù)值又恢復(fù)到了1.

之后蚊子又重新查看了一下/etc/my.cnf的內(nèi)容,確認(rèn)應(yīng)該不是這個(gè)文件的問題,于是去google查了一下,看到mysql在啟動(dòng)的時(shí)候會(huì)查找/etc/my.cnf、DATADIR/my.cnf，USER_HOME/my.cnf.

于是我執(zhí)行了如下代碼:find / -name "my.cnf",居然在/usr/local/mysql這個(gè)目錄下發(fā)現(xiàn)了my.cnf文件,于是蚊子將這個(gè)文件刪除了,然后再重啟mysql服務(wù),發(fā)現(xiàn)一切恢復(fù)了正常.

一些錯(cuò)誤處理和日常維護(hù),檢查從服務(wù)器一般使用show slave status命令來檢查,代碼如下:

1. mysql> SHOW SLAVE STATUSG 
2. *************************** 1. row *************************** 
3. Slave_IO_State: Waiting for master to send event 
4. Master_Host: 192.168.0.100 
5. Master_User: root 
6. Master_Port: 3306 
7. Connect_Retry: 3 
8.  Master_Log_File: mysql-bin.003 
9.  Read_Master_Log_Pos: 79 
10. Relay_Log_File: mysql -relay-bin. 003 
11. Relay_Log_Pos: 548 
12. Relay_Master_Log_File: mysql -bin. 003 
13. Slave_IO_Running: Yes 
14. Slave_SQL_Running: Yes 
15. Replicate_Do_DB: 
16. Replicate_Ignore_DB: 
17. Last_Errno: 0 
18. ….. 

在上面這些信息中我們主要關(guān)注的是Slave_IO_Running和Slave_SQL_Running

Slave_IO_Running:從服務(wù)器正從主服務(wù)器上讀取BINLOG日志,并寫入從服務(wù)器的中繼日志.

Slave_SQL_Running:進(jìn)程正在讀取從服務(wù)器的BINLOG中繼日志，并轉(zhuǎn)化為SQL執(zhí)行

以前有一個(gè)進(jìn)程是no狀態(tài)，表示復(fù)制的進(jìn)程停止，在Last_Errno會(huì)看到是什么情況,有時(shí)候因?yàn)橹鞣?wù)器的更新過于頻繁,造成了從服務(wù)器更新速度較慢,當(dāng)然問題是多種多樣,有可能是網(wǎng)絡(luò)搭建的結(jié)構(gòu)不好或者硬件的性能較差,從而使得主從服務(wù)器之間的差距越來越大,最終對(duì)某些應(yīng)用產(chǎn)生了影響,在這種情況下,我們需要定期進(jìn)行主從服務(wù)器的數(shù)據(jù)同步,具體步驟如下.

在主服務(wù)器上,代碼如下:

1. mysql> FLUSH TABLES WITH READ LOCK; 
2. Query OK, 0 rows affected (0.03 sec) 
3. mysql> show master statusG; 
4. *************************** 1. row *************************** 
5. File: mysql-bin.000004 
6. Position: 102 
7. Binlog_Do_DB: 
8. Binlog_Ignore_DB: 
9. 1 row in set (0.00 sec) 

記錄出日志的名字和偏移量,這些是從服務(wù)器復(fù)制的目的目標(biāo),在從服務(wù)器上,使用MASTER_POS_WAIT()函數(shù)得到復(fù)制坐標(biāo)值,代碼如下:

1. mysql> select master_pos_wait('mysql-bin.000004','102'); 
2. +-------------------------------------------+ 
3. | master_pos_wait('mysql-bin.000004','102') | 
4. +-------------------------------------------+ 
5. |                                      0                         | 
6. +-------------------------------------------+ 
7. 1 row in set (0.00 sec) //開源軟件:Vevb.com 

這個(gè)select 語句會(huì)阻塞直到從服務(wù)器達(dá)到指定日志文件和偏移量后,返回0,如果是-1,則表示超時(shí)推出,查詢是0時(shí),表示從服務(wù)器與主服務(wù)器已經(jīng)同步.

在某些情況下,會(huì)出現(xiàn)從服務(wù)器更新失敗,首先需要確定是否從服務(wù)器的表與主服務(wù)器的不同造成的,如果是表結(jié)構(gòu)造成的,則需要修改從服務(wù)器的表和主服務(wù)器一致,然后重新運(yùn)行start slave

如果不是表結(jié)構(gòu)不同造成的更新失敗,則需要確認(rèn)手動(dòng)更新是否安全,然后忽視來自主服務(wù)器的更新失敗語句,跳過來來自主服務(wù)器的語句,命令為SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n,其中,n=1表示來自主服務(wù)器的更新語句不使用AUTO_INCREMENT或LAST_INSERT_ID(),n=2時(shí)則反之,原因是使用AUTO_INCREMENT或LAST_INSERT_ID的語句需要從二進(jìn)制日志中取得兩個(gè)事件.