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

2024-07-24 12:34:32

字體：大中小

來源：轉載

供稿：網友

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

　　一、MySQL主從不同步情況

　　1.1 網絡的延遲

　　由于mysql主從復制是基于binlog的一種異步復制

　　通過網絡傳送binlog文件，理所當然網絡延遲是主從不同步的絕大多數的原因，特別是跨機房的數據同步出現這種幾率非常的大，所以做讀寫分離，注意從業務層進行前期設計。

　　1.2 主從兩臺機器的負載不一致

　　由于mysql主從復制是主數據庫上面啟動1個io線程，而從上面啟動1個sql線程和1個io線程，當中任何一臺機器的負載很高，忙不過來，導致其中的任何一個線程出現資源不足，都將出現主從不一致的情況。

　　1.3 max_allowed_packet設置不一致

　　主數據庫上面設置的max_allowed_packet比從數據庫大，當一個大的sql語句，能在主數據庫上面執行完畢，從數據庫上面設置過小，無法執行，導致的主從不一致。

　　1.4 自增鍵不一致

　　key自增鍵開始的鍵值跟自增步長設置不一致引起的主從不一致。

　　1.5 同步參數設置問題

　　mysql異常宕機情況下，如果未設置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出現binlog或者relaylog文件出現損壞，導致主從不一致。

　　1.6 自身bug

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

　　1.7 版本不一致

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

　　1.8 主從不一致優化配置

　　基于以上情況，先保證max_allowed_packet，自增鍵開始點和增長點設置一致

　　再者犧牲部分性能在主上面開啟sync_binlog，對于采用innodb的庫，推薦配置下面的內容

　　innodb_flush_logs_at_trx_commit = 1

　　innodb-support_xa = 1 # Mysql 5.0 以上

　　innodb_safe_binlog # Mysql 4.0

　　同時在從上面推薦加入下面兩個參數

　　skip_slave_start

　　read_only

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

　　2.1 主從不同步場景描述

　　今天發現Mysql的主從數據庫沒有同步

　　先上Master庫：

　　mysql>show processlist;

　　查看下進程是否Sleep太多。發現很正常。

　　show master status;

　　查看主庫狀態也正常。

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

　　1 row in set (0.00 sec)

　　復制代碼再到Slave上查看

　　mysql> show slave statusG

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: No

　　復制代碼由此可見是Slave不同步

　　2.2 解決方法一：忽略錯誤后，繼續同步

　　該方法適用于主從庫數據相差不大，或者要求數據可以不完全統一的情況，數據要求不嚴格的情況

　　解決：

　　stop slave;

　　復制代碼

　　表示跳過一步錯誤，后面的數字可變

　　set global sql_slave_skip_counter =1;

　　start slave;

　　復制代碼

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

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: Yes

　　復制代碼ok，現在主從同步狀態正常了。。。

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

　　該方法適用于主從庫數據相差較大，或者要求數據完全統一的情況

　　解決步驟如下：

　　1.先進入主庫，進行鎖表，防止數據寫入

　　使用命令：

　　mysql> flush tables with read lock;

　　注意：該處是鎖定為只讀狀態，語句不區分大小寫

　　2.進行數據備份

　　把數據備份到mysql.bak.sql文件

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

　　這里注意一點：數據庫備份一定要定期進行，可以用shell腳本或者python腳本，都比較方便，確保數據萬無一失

　　3.查看master 狀態

　　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)

　　復制代碼

　　4.把mysql備份文件傳到從庫機器，進行數據恢復

　　使用scp命令

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

　　5.停止從庫的狀態

　　mysql> stop slave;

　　6.然后到從庫執行mysql命令，導入數據備份

　　mysql> source /tmp/mysql.bak.sql

　　7.設置從庫同步，注意該處的同步點，就是主庫show master status信息里的| File| Position兩項

　　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.查看同步狀態

　　mysql> show slave statusG 查看：

　　Slave_IO_Running: Yes

　　Slave_SQL_Running: Yes

　　好了，同步完成啦

　　在此給大家分享一份學習資料，里面包括：（BATJ面試資料、高可用、高并發、高性能及分布式、Jvm性能調優、Spring源碼，MyBatis，Netty，Redis，Kafka，Mysql，Zookeeper，Tomcat，Docker，Dubbo，Nginx等多個知識點的架構資料）和Java進階學習路線圖，免費領取加 q群：478052716 即可！

　　三、如何監控mysql主從之間的延遲

　　3.1 前言：

　　日常工作中，對于MYSQL主從復制的檢查有兩方面

　　保證復制的整體結構是否完整；

　　需要檢查數據是否一致；

　　對于前者我們可以通過監控復制線程是否工作正常以及主從延時是否在容忍范圍內，對于后者則可以通過分別校驗主從表中數據的md5碼是否一致，來保證數據一致，可以使用Maatkit工具包中的mk-table-checksum工具去檢查。

　　本文檔介紹下關于如何檢查主從延遲的問題。

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

　　3.2方法1.

　　通過監控show slave statusG命令輸出的Seconds_Behind_Master參數的值來判斷，是否有發生主從延時。

　　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)

　　復制代碼以上是show slave statusG的輸出結果，這些結構給我們的監控提供了很多有意義的參數。

　　Slave_IO_Running

　　該參數可作為io_thread的監控項，Yes表示io_thread的和主庫連接正常并能實施復制工作，No則說明與主庫通訊異常，多數情況是由主從間網絡引起的問題；

　　Slave_SQL_Running

　　該參數代表sql_thread是否正常，具體就是語句是否執行通過，常會遇到主鍵重復或是某個表不存在。

　　Seconds_Behind_Master

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

　　正值 — 表示主從已經出現延時，數字越大表示從庫落后主庫越多。負值 — 幾乎很少見，我只是聽一些資深的DBA說見過，其實，這是一個BUG值，該參數是不支持負值的，也就是不應該出現。

　　備注Seconds_Behind_Master的計算方式可能帶來的問題

　　我們都知道的relay-log和主庫的bin-log里面的內容完全一樣，在記錄sql語句的同時會被記錄上當時的ts，所以比較參考的值來自于binlog，其實主從沒有必要與NTP進行同步，也就是說無需保證主從時鐘的一致。你也會發現，其實比較真正是發生在io_thread與sql_thread之間，而io_thread才真正與主庫有關聯，于是，問題就出來了，

　　當主庫I/O負載很大或是網絡阻塞

　　io_thread不能及時復制binlog（沒有中斷，也在復制），而sql_thread一直都能跟上io_thread的腳本，這時Seconds_Behind_Master的值是0，

　　也就是我們認為的無延時，但是，實際上不是，你懂得。

　　這也就是為什么大家要批判用這個參數來監控數據庫是否發生延時不準的原因，但是這個值并不是總是不準，

　　如果當io_thread與master網絡很好的情況下，那么該值也是很有價值的。’‘之前，提到Seconds_Behind_Master這個參數會有負值出現，我們已經知道該值是io_thread的最近跟新的ts與sql_thread執行到的ts差值，

　　前者始終是大于后者的，唯一的肯能就是某個event的ts發生了錯誤，比之前的小了，那么當這種情況發生時，負值出現就成為可能。

　　3.2 方法2.

　　mk-heartbeat：Maatkit萬能工具包中的一個工具，被認為可以準確判斷復制延時的方法。

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

（編輯：武林網）

上一篇：學會用各種方式備份MySQL數據庫

下一篇：MySQL分庫分表環境下全局ID生成方案