mysql主從不同步問(wèn)題解決
環(huán)境介紹：
我這里一兩臺(tái)，主主復(fù)制的mysql：
192.10.0.243
192.10.0.244
通過(guò)keepalived映射出來(lái)了vip:192.10.0.118，目前vip在243上。
由于某種原因244服務(wù)器異常down機(jī)，服務(wù)器啟動(dòng)之后，很幸運(yùn)的是mysql服務(wù)器正常啟動(dòng)了，查看按著習(xí)慣馬上查看錯(cuò)誤日志，發(fā)現(xiàn)報(bào)錯(cuò)：有一個(gè)表需要repair.
2017-03-21 10:46:40 3178 [ERROR] /usr/sbin/mysqld: Table './info/v_publish_text' is marked as crashed and should be repaired
2017-03-21 10:46:40 3178 [Warning] Checking table: './info/v_publish_text'
馬上處理這個(gè)報(bào)錯(cuò)：
mysql> repair table info.v_publish_text;
這個(gè)表很大，嘗試repair了整整6個(gè)小時(shí)，依舊沒(méi)有成功，錯(cuò)誤日志沒(méi)有任何報(bào)錯(cuò)，并且正好本身主從這兩個(gè)表就差將盡4萬(wàn)的數(shù)據(jù)（業(yè)務(wù)性質(zhì)允許這樣的偏差），正好借這個(gè)機(jī)會(huì)從新初始化下這個(gè)表好了，
具體初始化這個(gè)表的流程：
整體流程：首先把業(yè)務(wù)都切到243服務(wù)器上。
1.在243上mysqldump這個(gè)表并把dump文件傳到244，
2.關(guān)閉243的主從復(fù)制。
3.在244上恢復(fù)出來(lái)數(shù)據(jù)。
4.在244上查看當(dāng)前的mater 信息。
5.在243上重新change此時(shí)244的master的信息。
6.在244開啟slave，（244一直處于stop slave 的狀態(tài)）。
下面展示具體操作流程以及相關(guān)注意事項(xiàng)
一：在主庫(kù)（243）上mysqldump表v_publish_text,然后把dump文件傳給244服務(wù)器。
[root@S243 web_backup]#mysqldump -u root -p******* info v_publish_text |gzip > /mysql2/web_backup/v_publish_text.sql
[root@S243 web_backup]#scp v_publish_text.sql root@192.10.0.244:/mysql
二：停掉主庫(kù)243的復(fù)制進(jìn)程
mysql> stop slave;
驗(yàn)證io和sql進(jìn)程都為no,一定要確保slave進(jìn)程已經(jīng)被關(guān)閉了，目的是要跳過(guò)接下來(lái)在244執(zhí)行的恢復(fù)過(guò)程產(chǎn)生的binlog.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.244
Master_User: info_syncer
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001511
Read_Master_Log_Pos: 625205966
Relay_Log_File: S243-relay-bin.000079
Relay_Log_Pos: 479217301
Relay_Master_Log_File: mysql-bin.001511
Slave_IO_Running: No
Slave_SQL_Running: No
三：在244上恢復(fù)出數(shù)據(jù)，過(guò)程是：先drop table ，然后create table ,最后insert數(shù)據(jù)。
[root@S244 mysql]# gunzip<v_publish_text.sql | mysql -uroot -p****** info
等完成之后，等一會(huì)兒查看244mysql的master信息, 為243從新change做準(zhǔn)備。
[root@S244 mysql]#mysql -uroot -p*****
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.001472 | 127771389 | | | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在243從新change指向244新的位置，
mysql> CHANGE MASTER TO
MASTER_HOST="192.168.0.244",
MASTER_USER="info_syncer",
MASTER_PASSWORD="z=w@yLFh=su.VE7Oiw;e1QF,1",
master_port=3306,
MASTER_LOG_FILE="mysql-bin.001472",
MASTER_LOG_POS=127771389 ;
注意：寫binlog的時(shí)機(jī)是：sql語(yǔ)句或transaction執(zhí)行完，但任何相關(guān)的locks還未釋放或事務(wù)還未最終commit前。這樣保證了binlog記錄的操作時(shí)序與數(shù)據(jù)庫(kù)實(shí)際的數(shù)據(jù)變更順序一致。也就是說(shuō)當(dāng)你在244上恢復(fù)完數(shù)據(jù)后，show master status;顯示的master的binlog不一定把剛才寫完的sql或事務(wù)都寫進(jìn)了binlog,所以最好是在244上恢復(fù)完數(shù)據(jù)后，等一會(huì)再執(zhí)行change，反正此時(shí)244沒(méi)有寫的業(yè)務(wù)，索性就多等一會(huì)，再在243change;
四：?jiǎn)?dòng)244的主從復(fù)制，這樣會(huì)從當(dāng)時(shí)stop slave的位置繼續(xù)接受主庫(kù)的binlog,同時(shí)也會(huì)繼續(xù)從當(dāng)時(shí)stop時(shí)relay log的位置開始應(yīng)用。這時(shí)候肯定會(huì)有問(wèn)題，因?yàn)獒槍?duì)v_publish_text表，里面好多數(shù)據(jù)已經(jīng)通過(guò)mysqldump恢復(fù)出來(lái)了，再次應(yīng)用日志肯定會(huì)報(bào)主鍵沖突，進(jìn)而導(dǎo)致主從復(fù)制失敗，這時(shí)候提前寫好跳過(guò)一個(gè)事務(wù)的腳本，準(zhǔn)備在報(bào)錯(cuò)的時(shí)候執(zhí)行就可以了，反正現(xiàn)在從庫(kù)244上沒(méi)有任何業(yè)務(wù)，
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.244
Master_User: info_syncer
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001511
Read_Master_Log_Pos: 625205966
Relay_Log_File: S243-relay-bin.000079
Relay_Log_Pos: 479217301
Relay_Master_Log_File: mysql-bin.001511
Slave_IO_Running: YES
Slave_SQL_Running: YES
跳過(guò)一個(gè)事務(wù)的腳本，以便于遇到問(wèn)題之后快速執(zhí)行跳過(guò)一個(gè)事務(wù)。一定注意當(dāng)遇到報(bào)錯(cuò)的時(shí)候，再跳過(guò)，否則過(guò)多的跳過(guò)會(huì)造成數(shù)據(jù)不同步,因?yàn)檎５氖聞?wù)是不能跳過(guò)的。
[root@S244 ~]# cat /root/skip_erro.sh
#!/bin/bash
/usr/bin/mysql -u root -p'c!*]nnnn$' <<EOF
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
flush privileges;
然后觀察一段時(shí)間，一直到244應(yīng)用日志跟上243的master信息。。。。
補(bǔ)充內(nèi)容：
mysqlbinlog主從寫的機(jī)制：
一：在主庫(kù)243操作數(shù)據(jù)庫(kù)，然后分別在243和244上格式化當(dāng)前的binlog,并且查看相關(guān)內(nèi)容，結(jié)果如下：
create table liuliuliu ( id int)
insert into liuliuliu values(111)
insert into liuwenhe.liuliuliu values(11)
[root@S243 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001473 > binlog
[root@S243 mybinlog]# cat binlog | grep liuliuliu
create table liuliuliu ( id int)
insert into liuliuliu values(111)
insert into liuwenhe.liuliuliu values(11)
[root@S244 mybinlog]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001513 > binlog
[root@S244 mybinlog]# cat binlog | grep liuliuliu
create table liuliuliu ( id int)
insert into liuliuliu values(111)
insert into liuwenhe.liuliuliu values(11)
結(jié)論：在主庫(kù)操作的數(shù)據(jù)庫(kù)，相關(guān)記錄必然記錄到主庫(kù)binlog，值得注意的是從庫(kù)也把相關(guān)的信息記錄進(jìn)它自己的binlog中. 然后我猜應(yīng)該是做了特別的標(biāo)記，使得244并不會(huì)把從243接收到的相關(guān)操作信息再次傳回給243，
二：binlog的三種格式以及binlog的組提交（摘自網(wǎng)絡(luò)）：
2.1：Mysql binlog日志有三種格式，分別為Statement,MiXED,以及ROW！
2.1.1.Statement：每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中。
優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。(相比row能節(jié)約多少性能與日志量，這個(gè)取決于應(yīng)用的SQL情況，正常同一條記錄修改或者插入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會(huì)產(chǎn)生大量日志，因此在考慮是否使用ROW格式日志時(shí)應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況，其所產(chǎn)生的日志量會(huì)增加多少，以及帶來(lái)的IO性能問(wèn)題。)
缺點(diǎn)：由于記錄的只是執(zhí)行語(yǔ)句，為了這些語(yǔ)句能在slave上正確運(yùn)行，因此還必須記錄每條語(yǔ)句在執(zhí)行的時(shí)候的一些相關(guān)信息，以保證所有語(yǔ)句能在slave得到和在master端執(zhí)行時(shí)候相同 的結(jié)果。另外mysql 的復(fù)制,像一些特定函數(shù)功能，slave可與master上要保持一致會(huì)有很多相關(guān)問(wèn)題(如sleep()函數(shù)， last_insert_id()，以及user-defined functions(udf)會(huì)出現(xiàn)問(wèn)題).
使用以下函數(shù)的語(yǔ)句也無(wú)法被復(fù)制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動(dòng)時(shí)啟用了 --sysdate-is-now 選項(xiàng))
同時(shí)在INSERT ...SELECT 會(huì)產(chǎn)生比 RBR 更多的行級(jí)鎖
2.1.2：Row:不記錄sql語(yǔ)句上下文相關(guān)信息，僅保存哪條記錄被修改。
優(yōu)點(diǎn)： binlog中可以不記錄執(zhí)行的sql語(yǔ)句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。而且不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程，或function，以及trigger的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題
缺點(diǎn):所有的執(zhí)行的語(yǔ)句當(dāng)記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì)產(chǎn)生大量的日志內(nèi)容,比如一條update語(yǔ)句，修改多條記錄，則binlog中每一條修改都會(huì)有記錄，這樣造成binlog日志量會(huì)很大，特別是當(dāng)執(zhí)行alter table之類的語(yǔ)句的時(shí)候，由于表結(jié)構(gòu)修改，每條記錄都發(fā)生改變，那么該表每一條記錄都會(huì)記錄到日志中。
2.1.3：Mixedlevel: 是以上兩種level的混合使用，一般的語(yǔ)句修改使用statment格式保存binlog，如一些函數(shù)，statement無(wú)法完成主從復(fù)制的操作，則采用row格式保存binlog,MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊(duì)row level模式也被做了優(yōu)化，并不是所有的修改都會(huì)以row level來(lái)記錄，像遇到表結(jié)構(gòu)變更的時(shí)候就會(huì)以statement模式來(lái)記錄。至于update或者delete等修改數(shù)據(jù)的語(yǔ)句，還是會(huì)記錄所有行的變更。
2.1.4：Binlog基本配制與格式設(shè)定
1.基本配制
Mysql BInlog日志格式可以通過(guò)mysql的my.cnf文件的屬性binlog_format指定。如以下：
binlog_format = MIXED //binlog日志格式
log_bin =目錄/mysql-bin.log //binlog日志名
expire_logs_days = 7 //binlog過(guò)期清理時(shí)間
max_binlog_size 100m //binlog每個(gè)日志文件大小
2.1.5：Binlog日志格式選擇
Mysql默認(rèn)是使用Statement日志格式，推薦使用MIXED.
由于一些特殊使用，可以考慮使用ROWED，如自己通過(guò)binlog日志來(lái)同步數(shù)據(jù)的修改，這樣會(huì)節(jié)省很多相關(guān)操作。對(duì)于binlog數(shù)據(jù)處理會(huì)變得非常輕松,相對(duì)mixed，解析也會(huì)很輕松(當(dāng)然前提是增加的日志量所帶來(lái)的IO開銷在容忍的范圍內(nèi)即可)。
2.1.6：針對(duì)binlog的三種格式而產(chǎn)生相應(yīng)的主從復(fù)制的三種方式：
（１）：基于語(yǔ)句（Statement）的復(fù)制： 在主服務(wù)器上執(zhí)行的SQL語(yǔ)句，在從服務(wù)器上執(zhí)行同樣的語(yǔ)句。MySQL默認(rèn)采用基于語(yǔ)句的復(fù)制，效率比較高。
（２）：基于行（row)的復(fù)制：把改變的內(nèi)容復(fù)制過(guò)去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0開始支持
（３）：混合類型（mixed)的復(fù)制: 默認(rèn)采用基于語(yǔ)句的復(fù)制，一旦發(fā)現(xiàn)基于語(yǔ)句的無(wú)法精確的復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制。
2.2： binlog組提交（5.6），5.6默認(rèn)就是組提交，不需要開啟，這是它的內(nèi)部機(jī)制
它的基本思想是：引入隊(duì)列機(jī)制保證innodb commit順序與binlog落盤順序一致，并將事務(wù)分組，組內(nèi)的binlog刷盤動(dòng)作交給一個(gè)事務(wù)進(jìn)行，實(shí)現(xiàn)組提交目的。binlog提交將提交分為了3個(gè)階段，F(xiàn)LUSH階段，SYNC階段和COMMIT階段。每個(gè)階段都有一個(gè)隊(duì)列，每個(gè)隊(duì)列有一個(gè)mutex保護(hù)，約定進(jìn)入隊(duì)列第一個(gè)線程為leader，其他線程為follower，所有事情交由leader去做，leader做完所有動(dòng)作后，通知follower刷盤結(jié)束。在 mysql 5.5 中，只有當(dāng) sync_binlog = 0 時(shí)，才能使用 group commit，在 mysql 5.6中都可以進(jìn)行 group commit log組提交基本流程如下：
FLUSH 階段
1) 持有Lock_log mutex [leader持有，follower等待]
2) 獲取隊(duì)列中的一組binlog(隊(duì)列中的所有事務(wù))
3) 將binlog buffer到I/O cache
4) 通知dump線程dump binlog
SYNC階段
這個(gè)階段和參數(shù)sync_binlog有關(guān)系，
1) 釋放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]
2) 將一組binlog 落盤(sync動(dòng)作，最耗時(shí)，假設(shè)sync_binlog為1)。
COMMIT階段
1) 釋放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]
2) 遍歷隊(duì)列中的事務(wù)，逐一進(jìn)行innodb commit
3) 釋放Lock_commit mutex
4) 喚醒隊(duì)列中等待的線程
說(shuō)明：由于有多個(gè)隊(duì)列，每個(gè)隊(duì)列各自有mutex保護(hù)，隊(duì)列之間是順序的，約定進(jìn)入隊(duì)列的一個(gè)線程為leader，因此FLUSH階段的leader可能是SYNC階段的follower，但是follower永遠(yuǎn)是follower。
通過(guò)上文分析，我們知道MYSQL目前的組提交方式解決了一致性和性能的問(wèn)題。通過(guò)二階段提交解決一致性，通過(guò)redo log和binlog的組提交解決磁盤IO的性能。
2.3：關(guān)于參數(shù)sync_binlog的理解：
sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時(shí)候來(lái)做同步，或者cache滿了之后才同步到磁盤。
sync_binlog=n，當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來(lái)將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤，當(dāng)數(shù)據(jù)庫(kù)crash的時(shí)候至少會(huì)丟失N-1個(gè)transactions
sync_binlog=1，每一個(gè)transaction commit都會(huì)調(diào)用一次fsync()，此時(shí)能保證數(shù)據(jù)最安全但是性能影響較大。
總結(jié)：mysql主從復(fù)制，正常情況下slave讀取master的binlog_buffer中的binlog,并不是等寫到binlog底層文件后才讀取的，只有當(dāng)slave出現(xiàn)故障后，但是此時(shí)maser庫(kù)依舊在跑業(yè)務(wù)，當(dāng)從新開始start slave；這時(shí)候讀取的binlog就會(huì)從磁層磁盤binlog文件讀取。
延伸內(nèi)容：
異 步復(fù)制：咱們現(xiàn)在大多數(shù)都是異步復(fù)制的，MySQL本身支持單向的、異步的復(fù)制。異步復(fù)制意味著在把數(shù)據(jù)從一臺(tái)機(jī)器拷貝到另一臺(tái)機(jī)器時(shí)有一個(gè)延時(shí) – 最重要的是這意味著當(dāng)應(yīng)用系統(tǒng)的事務(wù)提交已經(jīng)確認(rèn)時(shí)數(shù)據(jù)并不能在同一時(shí)刻拷貝/應(yīng)用到從機(jī)。通常這個(gè)延時(shí)是由網(wǎng)絡(luò)帶寬、資源可用性和系統(tǒng)負(fù)載決定的。然 而，使用正確的組件并且調(diào)優(yōu)，復(fù)制能做到接近瞬時(shí)完成。
同步復(fù)制：使用MyISAM或者InnoDB存儲(chǔ)引擎的MySQL本身并不支持同步復(fù)制，同步復(fù)制可以定義為數(shù)據(jù)在同一時(shí)刻被提交到一臺(tái)或多臺(tái)機(jī)器，通常這是通過(guò)眾所周知的“兩階段提交”做到的，也就是說(shuō)保證數(shù)據(jù)至少在一臺(tái)slave上正常commit。雖然這確實(shí)給你在多系統(tǒng)中保持一致性，但也由于增加了額外的消息交換而造成性能下降。
半同步復(fù)制：是基于Google為MySQL開發(fā)的半同步復(fù)制的插件。半同步復(fù)制的原理是，一個(gè)事務(wù)在主服務(wù)器上執(zhí)行完成后，必須至少確保至少在一臺(tái)從服務(wù)器上執(zhí)行完成后，事務(wù)才算提交成功。如果在一定時(shí)間內(nèi)從服務(wù)器沒(méi)有響應(yīng)，則會(huì)自動(dòng)降級(jí)為異步復(fù)制。
這個(gè)半同步復(fù)制是建立在異步復(fù)制的基礎(chǔ)之上進(jìn)行的。
