MySQL Binlog解析方法对比
一、准备测试数据
- 学生表student(Sno,Sname,Ssex)
- 教师表teacher(Tno,Tname)
- 成绩表sc(Sno,Cno,score)
- 选修课程表 course(Cno,Cname,Tno)
# 创建表结构
create database starcto;
use starcto;
CREATE TABLE student
(
sno INT,
sname VARCHAR(32),
ssex VARCHAR(8),
primary key (sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE teacher
(
tno INT,
tname VARCHAR(32),
primary key (tno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE course
(
cno INT,
cname VARCHAR(32),
tno INT,
primary key (cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE sc(
sno INT,
cno INT,
score INT,
primary key (sno,cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 插入测试数据
insert into student values ("1001,'牛一','男'","1002,'蔡二','女'","1003,'张三','男'");
insert into student values ("1004,'李四','女'","1005,'王五','男'","1006,'李志基','男'");
insert into student values ("1007,'陈李强','男'","1008,'王八','女'","1009,'张仲景','男'");
insert into teacher values ("1001,'叶平'","1002,'李金洋'","1003,'易法令'","1004,'李闵'","1005,'陈国华'");
insert into course values ("001,'高等数学',1001","002,'马克思',1001","003,'大学英语',1002");
insert into course values ("004,'数据库',1002","005,'数据结构',1003");
insert into sc values ("1001,001,100","1001,002,80","1001,003,70","1001,004,60","1001,005,50");
insert into sc values ("1002,001,60","1002,002,50","1002,003,80","1002,004,30","1002,005,100");
insert into sc values (1003,001,60);
insert into sc values (1003,002,80);
insert into sc values (1003,003,60);
insert into sc values (1003,004,40);
insert into sc values (1004,001,20);
insert into sc values (1004,002,100);
insert into sc values (1004,003,30);
insert into sc values (1004,005,40);
insert into sc values (1005,002,80);
insert into sc values (1005,003,60);
insert into sc values (1005,004,50);
insert into sc values (1006,002,100);
insert into sc values (1006,005,100);
insert into sc values (1007,001,50);
insert into sc values (1007,004,40);
insert into sc values (1008,003,60);
二、解析binlog文件
mysql>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 2947794 |
| mysql-bin.000003 | 13607 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
2.1 不加-v -vv --base64-output
mysqlbinlog --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
2.2 -v -vv --base64-output单独使用
-v
# 会显示dml语句具体做了什么操作,例如:insert语句,会显示出insert具体插入了什么数据;
# 但是看不到完整的dml语句,例如:insert into t1(id) values(1);
# 但还是会看到dml语句的"伪"sql语句,也看不懂;
mysqlbinlog -v --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
-vv
# 会显示dml语句具体做了什么操作,例如:insert语句,会显示insert具体插入了什么数据;
# 且可以看到完整的dml语句,例如:insert into t1(id) values(1);
# 但还是会看到dml语句的"伪"sql语句
mysqlbinlog -vv --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
--base64-output=decode-rows
# 看不到dml语句的"伪" SQL语句
# 看不到dml语句具体操作了什么数据
mysqlbinlog --base64-output=decode-rows --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
2.3 -v -vv --base64-output结合使用
-v --base64-output=decode-rows
# 看得到dml语句具体做了什么操作(例如:insert时具体插入了什么数据)
# 看不到dml语句的完整sql语句(例如:insert into t1(id) values(1);)
# 看不到dml语句的"伪"sql语句
mysqlbinlog -v --base64-output=decode-rows --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
-vv --base64-output=decode-rows
# 看得到dml语句具体做了什么操作(例如:insert时具体插入了什么数据)
# 看得到dml语句的完整sql语句(例如:insert into t1(id) values(1);),但是被注释掉了的;
# 看不到dml语句的"伪"sql语句
mysqlbinlog -vv --base64-output=decode-rows --start-position=1802 --stop-position=1978 /data/docker-mysql/data/mysql-bin.000003
作者:UStarGao
链接:https://www.starcto.com/application_of_operational/229.html
来源:STARCTO
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-04-17Linux修改系统时区
- 2021-03-04MySQL Explain执行计划输出字段解读
- 2021-08-09MongoDB副本集搭建教程
- 2023-07-04MySQL mysqldump备份之--set-gtid-purged=OFF参数影响
- 2021-03-11MySQL数据的导入导出/备份恢复