Binlog詳解
binlog，即二進制日志，以二進制的格式記錄了對數(shù)據(jù)庫所做的修改，包含所有的DDL和DML語句（除查詢語句外).
binlog日志的開啟只需要在MySQL的my.cnf文件中配置如下變量：
log-bin=file_name
server_id=1
log-bin: 配置binlog日志文件名稱的基礎(chǔ)名，在這個變量的后面加上數(shù)字后綴作為文件名。
日志文件的存儲路徑默認是MySQL的data目錄，如果想自定義目錄，則給這個變量加上絕對路徑即可。
開啟binlog需要同時配置server_id，如果沒有配置無法啟動服務器。
通過一下命令可以查詢binlog日志是否開啟：
binlog日志的相關(guān)選項配置，可以參照MySQL官網(wǎng)了解詳情。
其中需要特別關(guān)注的幾項有：
binlog_format: 設(shè)置binlog日志的格式。分別有STATEMENT、ROW、MIXED三種可選，每種格式都有著不同優(yōu)缺點。
-
STATEMENT：日志記錄的是sql語句，該格式寫入日志量少，但是不夠精確，存在很多限制。 -
ROW：日志記錄的的行數(shù)據(jù)的變化，該格式寫入日志量大，精確且靈活，恢復數(shù)據(jù)的時間會更長。 -
MIXED：以上兩種的混合，根據(jù)sql語句選擇不同的記錄格式。
關(guān)于不同格式優(yōu)缺點可以查看官網(wǎng)關(guān)于SBR與RBR的對比說明。
expire_logs_days：binlog日志自動過期時間，到期刪除。
max_binlog_size：單個binlog日志文件的最大值，超過該值會重新生成一個新文件。
binlog是以事件形式進行記錄。使用如下命令可以查看到binlog中寫入的事件：
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
根據(jù)事件所在的Log，開始位置Pos，結(jié)束位置End_log_pos，我們可以解析當前事件所執(zhí)行的操作。
binlog在MySQL中的主要作用包括：
-
數(shù)據(jù)恢復：對數(shù)據(jù)庫的修改都會被記錄到binlog中，所以在數(shù)據(jù)庫發(fā)生故障或因誤刪導致數(shù)據(jù)丟失，可以通過回放binlog來進行恢復。 -
主從復制：MySQL的主從模式中，從庫通過讀取主庫的binlog文件獲取數(shù)據(jù)變更，再在從庫上進行操作，達到主從一致。 -
審計與監(jiān)控：分析binlog文件，可以了解對數(shù)據(jù)庫的訪問情況，查找異常的更新和刪除操作。在生產(chǎn)中，在沒有做好操作日志落地的情況下，該功能很有用。
Binlog解析工具
binlog日志以二進制格式存儲，為了方面閱讀，需要使用相應的binlog解析工具將其解析成文本格式。
我常用的工具有兩種，一種是MySQL自帶的解析工具mysqlbinlog，一種是開源工具binlog2sql。
mysqlbinlog
mysqlbinlog的使用命令十分簡單：
mysqlbinlog [options] log_file ...
上述命令將mysql_bin_0160.000018日志文件所包含的事件全部輸出為文本格式。
在一般情況下，我們只需要相應日志文件中的部分日志，或者我們就不知道具體的日志位置。
這就需要我們在日志文件上進行相應的過濾篩選，mysqlbinlog也提供了多種選項供使用。完整詳細介紹可以見MySQL官網(wǎ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：不讀取默認選項。避免讀取my.cnf中默認的參數(shù)導致解析binlog失敗。
--base64-output：使用 base-64 編碼打印二進制日志條目;
--start-datetime：從大于等于該時間開始讀取事件日志；
--stop-datetime：讀取事件日志到大于等于該時間為止；
-vv: 即verbose，將日志重構(gòu)成sql輸出，兩個v輸出的sql會加上字段的注釋。區(qū)別如下圖：
我日常的業(yè)務場景是知道大概的時間范圍，查詢相關(guān)的更改操作。所以上述的命令選項剛好滿足。
根據(jù)不同的需求場景，需要選擇不同的選項，如日志回放需要注意事務id，知道事務的起止位置等。
當使用mysqlbinlog進行數(shù)據(jù)恢復時，只需要使用輸出管道將mysqlbinlog的輸出作為mysql輸入：
mysqlbinlog binlog_files | mysql -u root -p;
當我們需要修改binlog中的語句時，可以將mysqlbinlog的輸出重定向到文本文件中（通常binlog日志可能很大，推薦使用重定向到指定的目錄下的文件中），
在修改文件后作為輸入進行重放：
mysqlbinlog binlog_files > tmpfile
... edit tmpfile ...
mysql -u root -p < tmpfile
mysqlbinlog處理多個文件時，推薦使用如下的命令：
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
使用單個進程處理所有的binlog日志文件。
或者將所有binlog日志寫入到同一個文件后，在進行重放：
mysqlbinlog binlog.000001 > /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
binlog2sql
開源項目地址：https://github.com/danfengcao/binlog2sql
mysqlbinlog 只支持到database級別，在很多情況下，我們需要到table級別。
同時存在將日志中的sql轉(zhuǎn)換成回滾sql的的需求，mysqlbinlog不支持。
binlog2sql的主要功能就包括解析binlog日志（精確到table級別，sql類型級別）、生成回滾日志等。
安裝：
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服務端配置賬號，并賦予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
選項：
-
-K, --no-primary-key：對INSERT語句去除主鍵。可選。默認False。 -
-B, --flashback：生成回滾SQL。 -
--start-file/--stop-file: 起始/終止解析文件。 -
--start-position/--stop-position： 起始/終止解析位置。 -
--start-datetime/--stop-datetime： 起始/終止解析時間，格式'%Y-%m-%d %H:%M:%S'。 -
-d, --databases： 只解析目標db的sql，多個庫用空格隔開。 -
-t, --tables： 只解析目標table的sql，多張表用空格隔開。 -
--only-dml： 只解析dml，忽略ddl -
--sql-type： 只解析指定類型，支持INSERT, UPDATE, DELETE。多個類型用空格隔開。
