MySQL主從不同步問題分析與處理思路-創(chuàng)新互聯(lián)

之前部署了Mysql主從復(fù)制環(huán)境（MySQL主從復(fù)制環(huán)境部署【http://blog.itpub.net/31015730/viewspace-2153251/】）以及總結(jié)了mysql主從復(fù)制的原理和相關(guān)知識(shí)（MySQL主從復(fù)制原理及必備知識(shí)總結(jié)【http://blog.itpub.net/31015730/viewspace-2154408/】），但是在mysql主從同步過程中會(huì)出現(xiàn)很多問題，導(dǎo)致數(shù)據(jù)同步異常，主要有兩個(gè)比較頭疼的問題：

專業(yè)成都網(wǎng)站建設(shè)公司，做排名好的好網(wǎng)站，排在同行前面，為您帶來客戶和效益!創(chuàng)新互聯(lián)公司為您提供成都網(wǎng)站建設(shè)，五站合一網(wǎng)站設(shè)計(jì)制作，服務(wù)好的網(wǎng)站設(shè)計(jì)公司，網(wǎng)站建設(shè)、成都做網(wǎng)站負(fù)責(zé)任的成都網(wǎng)站制作公司!

1、主從數(shù)據(jù)不同步后如何處理？

2、主從同步延遲問題如何解決？

以下內(nèi)容梳理了幾種主從同步中可能存在的問題以及解決思路:

（1）slave運(yùn)行過慢不能與master同步，也就是MySQL數(shù)據(jù)庫主從同步延遲

MySQL數(shù)據(jù)庫slave服務(wù)器延遲的現(xiàn)象是非常普遍的，MySQL復(fù)制允許從機(jī)進(jìn)行SELECT操作，但是在實(shí)際生產(chǎn)環(huán)境下，由于從機(jī)延遲的關(guān)系，很難將讀取操作轉(zhuǎn)向到從機(jī)。這就導(dǎo)致了有了以下一些潛規(guī)則：“實(shí)時(shí)性要求不高的讀取操作可以放到slave服務(wù)器，實(shí)時(shí)性要求高的讀取操作放到master服務(wù)器”，“從機(jī)僅能做前一天的統(tǒng)計(jì)類查詢”。

slave滯后即slave不能快速執(zhí)行來自于master的所有事件，從而不能避免更新slave數(shù)據(jù)延遲。

mysql的master-slave架構(gòu)中master僅做寫入、更新、刪除操作，slave做select操作。造成slave滯后的原因有很多。

slave同步延遲的原理

MySQL的主從復(fù)制都是單線程的操作，主庫對所有DDL和DML產(chǎn)生的日志寫進(jìn)binlog，由于binlog是順序?qū)?，所以效率很高?/p>

Slave的IO Thread線程從主庫中bin log中讀取取日志。

Slave的SQL Thread線程將主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的，不是順序的，成本高很多。

由于SQL Thread也是單線程的，如果slave上的其他查詢產(chǎn)生lock爭用，又或者一個(gè)DML語句（大事務(wù)、大查詢）執(zhí)行了幾分鐘卡住了，那么所有之后的DML會(huì)等待這個(gè)DML執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。也許有人會(huì)質(zhì)疑：主庫上那個(gè)相同的DDL也會(huì)執(zhí)行幾分鐘，為什么slave會(huì)延時(shí)？原因是master可以并發(fā)執(zhí)行，而Slave_SQL_Running線程卻不可以。

slave同步延遲的可能原因

1--slave的I/O線程推遲讀取日志中的事件信息；最常見原因是slave是在單線程中執(zhí)行所有事務(wù)，而master有很多線程可以并行執(zhí)行事務(wù)。

2--帶來低效連接的長查詢、磁盤讀取的I/O限制、鎖競爭和innodb線程同步啟動(dòng)等。

3--Master負(fù)載；Slave負(fù)載

4--網(wǎng)絡(luò)延遲

