MySQL主從不一致情形與解決辦法

2024-07-24 12:32:42

字體：大中小

供稿：網(wǎng)友

MySQL主從不一致情形與解決辦法：

一、MySQL主從不同步情況

1.1 網(wǎng)絡(luò)的延遲

由于mysql主從復(fù)制是基于binlog的一種異步復(fù)制

通過(guò)網(wǎng)絡(luò)傳送binlog文件，理所當(dāng)然網(wǎng)絡(luò)延遲是主從不同步的絕大多數(shù)的原因，特別是跨機(jī)房的數(shù)據(jù)同步出現(xiàn)這種幾率非常的大，所以做讀寫分離，注意從業(yè)務(wù)層進(jìn)行前期設(shè)計(jì)。

1.2 主從兩臺(tái)機(jī)器的負(fù)載不一致

由于mysql主從復(fù)制是主數(shù)據(jù)庫(kù)上面啟動(dòng)1個(gè)io線程，而從上面啟動(dòng)1個(gè)sql線程和1個(gè)io線程，當(dāng)中任何一臺(tái)機(jī)器的負(fù)載很高，忙不過(guò)來(lái)，導(dǎo)致其中的任何一個(gè)線程出現(xiàn)資源不足，都將出現(xiàn)主從不一致的情況。

1.3 max_allowed_packet設(shè)置不一致

主數(shù)據(jù)庫(kù)上面設(shè)置的max_allowed_packet比從數(shù)據(jù)庫(kù)大，當(dāng)一個(gè)大的sql語(yǔ)句，能在主數(shù)據(jù)庫(kù)上面執(zhí)行完畢，從數(shù)據(jù)庫(kù)上面設(shè)置過(guò)小，無(wú)法執(zhí)行，導(dǎo)致的主從不一致。

1.4 自增鍵不一致

key自增鍵開始的鍵值跟自增步長(zhǎng)設(shè)置不一致引起的主從不一致。

1.5 同步參數(shù)設(shè)置問(wèn)題

mysql異常宕機(jī)情況下，如果未設(shè)置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出現(xiàn)binlog或者relaylog文件出現(xiàn)損壞，導(dǎo)致主從不一致。

1.6 自身bug

mysql本身的bug引起的主從不同步

1.7 版本不一致

特別是高版本是主，低版本為從的情況下，主數(shù)據(jù)庫(kù)上面支持的功能，從數(shù)據(jù)庫(kù)上面不支持該功能。

1.8 主從不一致優(yōu)化配置

基于以上情況，先保證max_allowed_packet，自增鍵開始點(diǎn)和增長(zhǎng)點(diǎn)設(shè)置一致

再者犧牲部分性能在主上面開啟sync_binlog，對(duì)于采用innodb的庫(kù)，推薦配置下面的內(nèi)容

innodb_flush_logs_at_trx_commit = 1

innodb-support_xa = 1 # Mysql 5.0 以上

innodb_safe_binlog # Mysql 4.0

同時(shí)在從上面推薦加入下面兩個(gè)參數(shù)

skip_slave_start

read_only

二、解決主從不同步的方法

2.1 主從不同步場(chǎng)景描述

今天發(fā)現(xiàn)Mysql的主從數(shù)據(jù)庫(kù)沒(méi)有同步

先上Master庫(kù)：

mysql>show processlist;

查看下進(jìn)程是否Sleep太多。發(fā)現(xiàn)很正常。

show master status;

查看主庫(kù)狀態(tài)也正常。

mysql> show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBmysqld-bin.0000013260mysql,test,information_schema

1 row in set (0.00 sec)

復(fù)制代碼再到Slave上查看

mysql> show slave statusG

Slave_IO_Running: Yes

Slave_SQL_Running: No

復(fù)制代碼由此可見(jiàn)是Slave不同步

2.2 解決方法一：忽略錯(cuò)誤后，繼續(xù)同步

該方法適用于主從庫(kù)數(shù)據(jù)相差不大，或者要求數(shù)據(jù)可以不完全統(tǒng)一的情況，數(shù)據(jù)要求不嚴(yán)格的情況

解決：

stop slave;

復(fù)制代碼

表示跳過(guò)一步錯(cuò)誤，后面的數(shù)字可變

set global sql_slave_skip_counter =1;

start slave;

復(fù)制代碼

之后再用mysql> show slave statusG 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

復(fù)制代碼ok，現(xiàn)在主從同步狀態(tài)正常了。。。

2.3 方式二：重新做主從，完全同步

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

解決步驟如下：

1.先進(jìn)入主庫(kù)，進(jìn)行鎖表，防止數(shù)據(jù)寫入

