MySQL怎么快速查看原始SQL语句

藏色散人
发布: 2021-11-10 17:11:53
转载
2409 人浏览过

背景

最近在研究的一个遗留项目的过程中,为了确定进行某个业务操作究竟修改了哪些数据库表,我们决定查看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视频教程

以上是MySQL怎么快速查看原始SQL语句的详细内容。更多信息请关注PHP中文网其他相关文章!

相关标签:
来源:juejin.im
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!