How to quickly view the original SQL statement in MySQL

Background

In the process of researching a legacy project recently, in order to determine which database tables were modified for a certain business operation, we decided to view the SQL statements recorded in the binlog. . I have always known that MySQL has many binlog parsing tools, but I have never used them before. Today I will record the process.

This time, in addition to using the official parsing tool, we also used a third-party open source tool.

Preparation

Enable binlog

First make sure that the MySQL server has binlog enabled. This is in the my.cnf file configured in.

cat /etc/my.cnf

# 取消log_bin的注释即可，这里可以提供一个具体的路径，否则就使用默认地址
log_bin
# 高版本MySQL需要server-id这个参数，提供一个集群中不重复的id值即可
server-id=1
# 重新启动服务器
service mysqld restart

Record the current log position

In order to facilitate subsequent testing, we first record some relevant information of the MySQL log.

-- Confirm that the log has been correctly enabled. You can also view the specific path of the log file here.

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                              |
+---------------------------------+---------------------------------+

-- Record the file name and offset position of the current log. You can view the log later. Accurate positioning

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000001 |     2425 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

-- You can also view the log by specifying the starting time, so also record the current time

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-08-02 09:59:43 |
+---------------------+

Execute DDL

The following statements are executed in the test library. Our final goal is to restore these statements by viewing the 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;

View binlog

Official tool mysqlbinlog

The official parsing tool is very simple to use, but the parsed The result is not very readable. We used the no-defaults parameter during the call to avoid unknown variable 'default-character-set=utf8' errors.

mysqlbinlog --no-defaults  /var/lib/mysql/mysqld-bin.000001 --start-position=2425

The display results of mysqlbinlog are not readable. Here is an excerpt. You can see that in addition to the DDL statements we executed are recorded, other contextual information is also recorded.

# 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')
/*!*/;

Third-party tools binlog2sql

There are many open source tools for binlog. We found binlog2sql after searching Google. It provides more functions than the official one. According to different options, you can get original SQL, rollback SQL, INSERT SQL with primary key removed, etc. This time we only use it to view the executed DDL. For more advanced usage, please refer to the official Chinese documentation.

This tool is developed based on Python (2.7, 3.4), so install the relevant dependencies first according to the manual

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

The usage method is similar to the official tool, providing offset position or starting time. Accurate positioning. If no location information is provided, all information will be displayed from the beginning.

# 使用偏移位置
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

The display result of this tool is very simple, as follows

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;

Recommended learning: "mysql video tutorial"

The above is the detailed content of How to quickly view the original SQL statement in MySQL. For more information, please follow other related articles on the PHP Chinese website!