監控mysql數據庫主從狀態,數據庫主從模式

本文目錄一覽：

• 1、MySQL 主從，5 分鐘帶你掌握
• 2、如何實現實時監控mysql數據庫主從同步的狀態
• 3、如何檢測mysql主從不同步
• 4、如何檢查MySQL數據庫的主從延時
• 5、如何監控MySQL主從同步情況

MySQL 主從，5 分鐘帶你掌握

MySQL 主從一直是面試常客，裡面的知識點雖然基礎，但是能回答全的同學不多。

比如樓哥之前面試小米，就被問到過主從複製的原理，以及主從延遲的解決方案，因為回答的非常不錯，給面試官留下非常好的印象。你之前面試，有遇到過哪些 MySQL 主從的問題呢？

所謂 MySQL 主從，就是建立兩個完全一樣的數據庫，一個是主庫，一個是從庫， 主庫對外提供讀寫的操作，從庫對外提供讀的操作 ，下面是一主一從模式：

對於數據庫單機部署，在 4 核 8G 的機器上運行 MySQL 5.7 時，大概可以支撐 500 的 TPS 和 10000 的 QPS， 當遇到一些活動時，查詢流量驟然，就需要進行主從分離。

大部分系統的訪問模型是讀多寫少，讀寫請求量的差距可能達到幾個數量級，所以我們可以通過一主多從的方式， 主庫只負責寫入和部分核心邏輯的查詢，多個從庫只負責查詢，提升查詢性能，降低主庫壓力。

MySQL 主從還能做到服務高可用，當主庫宕機時，從庫可以切成主庫，保證服務的高可用，然後主庫也可以做數據的容災備份。

整體場景總結如下：

MySQL 的主從複製是依賴於 binlog 的，也就是記錄 MySQL 上的所有變化並以二進制形式保存在磁盤上二進制日誌文件。

主從複製就是將 binlog 中的數據從主庫傳輸到從庫上，一般這個過程是異步的，即主庫上的操作不會等待 binlog 同步的完成。

詳細流程如下：

當主庫和從庫數據同步時，突然中斷怎麼辦？因為主庫與從庫之間維持了一個長鏈接，主庫內部有一個線程，專門服務於從庫的這個長鏈接的。

對於下面的情況，假如主庫執行如下 SQL，其中 a 和 create_time 都是索引：

我們知道，數據選擇了 a 索引和選擇 create_time 索引，最後 limit 1 出來的數據一般是不一樣的。

所以就會存在這種情況：在 binlog = statement 格式時，主庫在執行這條 SQL 時，使用的是索引 a，而從庫在執行這條 SQL 時，使用了索引 create_time，最後主從數據不一致了。

那麼我們改如何解決呢？

可以把 binlog 格式修改為 row，row 格式的 binlog 日誌記錄的不是 SQL 原文，而是兩個 event:Table_map 和 Delete_rows。

Table_map event 說明要操作的表，Delete_rows event用於定義要刪除的行為，記錄刪除的具體行數。 row 格式的 binlog 記錄的就是要刪除的主鍵 ID 信息，因此不會出現主從不一致的問題。

但是如果 SQL 刪除 10 萬行數據，使用 row 格式就會很占空間的，10 萬條數據都在 binlog 裡面，寫 binlog 的時候也很耗 IO。但是 statement 格式的 binlog 可能會導致數據不一致。

設計 MySQL 的大叔想了一個折中的方案，mixed 格式的 binlog，其實就是 row 和 statement 格式混合使用， 當 MySQL 判斷可能數據不一致時，就用 row 格式，否則使用就用 statement 格式。

有時候我們遇到從數據庫中獲取不到信息的詭異問題時，會糾結於代碼中是否有一些邏輯會把之前寫入的內容刪除，但是你又會發現，過了一段時間再去查詢時又可以讀到數據了，這基本上就是主從延遲在作怪。

主從延遲，其實就是“從庫回放” 完成的時間，與 “主庫寫 binlog” 完成時間的差值， 會導致從庫查詢的數據，和主庫的不一致 。

談到 MySQL 數據庫主從同步延遲原理，得從 MySQL 的主從複製原理說起：

總結一下主從延遲的主要原因 ：主從延遲主要是出現在 “relay log 回放” 這一步，當主庫的 TPS 並發較高，產生的 DDL 數量超過從庫一個 SQL 線程所能承受的範圍，那麼延時就產生了，當然還有就是可能與從庫的大型 query 語句產生了鎖等待。

我們一般會把從庫落後的時間作為一個重點的數據庫指標做監控和報警，正常的時間是在毫秒級別，一旦落後的時間達到了秒級別就需要告警了。

解決該問題的方法，除了縮短主從延遲的時間，還有一些其它的方法，基本原理都是盡量不查詢從庫。

具體解決方案如下：

在實際應用場景中，對於一些非常核心的場景，比如庫存，支付訂單等，需要直接查詢從庫，其它非核心場景，就不要去查主庫了。

兩台機器 A 和 B，A 為主庫，負責讀寫，B 為從庫，負責讀數據。

如果 A 庫發生故障，B 庫成為主庫負責讀寫，修復故障後，A 成為從庫，主庫 B 同步數據到從庫 A。

一台主庫多台從庫，A 為主庫，負責讀寫，B、C、D為從庫，負責讀數據。

