教你MySQL Binlog實用攻略
本文由雲+社群發表
1.概述
binlog是Mysql sever層維護的一種二進位制日誌，與innodb引擎中的redo/undo log是完全不同的日誌；其主要是用來記錄對mysql資料更新或潛在發生更新的SQL語句，並以”事務”的形式儲存在磁碟中；
作用主要有：
- [x] 複製：MySQL Replication在Master端開啟binlog，Master把它的二進位制日誌傳遞給slaves並回放來達到master-slave資料一致的目的
- [x] 資料恢復：通過mysqlbinlog工具恢復資料
- [x] 增量備份
2.binlog管理
- 開啟binlog my.cnf配置中設定：log_bin=”存放binlog路徑目錄”
- binlog資訊查詢 binlog開啟後，可以在配置檔案中檢視其位置資訊，也可以在myslq命令列中檢視： show variables like '%log_bin%'; +---------------------------------+-------------------------------------+ | Variable_name | Value | +---------------------------------+-------------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/3306/mysql-bin | | log_bin_index | /var/lib/mysql/3306/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------------------------------------+
- binlog檔案 開啟binlog後，會在資料目錄（預設）生產host-bin.n（具體binlog資訊）檔案及host-bin.index索引檔案（記錄binlog檔案列表）。當binlog日誌寫滿(binlog大小max_binlog_size，預設1G),或者資料庫重啟才會生產新檔案，但是也可通過手工進行切換讓其重新生成新的檔案（flush logs）；另外，如果正使用大的事務，由於一個事務不能橫跨兩個檔案，因此也可能在binlog檔案未滿的情況下重新整理檔案 mysql> show binary logs; //檢視binlog檔案列表, +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 177 | | mysql-bin.000003 | 10343266 | | mysql-bin.000004 | 10485660 | | mysql-bin.000005 | 53177 | | mysql-bin.000006 | 2177 | | mysql-bin.000007 | 1383 | +------------------+-----------+
- 檢視binlog的狀態： show master status可檢視當前二進位制日誌檔案的狀態資訊，顯示正在寫入的二進位制檔案，及當前position mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+
- reset master 清空binlog日誌檔案
3.binlog內容
預設情況下binlog日誌是二進位制格式，無法直接檢視。可使用兩種方式進行檢視：
a. mysqlbinlog: /usr/bin/mysqlbinlog mysql-bin.000007
- mysqlbinlog是mysql官方提供的一個binlog檢視工具，
- 也可使用–read-from-remote-server從遠端伺服器讀取二進位制日誌，
- 還可使用--start-position --stop-position、--start-time= --stop-time精確解析binlog日誌
擷取位置1190-1352 binlog如下：
***************************************************************************************
# at 1190 //事件的起點
#171223 21:56:26 server id 123 end_log_pos 1190 CRC32 0xf75c94a7 Intvar
SET INSERT_ID=2/*!*/;
#171223 21:56:26 server id 123 end_log_pos 1352 CRC32 0xefa42fea Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1514123786/*!*/; //開始事務的時間起點 (每個at即為一個event)
insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing" //sql語句
/*!*/;
# at 1352
#171223 21:56:26 server id 123 end_log_pos 1383 CRC32 0x72c565d3 Xid = 5 //執行時間，及位置戳，Xid:事件指示提交的XA事務
***************************************************************************************
b.直命令列解析
SHOW BINLOG EVENTS
[IN 'log_name'] //要查詢的binlog檔名
[FROM pos]
[LIMIT [offset,] row_count]
1190-135如下：mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
*************************** 13. row ***************************
Log_name: mysql-bin.000007
Pos: 1190
Event_type: Query //事件型別
Server_id: 123
End_log_pos: 1352 //結束pose點，下個事件的起點
Info: use `test`; insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing"
*************************** 14. row ***************************
Log_name: mysql-bin.000007
Pos: 1352
Event_type: Xid
Server_id: 123
End_log_pos: 1383
Info: COMMIT /* xid=51 */4.binlog格式
Mysql binlog日誌有ROW,Statement,MiXED三種格式；可通過my.cnf配置檔案及 ==++set global binlog_format=’ROW/STATEMENT/MIXED’++== 進行修改，命令列 ==++show variables like ‘binlog_format’++== 命令檢視binglog格式；。
- Row level: 僅儲存記錄被修改細節，不記錄sql語句上下文相關資訊 優點：能非常清晰的記錄下每行資料的修改細節，不需要記錄上下文相關資訊，因此不會發生某些特定情況下的procedure、function、及trigger的呼叫觸發無法被正確複製的問題，任何情況都可以被複制，且能加快從庫重放日誌的效率，保證從庫資料的一致性 缺點:由於所有的執行的語句在日誌中都將以每行記錄的修改細節來記錄，因此，可能會產生大量的日誌內容，干擾內容也較多；比如一條update語句，如修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中，實際等於重建了表。 tip: - row模式生成的sql編碼需要解碼，不能用常規的辦法去生成，需要加上相應的引數(--base64-output=decode-rows -v)才能顯示出sql語句; - 新版本binlog預設為ROW level，且5.6新增了一個引數：binlog_row_image；把binlog_row_image設定為minimal以後，binlog記錄的就只是影響的列，大大減少了日誌內容
- Statement level: 每一條會修改資料的sql都會記錄在binlog中 優點：只需要記錄執行語句的細節和上下文環境，避免了記錄每一行的變化，在一些修改記錄較多的情況下相比ROW level能大大減少binlog日誌量，節約IO，提高效能；還可以用於實時的還原；同時主從版本可以不一樣，從伺服器版本可以比主伺服器版本高 缺點：為了保證sql語句能在slave上正確執行，必須記錄上下文資訊，以保證所有語句能在slave得到和在master端執行時候相同的結果；另外，主從複製時，存在部分函式（如sleep）及儲存過程在slave上會出現與master結果不一致的情況，而相比Row level記錄每一行的變化細節，絕不會發生這種不一致的情況
- Mixedlevel level: 以上兩種level的混合使用 經過前面的對比，可以發現ROW level和statement level各有優勢，如能根據sql語句取捨可能會有更好地效能和效果；Mixed level便是以上兩種leve的結合。不過，新版本的MySQL對row level模式也被做了優化，並不是所有的修改都會以row level來記錄，像遇到表結構變更的時候就會以statement模式來記錄，如果sql語句確實就是update或者delete等修改資料的語句，那麼還是會記錄所有行的變更；因此，現在一般使用row level即可。
- 選取規則 如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄 如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都採用statement模式記錄
5.複製
複製是mysql最重要的功能之一，mysql叢集的高可用、負載均衡和讀寫分離都是基於複製來實現的；從5.6開始複製有兩種實現方式，基於binlog和基於GTID（全域性事務標示符）；本文接下來將介紹基於binlog的一主一從複製；其複製的基本過程如下：
a.Master將資料改變記錄到二進位制日誌(binary log)中
b.Slave上面的IO程式連線上Master，並請求從指定日誌檔案的指定位置（或者從最開始的日誌）之後的日誌內容
c.Master接收到來自Slave的IO程式的請求後，負責複製的IO程式會根據請求資訊讀取日誌指定位置之後的日誌資訊，返回給Slave的IO程式。
返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊已經到Master端的bin-log檔案的名稱以及bin-log的位置
d.Slave的IO程式接收到資訊後，將接收到的日誌內容依次新增到Slave端的relay-log檔案的最末端，並將讀取到的Master端的 bin-log的
檔名和位置記錄到master-info檔案中，以便在下一次讀取的時候能夠清楚的告訴Master從某個bin-log的哪個位置開始往後的日誌內容
e.Slave的Sql程式檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，並在自身執行接下來使用例項演示基於binlog的主從複製：
a.配置master
主要包括設定複製賬號，並授予REPLICATION SLAVE許可權，具體資訊會儲存在於master.info檔案中，及開啟binlog；
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
檢視master當前binlogmysql狀態：mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
建表插入資料：
CREATE TABLE `tb_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(36) NOT NULL,
`address` varchar(36) NOT NULL DEFAULT '',
`sex` varchar(12) NOT NULL DEFAULT 'Man' ,
`other` varchar(256) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
insert into tb_person set name="name1", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name2", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name3", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name4", address="beijing", sex="man", other="nothing";
b.配置slave
Slave的配置類似master，需額外設定relay_log引數，slave沒有必要開啟二進位制日誌，如果slave為其它slave的master，須設定bin_log
c.連線master
mysql> CHANGE MASTER TO
MASTER_HOST='10.108.111.14',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=120;
d.show slave status;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ---------------------------- slave io狀態，表示還未啟動
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60 ------------------------- master當機或連線丟失從伺服器執行緒重新嘗試連線主伺服器之前睡眠時間
Master_Log_File: mysql-bin.000003 ------------ 當前讀取master binlog檔案
Read_Master_Log_Pos: 120 ------------------------- slave讀取master binlog檔案位置
Relay_Log_File: relay-bin.000001 ------------ 回放binlog
Relay_Log_Pos: 4 -------------------------- 回放relay log位置
Relay_Master_Log_File: mysql-bin.000003 ------------ 回放log對應maser binlog檔案
Slave_IO_Running: No
Slave_SQL_Running: No
Exec_Master_Log_Pos: 0 --------------------------- 相對於master從庫的sql執行緒執行到的位置
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running為NO說明slave還沒有開始複製過程。
e.啟動複製
start slave
f.再次觀察slave狀態
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event -- 等待master新的event
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3469 ---------------------------- 3469 等於Exec_Master_Log_Pos，已完成回放
Relay_Log_File: relay-bin.000002 ||
Relay_Log_Pos: 1423 ||
Relay_Master_Log_File: mysql-bin.000003 ||
Slave_IO_Running: Yes ||
Slave_SQL_Running: Yes ||
Exec_Master_Log_Pos: 3469 -----------------------------3469 等於slave讀取master binlog位置，已完成回放
Seconds_Behind_Master: 0
可看到slave的I/O和SQL執行緒都已經開始執行，而且Seconds_Behind_Master=0。Relay_Log_Pos增加，意味著一些事件被獲取並執行了。
最後看下如何正確判斷SLAVE的延遲情況，判定slave是否追上master的binlog：
1、首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差異；
2、如果Relay_Master_Log_File 和 Master_Log_File 是一樣的話，再來看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差異，對比SQL執行緒比IO執行緒慢了多少個binlog事件；
3、如果Relay_Master_Log_File 和 Master_Log_File 不一樣，那說明延遲可能較大，需要從MASTER上取得binlog status，判斷當前的binlog和MASTER上的差距；
4、如果以上都不能發現問題，可使用pt_heartbeat工具來監控主備複製的延遲。
g.查詢slave資料，主從一致
mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name | address | sex | other |
+----+-------+---------+-----+---------+
| 5 | name4 | beijing | man | nothing |
| 6 | name2 | beijing | man | nothing |
| 7 | name1 | beijing | man | nothing |
| 8 | name3 | beijing | man | nothing |
+----+-------+---------+-----+---------+
關於mysql複製的內容還有很多，比如不同的同步方式、複製格式情況下有什麼區別，有什麼特點，應該在什麼情況下使用....這裡不再一一介紹。6.恢復
恢復是binlog的兩大主要作用之一，接下來通過例項演示如何利用binlog恢復資料：
a.首先，看下當前binlog位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1847 | | | |
+------------------+----------+--------------+------------------+-------------------+
b.向表tb_person中插入兩條記錄：
insert into tb_person set name="person_1", address="beijing", sex="man", other="test-1";
insert into tb_person set name="person_2", address="beijing", sex="man", other="test-2";
c.記錄當前binlog位置：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 2585 | | | |
+------------------+----------+--------------+------------------+-------------------+
d.查詢資料
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.刪除一條: delete from tb_person where name ="person_2";
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
+----+----------+---------+-----+--------+
f. binlog恢復（指定pos點恢復/部分恢復）
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql
d.資料恢復完成
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
e.總結
恢復，就是讓mysql將儲存在binlog日誌中指定段落區間的sql語句逐個重新執行一次而已7.總結
本文簡要介紹binlog原理及其在恢復、複製中的使用方法；更多深入分析可參考mysql官方文件及相關部落格。
8.參考
- https://dev.mysql.com/doc/internals/en/binary-log-versions.html
- http://www.php.cn/mysql-tutorials-361643.html
- https://www.jianshu.com/p/c16686b35807
- https://www.cnblogs.com/jackluo/p/3336585.html
- http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html
此文已由作者授權騰訊雲+社群在各渠道釋出
獲取更多新鮮技術乾貨，可以關注我們騰訊雲技術社群-雲加社群官方號及知乎機構號
相關文章
- MySQL資料庫binlog解析神器-binlog2sql應用MySql資料庫
- MySQL：Redo & binlogMySql
- 實戰|教你用Python玩轉MysqlPythonMySql
- Mysql的binlog原理MySql
- MySQL Binlog 介紹MySql
- 教你自動恢復MySQL資料庫的日誌檔案(binlog)MySql資料庫
- 用canal監控binlog並實現mysql定製同步資料的功能MySql
- MySQL Binlog 增量同步工具 go-mysql-transfer 實現詳解MySqlGo
- MySQL 的日誌：binlogMySql
- Mysql的redolog和binlogMySql
- 基於 MySQL Binlog 的 Elasticsearch 資料同步實踐MySqlElasticsearch
- MySQL Binlog 技術原理和業務應用案例分析MySql
- Mysql-binlog日誌-TMySql
- MySql Binlog 說明 & Canal 整合MySql的更新異常說明 & MySql Binlog 常用命令彙總MySql
- MySQL Binlog 解析工具 Maxwell 詳解MySql
- 【MySQL】一、如何快速執行 binlogMySql
- MySQL binlog和redo的組提交MySql
- MySQL中binlog cache使用流程解惑MySql
- Mysql資料庫監聽binlogMySql資料庫
- MySQL8.0 binlog_row_metadataMySql
- 05、MySQL Case-MySQL binlog誤清除恢復MySql
- MySQL的binlog有啥用？誰寫的？在哪裡？怎麼配置MySql
- mysql閃回工具binlog2sqlMySql
- MySQL 透過 binlog 恢復資料MySql
- MySQL 通過 binlog 恢復資料MySql
- MySQL中binlog備份指令碼分享MySql指令碼
- mysql binlog檢視指定資料庫MySql資料庫
- MySQL 日誌系統 redo log、binlogMySql
- MySQL二進位制檔案（binlog）MySql
- 雲伺服器：MySQL -- 關閉 binlog伺服器MySql
- MySQL 正確刪除 binlog 日誌MySql
- mysql檢視binlog日誌詳解MySql
- Percona Toolkit 神器全攻略（實用類）
- MySQL binlog超過binlog_expire_logs_seconds閾值沒有刪除案例MySql
- MySQL閃回技術之binlog2sql恢復binlog中的SQLMySql
- 3分鐘部署mysql並開啟binlogMySql
- 利用binlog日誌恢復mysql資料MySql
- 【Mysql】如何透過binlog恢復資料MySql