背景
最近在研究的一個遺留項目的過程中,為了確定進行某個業務操作究竟修改了哪些資料庫表,我們決定查看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的開源工具,Google後我們就找到了 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影片教學》
以上是MySQL怎麼快速看原始SQL語句的詳細內容。更多資訊請關注PHP中文網其他相關文章!