5--機(jī)器配置（cpu、內(nèi)存、硬盤）

（主從同步延遲怎么產(chǎn)生的？）總之，當(dāng)主庫的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過slave一個(gè)sql線程所能處理的承受范圍時(shí)，主從同步就會(huì)產(chǎn)生延時(shí)；或者當(dāng)slave中有大型query語句產(chǎn)生了鎖等待也會(huì)產(chǎn)生延時(shí)。

如何查看同步延遲

1--可以通過比對master、slave上的日志位置

2--通過"show slave status\G"查看Seconds_Behind_Master的值，這個(gè)值代表主從同步延遲的時(shí)間，值越大說明延遲越嚴(yán)重。值為0為正常情況，正值表示已經(jīng)出現(xiàn)延遲，數(shù)字越大從庫落后主庫越多。

3--使用percona-toolkit的pt-hearbeat工具進(jìn)行查看。

減少同步延遲的操作方案

1--減少鎖競爭

如果查詢導(dǎo)致大量的表鎖定，需要考慮重構(gòu)查詢語句，盡量避免過多的鎖。

2--負(fù)載均衡

搭建多少slave，并且使用lvs或nginx進(jìn)行查詢負(fù)載均衡，可以減少每個(gè)slave執(zhí)行查詢的次數(shù)和時(shí)間，從而將更多的時(shí)間用于去處理主從同步。

3--salve較高的機(jī)器配置

4--slave調(diào)整參數(shù)

為了保障較高的數(shù)據(jù)安全性，配置sync_binlog=1，innodb_flush_log_at_trx_commit=1等設(shè)置。而Slave可以關(guān)閉binlog，innodb_flush_log_at_trx_commit也可以設(shè)置為0來提高sql的執(zhí)行效率（這兩個(gè)參數(shù)很管用）

5--并行復(fù)制

即將單線程的復(fù)制改成多線程復(fù)制。

從庫有兩個(gè)線程與復(fù)制相關(guān)：io_thread 負(fù)責(zé)從主庫拿binlog并寫到relaylog， sql_thread 負(fù)責(zé)讀relaylog并執(zhí)行。

多線程的思路就是把sql_thread 變成分發(fā)線程，然后由一組worker_thread來負(fù)責(zé)執(zhí)行。

幾乎所有的并行復(fù)制都是這個(gè)思路，有不同的，便是sql_thread 的分發(fā)策略。

MySQL5.7的真正并行復(fù)制enhanced multi-threaded slave（MTS）很好的解決了主從同步復(fù)制的延遲問題。

（2）slave同步狀態(tài)中出現(xiàn)Slave_IO_Running: NO

報(bào)錯(cuò)：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因1：清理數(shù)據(jù)導(dǎo)致主從庫不同步（前提是主庫的binlog日志沒有被暴力刪除或錯(cuò)誤刪除，即要確保正在使用的那個(gè)最新binlog文件在master主庫機(jī)器上存在）。

解決辦法：

1）先進(jìn)入slave中執(zhí)行:"slave stop;"來停止從庫同步；

2）再去master中執(zhí)行:"flush logs;"來清空日志；

3）然后在master中執(zhí)行:"show master status;"查看下主庫的狀態(tài)，主要是日志的文件和position；

4）然后回到slave中，執(zhí)行:"CHANGE MASTER TO ......執(zhí)行同步指令

原因2：該錯(cuò)誤發(fā)生在從庫的io進(jìn)程從主庫拉取日志時(shí)，發(fā)現(xiàn)主庫的mysql_bin.index文件中第一個(gè)文件不存在。出現(xiàn)此類報(bào)錯(cuò)可能是由于你的slave 由于某種原因停止了好長一段時(shí)間，當(dāng)你重啟slave 復(fù)制的時(shí)候，在主庫上找不到相應(yīng)的binlog ,會(huì)報(bào)此類錯(cuò)誤?；蛘呤怯捎谀承┰O(shè)置主庫上的binlog被刪除了，導(dǎo)致從庫獲取不到對應(yīng)的binglog file。