如果 A 庫發生故障，B 庫成為主庫負責讀寫，C、D負責讀，修復故障後，A 也成為從庫，主庫 B 同步數據到從庫 A。

如何實現實時監控mysql數據庫主從同步的狀態

1、增加一個用戶同步使用的帳號：

GRANT FILE ON *.* TO ‘backup’@’10.10.8.112′ IDENTIFIED BY ‘1234’;

GRANTREPLICATION SLAVE ON *.* TO ‘backup’@’10.10.8.112′ IDENTIFIED BY ‘1234’;

賦予10.10.8.112也就是Slave機器有File權限，只賦予Slave機器有File權限還不行，還要給它REPLICATION SLAVE的權

限才可以。

2、增加一個數據庫作為同步數據庫：

create databbse test;

3、創建一個表結構：

create table mytest (username varchar(20),password varchar(20));

4、修改配置文件：

修改A的/etc/my.cnf文件，在my.cnf配置項中加入下面配置：

server-id = 1 #Server標識

log-bin

binlog-do-db=test #指定需要日誌的數據庫

5、重起數據庫服務：

service mysqld restart

查看server-id：

show variable like ‘server_id’；

實例：

mysql show variables like ‘server_id’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| server_id | 1 |

+—————+——-+

1 row in set (0.00 sec)

6、用show master status/G命令看日誌情況。

正常為：

mysql show master status/G

如何檢測mysql主從不同步

方法一：忽略錯誤後，繼續同步

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

解決：

stop slave;

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

set global sql_slave_skip_counter =1;

start slave;

之後再用mysql show slave status\G 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

ok，現在主從同步狀態正常了。。。

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

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

解決步驟如下：

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.128.101:/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.128.100’, 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 status\G 查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了，同步完成啦。

如何檢查MySQL數據庫的主從延時

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

方法1：

通過監控show slave status\G命令輸出的Seconds_Behind_Master參數的值來判斷，是否有發生主從延時。其值有這麼幾種：

NULL — 表示io_thread或是sql_thread有任何一個發生故障，也就是該線程的Running狀態是No，而非Yes。

0 — 該值為零，是我們極為渴望看到的情況，表示主從複製良好，可以認為lag不存在。

正值 — 表示主從已經出現延時，數字越大表示從庫落後主庫越多。

負值 — 幾乎很少見，我只是聽一些資深的DBA說見過，其實，這是一個BUG值，該參數是不支持負值的，也就是不應該出現。

show slave status\G，該命令的輸出結果非常豐厚，給我們的監控提供了很多有意義的參數，比如：Slave_IO_Running該參數可作為 io_thread的監控項，Yes表示io_thread的和主庫連接正常並能實施複製工作，No則說明與主庫通訊異常，多數情況是由主從間網絡引起的問題；Slave_SQL_Running該參數代表sql_thread是否正常，具體就是語句是否執行通過，常會遇到主鍵重複或是某個表不存在。下面就說到今天的重點Seconds_Behind_Master，該值作為判斷主從延時的指標，那麼它又是怎麼得到這個值的呢，同時，它為什麼又受到很多人的質疑？

Seconds_Behind_Master是通過比較sql_thread執行的event的timestamp和 io_thread複製好的event的timestamp（簡寫為ts）進行比較，而得到的這麼一個差值。我們都知道的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發生了錯誤，比之前的小了，那麼當這種情況發生時，負值出現就成為可能。

方法2：

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

mk-heartbeat的實現也是藉助timestmp的比較實現的，它首先需要保證主從服務器必須要保持一致，通過與相同的一個NTPserver同步時鐘。它需要在主庫上創建一個heartbeat的表，裡面至少有id與ts兩個字段，id為server_id，ts就是當前的時間戳 now()，該結構也會被複制到從庫上。

表建好以後，會在主庫上以後台進程的模式去執行一行更新操作的命令，定期去向表中的插入數據，這個周期默認為1 秒，同時從庫也會在後台執行一個監控命令，與主庫保持一致的周期去比較，複製過來記錄的ts值與主庫上的同一條ts值，差值為0表示無延時，差值越大表示延時的秒數越多。

我們都知道複製是異步的ts不肯完全一致，所以該工具允許半秒的差距，在這之內的差異都可忽略認為無延時。這個工具就是通過實打實的複製，巧妙的借用timestamp來檢查延時，非常好用

如何監控MySQL主從同步情況

你好，

MySQL同步功能由3個線程(master上1個，slave上2個)來實現，簡單的說就是：master發送日誌一個，slave接收日誌一個，slave運行日誌一個。

首先，我們解釋一下 show slave status 中重要的幾個參數:

Slave_IO_Running： I/O線程是否被啟動並成功地連接到主服務器上。

Slave_SQL_Running： SQL線程是否被啟動。

Seconds_Behind_Master：

本字段是從屬服務器“落後”多少的一個指示。當從屬SQL線程正在運行時（處理更新），本字段為在主服務器上由此線程執行的最近的一個事件的時間標記開始，已經過的秒數。當此線程被從屬服務器I/O線程趕上，並進入閑置狀態，等待來自I/O線程的更多的事件時，本字段為零。總之，本字段測量從屬服務器SQL線程和從屬服務器I/O線程之間的時間差距，單位以秒計。