Binlog詳解

binlog，即二進(jìn)制日志，以二進(jìn)制的格式記錄了對(duì)數(shù)據(jù)庫(kù)所做的修改，包含所有的DDL和DML語(yǔ)句（除查詢語(yǔ)句外).

binlog日志的開(kāi)啟只需要在MySQL的my.cnf文件中配置如下變量：

log-bin=file_name
server_id=1

log-bin: 配置binlog日志文件名稱的基礎(chǔ)名，在這個(gè)變量的后面加上數(shù)字后綴作為文件名。

日志文件的存儲(chǔ)路徑默認(rèn)是MySQL的data目錄，如果想自定義目錄，則給這個(gè)變量加上絕對(duì)路徑即可。

開(kāi)啟binlog需要同時(shí)配置server_id，如果沒(méi)有配置無(wú)法啟動(dòng)服務(wù)器。

通過(guò)一下命令可以查詢binlog日志是否開(kāi)啟：

binlog日志的相關(guān)選項(xiàng)配置，可以參照MySQL官網(wǎng)了解詳情。

其中需要特別關(guān)注的幾項(xiàng)有：

binlog_format: 設(shè)置binlog日志的格式。分別有STATEMENT、ROW、MIXED三種可選，每種格式都有著不同優(yōu)缺點(diǎn)。

• STATEMENT：日志記錄的是sql語(yǔ)句，該格式寫(xiě)入日志量少，但是不夠精確，存在很多限制。
• ROW：日志記錄的的行數(shù)據(jù)的變化，該格式寫(xiě)入日志量大，精確且靈活，恢復(fù)數(shù)據(jù)的時(shí)間會(huì)更長(zhǎng)。
• MIXED：以上兩種的混合，根據(jù)sql語(yǔ)句選擇不同的記錄格式。
  

關(guān)于不同格式優(yōu)缺點(diǎn)可以查看官網(wǎng)關(guān)于SBR與RBR的對(duì)比說(shuō)明。

expire_logs_days：binlog日志自動(dòng)過(guò)期時(shí)間，到期刪除。
max_binlog_size：?jiǎn)蝹€(gè)binlog日志文件的最大值，超過(guò)該值會(huì)重新生成一個(gè)新文件。

binlog是以事件形式進(jìn)行記錄。使用如下命令可以查看到binlog中寫(xiě)入的事件：

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

根據(jù)事件所在的Log，開(kāi)始位置Pos，結(jié)束位置End_log_pos，我們可以解析當(dāng)前事件所執(zhí)行的操作。

binlog在MySQL中的主要作用包括：

• 數(shù)據(jù)恢復(fù)：對(duì)數(shù)據(jù)庫(kù)的修改都會(huì)被記錄到binlog中，所以在數(shù)據(jù)庫(kù)發(fā)生故障或因誤刪導(dǎo)致數(shù)據(jù)丟失，可以通過(guò)回放binlog來(lái)進(jìn)行恢復(fù)。
• 主從復(fù)制：MySQL的主從模式中，從庫(kù)通過(guò)讀取主庫(kù)的binlog文件獲取數(shù)據(jù)變更，再在從庫(kù)上進(jìn)行操作，達(dá)到主從一致。
• 審計(jì)與監(jiān)控：分析binlog文件，可以了解對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)情況，查找異常的更新和刪除操作。在生產(chǎn)中，在沒(méi)有做好操作日志落地的情況下，該功能很有用。

Binlog解析工具

binlog日志以二進(jìn)制格式存儲(chǔ)，為了方面閱讀，需要使用相應(yīng)的binlog解析工具將其解析成文本格式。

我常用的工具有兩種，一種是MySQL自帶的解析工具mysqlbinlog，一種是開(kāi)源工具binlog2sql。

mysqlbinlog

mysqlbinlog的使用命令十分簡(jiǎn)單：

mysqlbinlog [options] log_file ...

上述命令將mysql_bin_0160.000018日志文件所包含的事件全部輸出為文本格式。

在一般情況下，我們只需要相應(yīng)日志文件中的部分日志，或者我們就不知道具體的日志位置。

這就需要我們?cè)谌罩疚募线M(jìn)行相應(yīng)的過(guò)濾篩選，mysqlbinlog也提供了多種選項(xiàng)供使用。完整詳細(xì)介紹可以見(jiàn)MySQL官網(wǎng)。