解決辦法：

1）為了避免數(shù)據(jù)丟失，需要重新進(jìn)行slave同步操作。

2）注意主庫binlog的清理策略，選擇基于時(shí)間過期的刪除方式還是基于空間利用率的刪除方式。

3）記住最好不要使用"rm -rf"命令刪除binlog file，這樣不會(huì)同步修改mysql_bin.index 記錄的binlog 條目。在刪除binlog的時(shí)候確保主庫保留了從庫"show slave status\G"的Relay_Master_Log_File對應(yīng)的binlog file。任何時(shí)候都不能刪除正在使用的那個(gè)最新binlog文件；最好把bin-log文件不要?jiǎng)h除，最好給備份出來。

原因2的情況下，使用原因1的處理方案顯然是解決不了的！此時(shí)的解決方案是：

在從庫上執(zhí)行：

mysql> stop slave;

mysql> reset slave;

mysql> start slave;

mysql> show slave status \G;

（3）slave同步狀態(tài)中出現(xiàn)Slave_IO_Running: Connecting

導(dǎo)致這個(gè)錯(cuò)誤的原因一般是：

1--網(wǎng)絡(luò)不通

2--權(quán)限問題（連接master的用戶名和密碼跟master授權(quán)不一致）

3--連接時(shí)用的log file和pos節(jié)點(diǎn)跟"show master status"的結(jié)果不一致

（4）slave同步狀態(tài)中出現(xiàn)Slave_SQL_Running: No ，即slave不同步！

解決辦法：

第一種方法：忽略錯(cuò)誤后，繼續(xù)同步。

該方法適用于主從庫數(shù)據(jù)相差不大，或者要求數(shù)據(jù)可以不完全統(tǒng)一的情況，數(shù)據(jù)要求不嚴(yán)格的情況（下面均為在slave機(jī)器上的操作）

mysql> stop slave;

mysql> set global sql_slave_skip_counter =1; //表示跳過一步錯(cuò)誤，后面的數(shù)字可變；或者在my.cnf里添加slave-skip-errors = all（上面已在配置中添加）

mysql> start slave;

mysql> show slave status\G //查看

第二種方法：重新做主從，完全同步

該方法適用于主從庫數(shù)據(jù)相差較大，或者要求數(shù)據(jù)完全統(tǒng)一的情況

1--master主庫上操作

mysql> flush tables with read lock; //進(jìn)行鎖表，防止數(shù)據(jù)寫入。注意該處是鎖定為只讀狀態(tài)，語句不區(qū)分大小寫

#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql //主庫完全備份(如果是指定庫同步，就備份指定庫)，注意數(shù)據(jù)庫備份一定要定期進(jìn)行，確保數(shù)據(jù)萬無一失

mysql> show master status; //查看master狀態(tài)，注意log file和pos節(jié)點(diǎn)，slave同步會(huì)用到

# scp mysql.bak.sql root@192.168.1.102:/tmp/ //把備份文件傳到slave從庫機(jī)器，進(jìn)行數(shù)據(jù)恢復(fù)

2--slave從庫操作

mysql> stop slave;

mysql> source /tmp/mysql.bak.sql

mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......；

mysql> start slave;

mysql> show slave status\G

.......

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

此種方法中最為關(guān)鍵主要有兩步：

①主服務(wù)器上鎖表做完全備份，并滾動(dòng)日志；

②從服務(wù)器上進(jìn)行半道恢復(fù).

（5）slave中繼日志relay-log損壞？

什么是中繼日志？

relay-log存放在從服務(wù)器上，從服務(wù)器將主服務(wù)器的二進(jìn)制日志文件拷貝到自己的主機(jī)上放在中繼日志中，然后調(diào)用SQL線程按照拷中繼日志文件中的二進(jìn)制日志文件執(zhí)行以便就可達(dá)到數(shù)據(jù)的同步。

