MySQL_通過binlog檢視原始SQL語句

wait4friend發表於2018-08-02
MySql

背景

最近在研究的一個遺留專案的過程中，為了確定進行某個業務操作究竟修改了哪些資料庫表，我們決定檢視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 	Query	thread_id=70	exec_time=0	error_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 	Query	thread_id=70	exec_time=0	error_code=0
SET TIMESTAMP=1533175532/*!*/;
BEGIN
/*!*/;
# at 2632
#180802 10:05:32 server id 1  end_log_pos 2779 CRC32 0x8414086d 	Query	thread_id=70	exec_time=0	error_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;
複製程式碼

相關文章