mysql主從不同步問題解決

賀子_DBA時代發表於2017-03-28
MySql
環境介紹：
我這裡一兩臺，主主複製的mysql：
192.10.0.243
192.10.0.244
透過keepalived對映出來了vip:192.10.0.118，目前vip在243上。
由於某種原因244伺服器異常down機，伺服器啟動之後，很幸運的是mysql伺服器正常啟動了，檢視按著習慣馬上檢視錯誤日誌，發現報錯：有一個表需要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'
馬上處理這個報錯：
mysql> repair table info.v_publish_text;
這個表很大，嘗試repair了整整6個小時，依舊沒有成功，錯誤日誌沒有任何報錯，並且正好本身主從這兩個表就差將盡4萬的資料（業務性質允許這樣的偏差），正好借這個機會從新初始化下這個表好了，
具體初始化這個表的流程：
整體流程：首先把業務都切到243伺服器上。
1.在243上mysqldump這個表並把dump檔案傳到244，
2.關閉243的主從複製。
3.在244上恢復出來資料。
4.在244上檢視當前的mater 資訊。
5.在243上重新change此時244的master的資訊。
6.在244開啟slave，（244一直處於stop slave 的狀態）。
下面展示具體操作流程以及相關注意事項
一：在主庫（243）上mysqldump表v_publish_text,然後把dump檔案傳給244伺服器。
[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
二：停掉主庫243的複製程式
mysql> stop slave;
驗證io和sql程式都為no,一定要確保slave程式已經被關閉了，目的是要跳過接下來在244執行的恢復過程產生的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上恢復出資料，過程是：先drop table ，然後create table ,最後insert資料。
[root@S244 mysql]# gunzip<v_publish_text.sql | mysql -uroot -p****** info
等完成之後，等一會兒檢視244mysql的master資訊, 為243從新change做準備。
[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的時機是：sql語句或transaction執行完，但任何相關的locks還未釋放或事務還未最終commit前。這樣保證了binlog記錄的操作時序與資料庫實際的資料變更順序一致。也就是說當你在244上恢復完資料後，show master status;顯示的master的binlog不一定把剛才寫完的sql或事務都寫進了binlog,所以最好是在244上恢復完資料後，等一會再執行change，反正此時244沒有寫的業務，索性就多等一會，再在243change;
四：啟動244的主從複製，這樣會從當時stop slave的位置繼續接受主庫的binlog,同時也會繼續從當時stop時relay log的位置開始應用。這時候肯定會有問題，因為針對v_publish_text表，裡面好多資料已經透過mysqldump恢復出來了，再次應用日誌肯定會報主鍵衝突，進而導致主從複製失敗，這時候提前寫好跳過一個事務的指令碼，準備在報錯的時候執行就可以了，反正現在從庫244上沒有任何業務，
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
跳過一個事務的指令碼，以便於遇到問題之後快速執行跳過一個事務。一定注意當遇到報錯的時候，再跳過，否則過多的跳過會造成資料不同步,因為正常的事務是不能跳過的。
[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;
然後觀察一段時間，一直到244應用日誌跟上243的master資訊。。。。
補充內容：
mysqlbinlog主從寫的機制：
一：在主庫243運算元據庫，然後分別在243和244上格式化當前的binlog,並且檢視相關內容，結果如下：
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)
結論：在主庫操作的資料庫，相關記錄必然記錄到主庫binlog，值得注意的是從庫也把相關的資訊記錄進它自己的binlog中. 然後我猜應該是做了特別的標記，使得244並不會把從243接收到的相關操作資訊再次傳回給243，
二：binlog的三種格式以及binlog的組提交（摘自網路）：
2.1：Mysql binlog日誌有三種格式，分別為Statement,MiXED,以及ROW！
2.1.1.Statement：每一條會修改資料的sql都會記錄在binlog中。
優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高效能。(相比row能節約多少效能與日誌量，這個取決於應用的SQL情況，正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產生大量日誌，因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況，其所產生的日誌量會增加多少，以及帶來的IO效能問題。)
缺點：由於記錄的只是執行語句，為了這些語句能在slave上正確執行，因此還必須記錄每條語句在執行的時候的一些相關資訊，以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的複製,像一些特定函式功能，slave可與master上要保持一致會有很多相關問題(如sleep()函式， last_insert_id()，以及user-defined functions(udf)會出現問題).
使用以下函式的語句也無法被複制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
同時在INSERT ...SELECT 會產生比 RBR 更多的行級鎖
2.1.2：Row:不記錄sql語句上下文相關資訊，僅儲存哪條記錄被修改。
優點： binlog中可以不記錄執行的sql語句的上下文相關的資訊，僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程，或function，以及trigger的呼叫和觸發無法被正確複製的問題
缺點:所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。
2.1.3：Mixedlevel: 是以上兩種level的混合使用，一般的語句修改使用statment格式儲存binlog，如一些函式，statement無法完成主從複製的操作，則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了最佳化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句，還是會記錄所有行的變更。
2.1.4：Binlog基本配製與格式設定
1.基本配製
Mysql BInlog日誌格式可以透過mysql的my.cnf檔案的屬性binlog_format指定。如以下：
binlog_format = MIXED //binlog日誌格式
log_bin =目錄/mysql-bin.log //binlog日誌名
expire_logs_days = 7 //binlog過期清理時間
max_binlog_size 100m //binlog每個日誌檔案大小
2.1.5：Binlog日誌格式選擇
Mysql預設是使用Statement日誌格式，推薦使用MIXED.
由於一些特殊使用，可以考慮使用ROWED，如自己透過binlog日誌來同步資料的修改，這樣會節省很多相關操作。對於binlog資料處理會變得非常輕鬆,相對mixed，解析也會很輕鬆(當然前提是增加的日誌量所帶來的IO開銷在容忍的範圍內即可)。
2.1.6：針對binlog的三種格式而產生相應的主從複製的三種方式：
（１）：基於語句（Statement）的複製： 在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。
（２）：基於行（row)的複製：把改變的內容複製過去，而不是把命令在從伺服器上執行一遍. 從mysql5.0開始支援
（３）：混合型別（mixed)的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。
2.2： binlog組提交（5.6），5.6預設就是組提交，不需要開啟，這是它的內部機制
它的基本思想是：引入佇列機制保證innodb commit順序與binlog落盤順序一致，並將事務分組，組內的binlog刷盤動作交給一個事務進行，實現組提交目的。binlog提交將提交分為了3個階段，FLUSH階段，SYNC階段和COMMIT階段。每個階段都有一個佇列，每個佇列有一個mutex保護，約定進入佇列第一個執行緒為leader，其他執行緒為follower，所有事情交由leader去做，leader做完所有動作後，通知follower刷盤結束。在 mysql 5.5 中，只有當 sync_binlog = 0 時，才能使用 group commit，在 mysql 5.6中都可以進行 group commit log組提交基本流程如下：
FLUSH 階段
1) 持有Lock_log mutex [leader持有，follower等待]
2) 獲取佇列中的一組binlog(佇列中的所有事務)
3) 將binlog buffer到I/O cache
4) 通知dump執行緒dump binlog
SYNC階段
這個階段和引數sync_binlog有關係，
1) 釋放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]
2) 將一組binlog 落盤(sync動作，最耗時，假設sync_binlog為1)。
COMMIT階段
1) 釋放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]
2) 遍歷佇列中的事務，逐一進行innodb commit
3) 釋放Lock_commit mutex
4) 喚醒佇列中等待的執行緒
說明：由於有多個佇列，每個佇列各自有mutex保護，佇列之間是順序的，約定進入佇列的一個執行緒為leader，因此FLUSH階段的leader可能是SYNC階段的follower，但是follower永遠是follower。
透過上文分析，我們知道MYSQL目前的組提交方式解決了一致性和效能的問題。透過二階段提交解決一致性，透過redo log和binlog的組提交解決磁碟IO的效能。
2.3：關於引數sync_binlog的理解：
sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。
sync_binlog=n，當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟，當資料庫crash的時候至少會丟失N-1個transactions
sync_binlog=1，每一個transaction commit都會呼叫一次fsync()，此時能保證資料最安全但是效能影響較大。
總結：mysql主從複製，正常情況下slave讀取master的binlog_buffer中的binlog,並不是等寫到binlog底層檔案後才讀取的，只有當slave出現故障後，但是此時maser庫依舊在跑業務，當從新開始start slave；這時候讀取的binlog就會從磁層磁碟binlog檔案讀取。
延伸內容：
異 步複製：我們們現在大多數都是非同步複製的，MySQL本身支援單向的、非同步的複製。非同步複製意味著在把資料從一臺機器複製到另一臺機器時有一個延時 – 最重要的是這意味著當應用系統的事務提交已經確認時資料並不能在同一時刻複製/應用到從機。通常這個延時是由網路頻寬、資源可用性和系統負載決定的。然 而，使用正確的元件並且調優，複製能做到接近瞬時完成。
 同步複製：使用MyISAM或者InnoDB儲存引擎的MySQL本身並不支援同步複製，同步複製可以定義為資料在同一時刻被提交到一臺或多臺機器，通常這是透過眾所周知的“兩階段提交”做到的，也就是說保證資料至少在一臺slave上正常commit。雖然這確實給你在多系統中保持一致性，但也由於增加了額外的訊息交換而造成效能下降。
半同步複製：是基於Google為MySQL開發的半同步複製的外掛。半同步複製的原理是，一個事務在主伺服器上執行完成後，必須至少確保至少在一臺從伺服器上執行完成後，事務才算提交成功。如果在一定時間內從伺服器沒有響應，則會自動降級為非同步複製。
這個半同步複製是建立在非同步複製的基礎之上進行的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2136185/，如需轉載，請註明出處，否則將追究法律責任。

相關文章