使用命令：

mysql> flush tables with read lock;

注意：該處是鎖定為只讀狀態(tài)，語(yǔ)句不區(qū)分大小寫

2.進(jìn)行數(shù)據(jù)備份

把數(shù)據(jù)備份到mysql.bak.sql文件

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

這里注意一點(diǎn)：數(shù)據(jù)庫(kù)備份一定要定期進(jìn)行，可以用shell腳本或者python腳本，都比較方便，確保數(shù)據(jù)萬(wàn)無(wú)一失

3.查看master 狀態(tài)

mysql> show master status;

+——————-+———-+————–+——————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————-+———-+————–+——————————-+

| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |

+——————-+———-+————–+——————————-+

1 row in set (0.00 sec)

復(fù)制代碼

4.把mysql備份文件傳到從庫(kù)機(jī)器，進(jìn)行數(shù)據(jù)恢復(fù)

使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.1.206:/tmp/

5.停止從庫(kù)的狀態(tài)

mysql> stop slave;

6.然后到從庫(kù)執(zhí)行mysql命令，導(dǎo)入數(shù)據(jù)備份

mysql> source /tmp/mysql.bak.sql

7.設(shè)置從庫(kù)同步，注意該處的同步點(diǎn)，就是主庫(kù)show master status信息里的| File| Position兩項(xiàng)

change master to master_host = ‘192.168.1.206’, master_user = ‘rsync’, master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001’, master_log_pos=3260;

8.重新開啟從同步

mysql> start slave;

9.查看同步狀態(tài)

mysql> show slave statusG 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了，同步完成啦

在此給大家分享一份學(xué)習(xí)資料，里面包括：（BATJ面試資料、高可用、高并發(fā)、高性能及分布式、Jvm性能調(diào)優(yōu)、Spring源碼，MyBatis，Netty，Redis，Kafka，Mysql，Zookeeper，Tomcat，Docker，Dubbo，Nginx等多個(gè)知識(shí)點(diǎn)的架構(gòu)資料）和Java進(jìn)階學(xué)習(xí)路線圖，免費(fèi)領(lǐng)取加 q群：478052716 即可！

三、如何監(jiān)控mysql主從之間的延遲

3.1 前言：

日常工作中，對(duì)于MYSQL主從復(fù)制的檢查有兩方面

保證復(fù)制的整體結(jié)構(gòu)是否完整；

需要檢查數(shù)據(jù)是否一致；

對(duì)于前者我們可以通過(guò)監(jiān)控復(fù)制線程是否工作正常以及主從延時(shí)是否在容忍范圍內(nèi)，對(duì)于后者則可以通過(guò)分別校驗(yàn)主從表中數(shù)據(jù)的md5碼是否一致，來(lái)保證數(shù)據(jù)一致，可以使用Maatkit工具包中的mk-table-checksum工具去檢查。

本文檔介紹下關(guān)于如何檢查主從延遲的問(wèn)題。

主從延遲判斷的方法，通常有兩種方法：Seconds_Behind_Master和mk-heartbeat

3.2方法1.

通過(guò)監(jiān)控show slave statusG命令輸出的Seconds_Behind_Master參數(shù)的值來(lái)判斷，是否有發(fā)生主從延時(shí)。

mysql> show slave statusG;

1. row **

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.205
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000008
Read_Master_Log_Pos: 120
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 287
Relay_Master_Log_File: edu-mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 464
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:

Master_Server_Id: 205
Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

復(fù)制代碼以上是show slave statusG的輸出結(jié)果，這些結(jié)構(gòu)給我們的監(jiān)控提供了很多有意義的參數(shù)。

Slave_IO_Running

該參數(shù)可作為io_thread的監(jiān)控項(xiàng)，Yes表示io_thread的和主庫(kù)連接正常并能實(shí)施復(fù)制工作，No則說(shuō)明與主庫(kù)通訊異常，多數(shù)情況是由主從間網(wǎng)絡(luò)引起的問(wèn)題；

Slave_SQL_Running

該參數(shù)代表sql_thread是否正常，具體就是語(yǔ)句是否執(zhí)行通過(guò)，常會(huì)遇到主鍵重復(fù)或是某個(gè)表不存在。

Seconds_Behind_Master

是通過(guò)比較sql_thread執(zhí)行的event的timestamp和io_thread復(fù)制好的event的timestamp(簡(jiǎn)寫為ts)進(jìn)行比較，而得到的這么一個(gè)差值；NULL—表示io_thread或是sql_thread有任何一個(gè)發(fā)生故障，也就是該線程的Running狀態(tài)是No，而非Yes。0 — 該值為零，是我們極為渴望看到的情況，表示主從復(fù)制良好，可以認(rèn)為lag不存在。

