MySQL主從不同步問(wèn)題分析與解決思路

2024-07-24 12:32:06

字體：大中小

供稿：網(wǎng)友

之前部署了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主從同步過(guò)程中會(huì)出現(xiàn)很多問(wèn)題，導(dǎo)致數(shù)據(jù)同步異常，主要有兩個(gè)比較頭疼的問(wèn)題：

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

2、主從同步延遲問(wèn)題如何解決？

slave同步延遲的可能原因

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

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

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

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

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

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

如何查看同步延遲

1--可以通過(guò)比對(duì)master、slave上的日志位置

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

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

減少同步延遲的操作方案

1--減少鎖競(jìng)爭(zhēng)

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

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來(lái)提高sql的執(zhí)行效率（這兩個(gè)參數(shù)很管用）

5--并行復(fù)制

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

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

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

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

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

（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)致主從庫(kù)不同步（前提是主庫(kù)的binlog日志沒(méi)有被暴力刪除或錯(cuò)誤刪除，即要確保正在使用的那個(gè)最新binlog文件在master主庫(kù)機(jī)器上存在）。

解決辦法：

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

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

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

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

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

（編輯：武林網(wǎng)）

上一篇：MySQL show status命令輸出結(jié)果解說(shuō)

下一篇：MySQL存儲(chǔ)過(guò)程詳解 mysql 存儲(chǔ)步驟