我常用的命令選項(xiàng)如下：

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv --start-datetime='your time' --stop-datetime="your time" --database="your database" log_file > temp.sql

--no-defaults：不讀取默認(rèn)選項(xiàng)。避免讀取my.cnf中默認(rèn)的參數(shù)導(dǎo)致解析binlog失敗。

--base64-output：使用 base-64 編碼打印二進(jìn)制日志條目;

--start-datetime：從大于等于該時(shí)間開(kāi)始讀取事件日志；

--stop-datetime：讀取事件日志到大于等于該時(shí)間為止；

-vv: 即verbose，將日志重構(gòu)成sql輸出，兩個(gè)v輸出的sql會(huì)加上字段的注釋。區(qū)別如下圖：

我日常的業(yè)務(wù)場(chǎng)景是知道大概的時(shí)間范圍，查詢相關(guān)的更改操作。所以上述的命令選項(xiàng)剛好滿足。

根據(jù)不同的需求場(chǎng)景，需要選擇不同的選項(xiàng)，如日志回放需要注意事務(wù)id，知道事務(wù)的起止位置等。

當(dāng)使用mysqlbinlog進(jìn)行數(shù)據(jù)恢復(fù)時(shí)，只需要使用輸出管道將mysqlbinlog的輸出作為mysql輸入：

mysqlbinlog binlog_files | mysql -u root -p;

當(dāng)我們需要修改binlog中的語(yǔ)句時(shí)，可以將mysqlbinlog的輸出重定向到文本文件中（通常binlog日志可能很大，推薦使用重定向到指定的目錄下的文件中），

在修改文件后作為輸入進(jìn)行重放：

mysqlbinlog binlog_files > tmpfile
... edit tmpfile ...
mysql -u root -p < tmpfile

mysqlbinlog處理多個(gè)文件時(shí)，推薦使用如下的命令：

mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

使用單個(gè)進(jìn)程處理所有的binlog日志文件。

或者將所有binlog日志寫(xiě)入到同一個(gè)文件后，在進(jìn)行重放：

mysqlbinlog binlog.000001 >  /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"

binlog2sql

開(kāi)源項(xiàng)目地址：https://github.com/danfengcao/binlog2sql

mysqlbinlog 只支持到database級(jí)別，在很多情況下，我們需要到table級(jí)別。

同時(shí)存在將日志中的sql轉(zhuǎn)換成回滾sql的的需求，mysqlbinlog不支持。

binlog2sql的主要功能就包括解析binlog日志（精確到table級(jí)別，sql類型級(jí)別）、生成回滾日志等。

安裝：

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

使用前置：

• MySQL配置需求：

  [mysqld]
  server_id = 1
  log_bin = /var/log/mysql/mysql-bin.log
  max_binlog_size = 1G
  binlog_format = row
  binlog_row_image = full
  

• MySQL權(quán)限需求：
  在MySQL服務(wù)端配置賬號(hào)，并賦予select, super/replication client, replication slave權(quán)限。

用法：

• 解析sql:

    python binlog2sql.py -hxxxxx -Pxxx -uxxxx -pxxxx -ddb_name -ttable_name --start-file='log_file' --stop-file='log_file' 
    --start-datetime="your time" --stop-datetime="your time" > tmp.sql

• 解析回滾sql：

    python binlog2sql.py -hxxxxx -Pxxx -uxxxx -pxxxx -ddb_name -ttable_name --flashback --start-file='log_file' 
    --stop-file='log_file' --start-datetime="your time" --stop-datetime="your time" > tmp.sql

選項(xiàng)：

• -K, --no-primary-key：對(duì)INSERT語(yǔ)句去除主鍵。可選。默認(rèn)False。
• -B, --flashback：生成回滾SQL。
• --start-file/--stop-file: 起始/終止解析文件。
• --start-position/--stop-position： 起始/終止解析位置。
• --start-datetime/--stop-datetime： 起始/終止解析時(shí)間，格式'%Y-%m-%d %H:%M:%S'。
• -d, --databases： 只解析目標(biāo)db的sql，多個(gè)庫(kù)用空格隔開(kāi)。
• -t, --tables： 只解析目標(biāo)table的sql，多張表用空格隔開(kāi)。
• --only-dml： 只解析dml，忽略ddl
• --sql-type： 只解析指定類型，支持INSERT, UPDATE, DELETE。多個(gè)類型用空格隔開(kāi)。