正值 — 表示主從已經(jīng)出現(xiàn)延時(shí)，數(shù)字越大表示從庫(kù)落后主庫(kù)越多。負(fù)值 — 幾乎很少見(jiàn)，我只是聽(tīng)一些資深的DBA說(shuō)見(jiàn)過(guò)，其實(shí)，這是一個(gè)BUG值，該參數(shù)是不支持負(fù)值的，也就是不應(yīng)該出現(xiàn)。

備注Seconds_Behind_Master的計(jì)算方式可能帶來(lái)的問(wèn)題

我們都知道的relay-log和主庫(kù)的bin-log里面的內(nèi)容完全一樣，在記錄sql語(yǔ)句的同時(shí)會(huì)被記錄上當(dāng)時(shí)的ts，所以比較參考的值來(lái)自于binlog，其實(shí)主從沒(méi)有必要與NTP進(jìn)行同步，也就是說(shuō)無(wú)需保證主從時(shí)鐘的一致。你也會(huì)發(fā)現(xiàn)，其實(shí)比較真正是發(fā)生在io_thread與sql_thread之間，而io_thread才真正與主庫(kù)有關(guān)聯(lián)，于是，問(wèn)題就出來(lái)了，

當(dāng)主庫(kù)I/O負(fù)載很大或是網(wǎng)絡(luò)阻塞

io_thread不能及時(shí)復(fù)制binlog（沒(méi)有中斷，也在復(fù)制），而sql_thread一直都能跟上io_thread的腳本，這時(shí)Seconds_Behind_Master的值是0，

也就是我們認(rèn)為的無(wú)延時(shí)，但是，實(shí)際上不是，你懂得。

這也就是為什么大家要批判用這個(gè)參數(shù)來(lái)監(jiān)控?cái)?shù)據(jù)庫(kù)是否發(fā)生延時(shí)不準(zhǔn)的原因，但是這個(gè)值并不是總是不準(zhǔn)，

如果當(dāng)io_thread與master網(wǎng)絡(luò)很好的情況下，那么該值也是很有價(jià)值的。’‘之前，提到Seconds_Behind_Master這個(gè)參數(shù)會(huì)有負(fù)值出現(xiàn)，我們已經(jīng)知道該值是io_thread的最近跟新的ts與sql_thread執(zhí)行到的ts差值，

前者始終是大于后者的，唯一的肯能就是某個(gè)event的ts發(fā)生了錯(cuò)誤，比之前的小了，那么當(dāng)這種情況發(fā)生時(shí)，負(fù)值出現(xiàn)就成為可能。

3.2 方法2.

mk-heartbeat：Maatkit萬(wàn)能工具包中的一個(gè)工具，被認(rèn)為可以準(zhǔn)確判斷復(fù)制延時(shí)的方法。

mk-heartbeat的實(shí)現(xiàn)也是借助timestmp的比較實(shí)現(xiàn)的，它首先需要保證主從服務(wù)器必須要保持一致，通過(guò)與相同的一個(gè)NTP server同步時(shí)鐘。它需要在主庫(kù)上創(chuàng)建一個(gè)heartbeat的表，里面至少有id與ts兩個(gè)字段，id為server_id，ts就是當(dāng)前的時(shí)間戳now()，該結(jié)構(gòu)也會(huì)被復(fù)制到從庫(kù)上，表建好以后，會(huì)在主庫(kù)上以后臺(tái)進(jìn)程的模式去執(zhí)行一行更新操作的命令，定期去向表中的插入數(shù)據(jù)，這個(gè)周期默認(rèn)為1秒，同時(shí)從庫(kù)也會(huì)在后臺(tái)執(zhí)行一個(gè)監(jiān)控命令，與主庫(kù)保持一致的周期去比較，復(fù)制過(guò)來(lái)記錄的ts值與主庫(kù)上的同一條ts值，差值為0表示無(wú)延時(shí)，差值越大表示延時(shí)的秒數(shù)越多。我們都知道復(fù)制是異步的ts不肯完全一致，所以該工具允許半秒的差距，在這之內(nèi)的差異都可忽略認(rèn)為無(wú)延時(shí)。這個(gè)工具就是通過(guò)實(shí)打?qū)嵉膹?fù)制，巧妙的借用timestamp來(lái)檢查延時(shí)。

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

上一篇：學(xué)會(huì)用各種方法備份MySQL數(shù)據(jù)庫(kù)

下一篇：MySQL分庫(kù)分表環(huán)境下全局ID生成辦法