背景
最近在研究的一個遺留項目的過程中，為了確定進行某個業務操作究竟修改了哪些數據庫表，我們決定查看binlog中記錄的sql語句。一直知道mysql的binlog解析工具很多，但是原來沒有用過，今天把這個過程記錄一下。
這一次我們除了使用官方提供的解析工具之外，還使用了一個第三方的開源工具。
準備工作
啟用binlog
首先要確定MySQL服務器啟用了binlog，這個是在my.cnf文件中進行配置的。
cat?/etc/my.cnf #?取消log_bin的注釋即可，這里可以提供一個具體的路徑，否則就使用默認地址 log_bin #?高版本MySQL需要server-id這個參數，提供一個集群中不重復的id值即可 server-id=1 #?重新啟動服務器 service?mysqld?restart
記錄當前log位置
為了方便后續測試，我們先記錄下MySQL日志的一些相關信息。
— 確認日志已經正確啟用，這里也可以查看日志文件的具體路徑
mysql>?show?variables?like?'%log_bin%'; +---------------------------------+---------------------------------+ |?Variable_name???????????????????|?Value???????????????????????????| +---------------------------------+---------------------------------+ |?log_bin?????????????????????????|?ON??????????????????????????????| |?log_bin_basename????????????????|?/var/lib/mysql/mysqld-bin???????| |?log_bin_index???????????????????|?/var/lib/mysql/mysqld-bin.index?| |?log_bin_trust_function_creators?|?OFF?????????????????????????????| |?log_bin_use_v1_row_events???????|?OFF?????????????????????????????| |?sql_log_bin?????????????????????|?ON??????????????????????????????| +---------------------------------+---------------------------------+
— 記錄下當前日志的文件名和偏移位置，在后續查看日志過程中可以準確定位
mysql>?show?master?status; +-------------------+----------+--------------+------------------+-------------------+ |?File??????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?| +-------------------+----------+--------------+------------------+-------------------+ |?mysqld-bin.000001?|?????2425?|??????????????|??????????????????|???????????????????| +-------------------+----------+--------------+------------------+-------------------+
— 也可以通過指定起始時間來查看日志，所以也記錄一下當前時間
mysql>?select?now(); +---------------------+ |?now()???????????????| +---------------------+ |?2018-08-02?09:59:43?| +---------------------+
執行DDL
在測試庫中執行下列語句，最后我們的目的就是通過查看binlog來復原這部分語句。
--?建表 create?table?aaa(id?int,?title?varchar(100)); --?插入初始化數據 insert?into?aaa(id,?title)?values?(1,?'測試1'); insert?into?aaa(id,?title)?values?(2,?'測試2'); insert?into?aaa(id,?title)?values?(3,?'測試3'); --?更新/刪除 update?aaa?set?title='修改'?where?id=2; delete?from?aaa?where?id=1;
查看binlog
官方工具mysqlbinlog
官方提供的解析工具，使用很簡單，但是解析出來的結果可讀性不好。我們在調用過程中使用了 no-defaults 參數，是為了避免出現 unknown variable ‘default-character-set=utf8’ 錯誤。
mysqlbinlog?--no-defaults??/var/lib/mysql/mysqld-bin.000001?--start-position=2425
mysqlbinlog的顯示結果可讀性差，這里節選一個片段，可以看到除了我們執行的DDL語句被記錄之外，其他的上下文信息也都被記錄下來了。
#?at?2425 #180802?10:05:32?server?id?1??end_log_pos?2553?CRC32?0x77e80f22?Querythread_id=70exec_time=0error_code=0 use?`aaaa`/*!*/; SET?TIMESTAMP=1533175532/*!*/; SET?@@session.pseudo_thread_id=70/*!*/; SET?@@session.foreign_key_checks=1,?@@session.sql_auto_is_null=0,?@@session.unique_checks=1,?@@session.autocommit=1/*!*/; SET?@@session.sql_mode=1075838976/*!*/; SET?@@session.auto_increment_increment=1,?@@session.auto_increment_offset=1/*!*/; /*!C?utf8mb4?*//*!*/; SET?@@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/; SET?@@session.lc_time_names=0/*!*/; SET?@@session.collation_database=DEFAULT/*!*/; --?建表 create?table?aaa(id?int,?title?varchar(100)) /*!*/; #?at?2553 #180802?10:05:32?server?id?1??end_log_pos?2632?CRC32?0x2bcb9bbd?Querythread_id=70exec_time=0error_code=0 SET?TIMESTAMP=1533175532/*!*/; BEGIN /*!*/; #?at?2632 #180802?10:05:32?server?id?1??end_log_pos?2779?CRC32?0x8414086d?Querythread_id=70exec_time=0error_code=0 SET?TIMESTAMP=1533175532/*!*/; --?插入初始化數據 insert?into?aaa(id,?title)?values?(1,?'測試1') /*!*/;
第三方工具 binlog2sql
有非常多針對binlog的開源工具，谷歌后我們就找到了 binlog2sql。它提供的功能比官方多，根據不同選項，你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。我們這次僅用來查看執行的DDL，更多高級用法請參考官方中文文檔。
這個工具是基于python開發的(2.7+,3.4+)，所以根據手冊先安裝相關依賴
git?clone?https://github.com/danfengcao/binlog2sql.git?&&?cd?binlog2sql pip?install?-r?requirements.txt
使用方法和官方工具類似，提供偏移位置或者起始時間來進行準確定位。如果不提供定位信息，就會從頭開始顯示所有信息。
#?使用偏移位置 python?binlog2sql.py?-h127.0.0.1?-P3306?-uroot?-p'root'?--start-file='mysqld-bin.000001'?--start-pos=2425?-d?aaaa #?同樣功能，使用時間戳 python?binlog2sql.py?-h127.0.0.1?-P3306?-uroot?-p'root'?--start-file='mysqld-bin.000001'?--start-datetime='2018-08-02?10:00:00'?-d?aaaa
這個工具的顯示結果就很簡潔，如下
USE?aaaa; --?建表 create?table?aaa(id?int,?title?varchar(100)); USE?aaaa; --?插入初始化數據 insert?into?aaa(id,?title)?values?(1,?'測試1'); USE?aaaa; insert?into?aaa(id,?title)?values?(2,?'測試2'); USE?aaaa; insert?into?aaa(id,?title)?values?(3,?'測試3'); USE?aaaa; --?更新/刪除 update?aaa?set?title='修改'?where?id=2; USE?aaaa; delete?from?aaa?where?id=1;
推薦學習：《mysql視頻教程》
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
