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新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面