如何避免中繼日志損壞：

mysql 5.6版本后，在my.cnf文件中開啟relay_log_recover=1即可避免。

（6）slave連接超時(shí)且重新連接頻繁

若有多個(gè)slave，且沒有設(shè)置server_id或兩個(gè)slave設(shè)置相同的server_id，將有可能會(huì)出現(xiàn)服務(wù)器的ID沖突。這種情況下，其中一臺(tái)slave可能會(huì)頻繁超時(shí)或丟失后重新連接序列。

所以一定要確保每臺(tái)slave及master在my.cnf中都要設(shè)置不一樣的server_id。

（7）主庫與從庫使用不同的存儲(chǔ)引擎造成不同步

（8）從庫同步時(shí)，提示表不存在

錯(cuò)誤：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'

解決方法：在從庫重建這張表。

（9）max_allowed_packet設(shè)置過小導(dǎo)致slave報(bào)錯(cuò)

max_allowed_packet默認(rèn)是16M，主從庫的max_allowed_packet值和備庫上的不匹配。

在這情況下，主庫可能會(huì)記錄一個(gè)備庫認(rèn)為過大的包。當(dāng)備庫獲取到該二進(jìn)制日志事件時(shí)，可能會(huì)碰到各種問題，如無限報(bào)錯(cuò)和重試、中繼日志損壞等。

具體表現(xiàn)：

從庫的Slave_IO_Thread死掉了，查看后，出現(xiàn)以下錯(cuò)誤提示：

Got a packet bigger than 'max_allowed_packet' bytes

很明顯是由于max_allowed_packet的設(shè)置太小導(dǎo)致的，然后查檢主從庫上的設(shè)置，主庫的設(shè)置大于從庫，因?yàn)閙ax_allowed_packet是動(dòng)態(tài)參數(shù)，先調(diào)整從庫上的max_allowed_packet 與主庫相同，重新單獨(dú)啟動(dòng)I/O線程就正常了。

原理說明：binlog的事件以RBR格式記錄，且當(dāng)前的事件長度大于了從庫的max_allowed_packet, 導(dǎo)致無法Slave IO不能正常讀取master binlog event.

（10）在master上刪除一條記錄時(shí)出現(xiàn)的故障

在master上刪除一條記錄后，slave上因找不到這條記錄而報(bào)錯(cuò)。

解決方法：

由于主庫上已經(jīng)對這條語句進(jìn)行了刪除操作，故可以跳過。

在這種情況下，說明主從同步可能數(shù)據(jù)會(huì)有不一致的情況發(fā)生，所以需要使用pt-table-checksum進(jìn)行數(shù)據(jù)庫一致性比對。

（11）在master更新一條記錄，而slave卻找不到。

主從數(shù)據(jù)不致時(shí)，master有某條記錄，但在salve上沒有這條記錄，若在master上進(jìn)行更新這條記錄，則在slave中可能報(bào)錯(cuò)。

解決方法：

1--根據(jù)從庫發(fā)生異常的位置，查主庫上的二進(jìn)制日志。

2--根據(jù)主庫二進(jìn)制日志信息，找到更新后的整條記錄。

3--在從庫上執(zhí)行在主庫上找到的記錄信息，進(jìn)行insert操作。

4--跳過這條語句，再同步slave。

5--使用pt-table-checksum查看主從庫表數(shù)據(jù)否一致。

文章連接：/tupian/20230522/6261111.html

作者：SEian.G（苦練七十二變，笑對八十一難）

網(wǎng)頁標(biāo)題：MySQL主從不同步問題分析與處理思路-創(chuàng)新互聯(lián)
網(wǎng)站鏈接：http://muchs.cn/article48/icchp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、App設(shè)計(jì)、關(guān)鍵詞優(yōu)化、自適應(yīng)網(wǎng)站、企業(yè)網(wǎng)站制作、云服務(wù)器

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容