Mysql的binlog日志与mysqlbinlog命令
binlog相关
mysql 的二进制日志 binlog 可以说是 mysql 最重要的日志，它记录了所有的 ddl 和 dml 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，mysql的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
binlog日志的两个最重要的使用场景
mysql主从复制：mysql replication在master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复：通过使用 mysqlbinlog工具来使恢复数据
总体的关于binlog的参数如下
sync_binlog = 1 log-bin = mysql-bin binlog_format = row expire_logs_days = 10 binlog_cache_size =4m max_binlog_cache_size =8m max_binlog_size =1024m
相关参数讲解
log-bin
决定了msyql 的binlog的名字，⽣成的binlog名字为mysql-bin.000001
binlog_format
规定binlog的格式， binlog有三种格式statement， row以及mixed，默认使⽤默认使⽤statement（5.7.7之前），建议使⽤row格式（5.7.7之后默认）
expire_logs_days
过期时间
binlog_do_db
此参数表示只记录指定数据库的⼆进制⽇志
binlog_ignore_db
此参数表示不记录指定的数据库的⼆进制⽇志
sync_binlog
在提交n次事务后，进⾏binlog的落盘， 0为不进⾏强⾏的刷新操作，⽽是由⽂件系统控制刷新⽇志⽂件
如果是在线交易和帐有关的数据建议设置成1, 如果是其它数据可以保持为０即可。
max_binlog_size
binlog⽂件的最⼤值，默认和最⼤是1gb，并不能严格限定⼆进制⽂件的⼤⼩
max_binlog_cache_size 表示的是binlog 能够使⽤的最⼤cache 内存⼤⼩
当我们执⾏多语句事务的时候 所有session的使⽤的内存超过max_binlog_cache_size的值时
就会报错： “multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”
mysqlbinlog格式
statment格式
特点：记录每⼀条数据的sql，将执⾏的每⼀条sql记录在binlog中，减少⽇志量，节省io,提⾼性能。
缺点：某些sql中的函数⽆法使⽤，⽐如sysdate()，在同步过程中会出现⽆法同步的问题。
mixed格式
特点：⼀般的语句使⽤sql语句来记录，遇到特殊的语句使⽤row格式来记录，保证数据的⼀致性和复制的准确性。
row格式
特点： binlog中仅仅记录哪⼀条记录被修改，不记录dml的sql语句，会详细记录每⼀行的更改细节，不会出现⽆法复制的问题。
缺点：因为要记录每⼀条修改记录的⽇志，所以⼤量占⽤磁盘io和⼤量使⽤硬盘空间。
注：将二进制日志格式设置为row时，有些更改仍然使用基于语句的格式，包括所有ddl语句，例如create table， alter table，或 drop table。
开启binlog
由于log_bin是个只读系统变量，不能动态的修改，只能再my.cnf里[mysqld]模块添加log-bin 配置，表示启用binlog，如果没有给定值，写成 log-bin=，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）
[mysqld] log-bin=mysql-bin
此方法需要重启数据库方能生效，如果想临时关掉binlog而又不想重启数据库，可以使用sql_log_bin，sql_log_bin 是一个动态变量，修改该变量时，可以只对当前会话生效（session），也可以是全局的（global），当全局修改这个变量时，只会对新的会话生效（这意味当对当前会话也不会生效），因此一般全局修改了这个变量后，都要把原来的所有连接 kill 掉，全局只在mariadb 5.5之前和mysql 5.6.22之前生效，后面的版本都已经不支持全局，因为太危险了。
用处：
当还原数据库的时候，如果不关闭二进制日志，那么你还原的过程仍然会记录在二进制日志里面，不仅浪费资源，那么增加了磁盘的容量，还没有必要（特别是利用二进制还原数据库的时候）所以一般还原的时候会选择关闭二进制日志，可以通过修改配置文件，重启关闭二进制日志。
也可以动态命令关闭sql_log_bin,然后导入数据库。
set sql_log_bin=0；# 设为0后，在当前会话上执行的语句都不记录binlog set sql_log_bin=1; # 取消限制
常用的binlog命令
# 是否启用binlog日志 show variables like 'log_bin'; # 查看详细的日志配置信息 show global variables like '%log%'; # 查看binlog的目录 show global variables like "%log_bin%"; # 查看当前服务器使用的biglog文件及大小 show binary logs; # 查看最新一个binlog日志文件名称和position show master status; # 清除所有的binlog⽂件，并且重置为⼀个 reset master
清理binlog日志
自动清理：
通过binlog参数（expire_logs_days ）来实现mysql⾃动删除binlog
show binary logs; show variables like 'expire_logs_days'; set global expire_logs_days=3; # 过期删除，单位是天
手动清理：
1、使⽤reset master 重置binlog⽂件（#reset master后，会造成slave⽆法找到master的严重后果）
2、直接rm本地删除binlog
# 查看正在使用的binlog show master status;
直接使⽤rm 命令删除不是当前使⽤的binlog(切记不要删除正在使⽤的binlog)
mysqlbinlog命令
root@localhost localhost 15:23:43 (none)>show global variables like "%log_bin%"; +---------------------------------+-----------------------------+ | variable_name | value | +---------------------------------+-----------------------------+ | log_bin | on | | log_bin_basename | /data/mysql/mysql-bin | | log_bin_index | /data/mysql/mysql-bin.index | | log_bin_trust_function_creators | on | | log_bin_use_v1_row_events | off | +---------------------------------+-----------------------------+ 5 rows in set (0.00 sec) root@localhost localhost 15:29:21 test1>show global variables like "binlog_format"; +---------------+-------+ | variable_name | value | +---------------+-------+ | binlog_format | row | +---------------+-------+ 1 row in set (0.01 sec) root@localhost localhost 15:25:06 abc>create database test1; query ok, 1 row affected (0.00 sec) root@localhost localhost 15:25:28 abc>use test1; database changed root@localhost localhost 15:25:34 test1>create table `abc` ( -> `id` int(11) not null auto_increment, -> `c1` int(11) default null, -> primary key (`id`), -> key `shouji` (`c1`) using btree -> ) engine=innodb auto_increment=1 default charset=utf8; query ok, 0 rows affected (0.01 sec) root@localhost localhost 15:26:12 test1>insert into abc(c1) values (1); query ok, 1 row affected (0.13 sec) root@localhost localhost 15:26:23 test1>insert into abc(c1) values (2),(3); query ok, 2 rows affected (0.02 sec) records: 2 duplicates: 0 warnings: 0 root@localhost localhost 15:26:43 test1>delete from abc where id=2; query ok, 1 row affected (0.00 sec) root@localhost localhost 15:26:58 test1>select * from abc; +----+------+ | id | c1 | +----+------+ | 1 | 1 | | 3 | 3 | +----+------+ 2 rows in set (0.00 sec)
root@localhost localhost 15:47:38 test1>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1361 | | | 0f943fa6-3117-11ea-a98d-005056b351ef:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
root@localhost localhost 15:48:27 test1>show binlog events in 'mysql-bin.000001'\g;
*************************** 1. row ***************************
log_name: mysql-bin.000001
pos: 4
event_type: format_desc
server_id: 1007231
end_log_pos: 123
info: server ver: 5.7.19-log, binlog ver: 4
*************************** 2. row ***************************
log_name: mysql-bin.000001
pos: 123
event_type: previous_gtids
server_id: 1007231
end_log_pos: 150
info:
*************************** 3. row ***************************
log_name: mysql-bin.000001
pos: 150
event_type: gtid
server_id: 1007231
end_log_pos: 211
info: set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:1'
*************************** 4. row ***************************
log_name: mysql-bin.000001
pos: 211
event_type: query
server_id: 1007231
end_log_pos: 304
info: create database test1
*************************** 5. row ***************************
log_name: mysql-bin.000001
pos: 304
event_type: gtid
server_id: 1007231
end_log_pos: 365
info: set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:2'
*************************** 6. row ***************************
log_name: mysql-bin.000001
pos: 365
event_type: query
server_id: 1007231
end_log_pos: 629
info: use `test1`; create table `abc` (
`id` int(11) not null auto_increment,
`c1` int(11) default null,
primary key (`id`),
key `shouji` (`c1`) using btree
) engine=innodb auto_increment=1 default charset=utf8
*************************** 7. row ***************************
log_name: mysql-bin.000001
pos: 629
event_type: gtid
server_id: 1007231
end_log_pos: 690
info: set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:3'
*************************** 8. row ***************************
log_name: mysql-bin.000001
pos: 690
event_type: query
server_id: 1007231
end_log_pos: 759
info: begin
*************************** 9. row ***************************
log_name: mysql-bin.000001
pos: 759
event_type: table_map
server_id: 1007231
end_log_pos: 803
info: table_id: 220 (test1.abc)
*************************** 10. row ***************************
log_name: mysql-bin.000001
pos: 803
event_type: write_rows
server_id: 1007231
end_log_pos: 843
info: table_id: 220 flags: stmt_end_f
*************************** 11. row ***************************
log_name: mysql-bin.000001
pos: 843
event_type: xid
server_id: 1007231
end_log_pos: 870
info: commit /* xid=52 */
*************************** 12. row ***************************
log_name: mysql-bin.000001
pos: 870
event_type: gtid
server_id: 1007231
end_log_pos: 931
info: set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:4'
*************************** 13. row ***************************
log_name: mysql-bin.000001
pos: 931
event_type: query
server_id: 1007231
end_log_pos: 1000
info: begin
*************************** 14. row ***************************
log_name: mysql-bin.000001
pos: 1000
event_type: table_map
server_id: 1007231
end_log_pos: 1044
info: table_id: 220 (test1.abc)
*************************** 15. row ***************************
log_name: mysql-bin.000001
pos: 1044
event_type: write_rows
server_id: 1007231
end_log_pos: 1093
info: table_id: 220 flags: stmt_end_f
*************************** 16. row ***************************
log_name: mysql-bin.000001
pos: 1093
event_type: xid
server_id: 1007231
end_log_pos: 1120
info: commit /* xid=53 */
*************************** 17. row ***************************
log_name: mysql-bin.000001
pos: 1120
event_type: gtid
server_id: 1007231
end_log_pos: 1181
info: set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:5'
*************************** 18. row ***************************
log_name: mysql-bin.000001
pos: 1181
event_type: query
server_id: 1007231
end_log_pos: 1250
info: begin
*************************** 19. row ***************************
log_name: mysql-bin.000001
pos: 1250
event_type: table_map
server_id: 1007231
end_log_pos: 1294
info: table_id: 220 (test1.abc)
*************************** 20. row ***************************
log_name: mysql-bin.000001
pos: 1294
event_type: delete_rows
server_id: 1007231
end_log_pos: 1334
info: table_id: 220 flags: stmt_end_f
*************************** 21. row ***************************
log_name: mysql-bin.000001
pos: 1334
event_type: xid
server_id: 1007231
end_log_pos: 1361
info: commit /* xid=54 */
21 rows in set (0.00 sec)
下面是mysqlbinlog命令的参数，摘自官方文档
| 选项名称 | 描述 | 引用 | 弃用 |
|---|---|---|---|
| 使用base-64编码打印二进制日志条目 | |||
| 使用指定的网络接口连接到mysql server | |||
| 二进制日志最大事件大小 | |||
| 字符集的安装目录 | |||
| 用于测试和调试。有关适用的默认值和其他详细信息，请参见文本 | 5.7.5 | ||
| 仅列出该数据库的条目 | |||
| 编写调试日志 | |||
| 程序退出时打印调试信息 | |||
| 程序退出时打印调试信息，内存和cpu统计信息 | |||
| 身份验证插件使用 | |||
| 除了通常的选项文件，还读取命名的选项文件 | |||
| 只读命名的选项文件 | |||
| 选项组后缀值 | |||
| 禁用二进制日志记录 | |||
| 不要显示提供的gtid集中的任何组 | |||
| 读取二进制日志文件，即使打开或未正确关闭 | |||
| 如果mysqlbinlog读取了无法识别的二进制日志事件，则会输出警告 | |||
| 从服务器请求rsa公钥 | 5.7.23 | ||
| 显示帮助信息并退出 | |||
| 显示登录注释的十六进制转储 | |||
| mysql服务器所在的主机 | |||
| 仅在处理来自此会话的二进制日志更新时，导致服务器使用幂等模式 | 5.7.0 | ||
| 仅显示提供的gtid集中的组 | |||
| 在指定目录中为load data准备本地临时文件 | |||
| 从.mylogin.cnf中读取登录路径选项 | |||
| 不读取选项文件 | |||
| 跳过日志中的前n个条目 | |||
| 指定要保留的打开文件描述符的数量 | |||
| 连接服务器时使用的密码 | |||
| 安装插件的目录 | |||
| 用于连接的tcp / ip端口号 | |||
| 打印默认选项 | |||
| 使用的连接协议 | |||
| 将事件以原始（二进制）格式写入输出文件 | |||
| 从mysql主服务器读取二进制日志，而不是读取本地日志文件 | |||
| 从mysql服务器而不是本地日志文件中读取二进制日志 | |||
| 直接输出到命名文件 | |||
| 从基于行的格式编写的日志中播放时，为数据库创建重写规则。可多次使用 | 5.7.1 | ||
| 不要以旧（4.1之前）格式向服务器发送密码 | 5.7.4 | 5.7.5 | |
| 仅提取由具有给定服务器id的服务器创建的那些事件 | |||
| 告诉mysqlbinlog当mysqld编写的server-id-bits设置为小于最大值时，如何解释二进制日志中的服务器id；仅受mysql cluster版本的mysqlbinlog支持 | |||
| 包含rsa公钥的文件的路径名 | 5.7.23 | ||
| 在输出中添加set names charset_name语句 | |||
| 用于共享内存连接的共享内存的名称 | |||
| 仅显示日志中包含的语句 | |||
| 不要打印任何gtid。从包含gtid的二进制日志写入转储文件时使用此功能 | |||
| unix套接字文件或windows命名管道使用 | |||
| 启用连接加密 | 5.7.3 | ||
| 包含受信任的ssl证书颁发机构列表的文件 | 5.7.3 | ||
| 包含受信任的ssl证书颁发机构证书文件的目录 | 5.7.3 | ||
| 包含x.509证书的文件 | 5.7.3 | ||
| 连接加密的允许密码 | 5.7.3 | ||
| 包含证书吊销列表的文件 | |||
| 包含证书吊销列表文件的目录 | |||
| 包含x.509密钥的文件 | 5.7.3 | ||
| 与服务器连接的所需安全状态 | 5.7.11 | ||
| 根据服务器证书的公用名身份验证主机名 | 5.7.3 | ||
| 从第一个事件中读取时间戳等于或晚于datetime参数的二进制日志 | |||
| 从位置等于或大于参数的第一个事件读取二进制日志 | |||
| 在时间戳等于或大于datetime参数的第一个事件时停止读取二进制日志 | |||
| 读取最后一个二进制日志文件后保持与服务器的连接 | |||
| 连接到服务器时要报告的从服务器id | |||
| 在第一个事件中停止读取二进制日志，且位置等于或大于参数 | |||
| 允许的tls协议进行加密连接 | 5.7.10 | ||
| 不要在从mysql服务器请求的二进制日志的结尾处停止，而要继续打印到最后一个二进制日志的结尾 | |||
| 连接服务器时要使用的mysql用户名 | |||
| 将行事件重建为sql语句 | |||
| 验证二进制日志中的校验和 |
[root@localhost mysql]# mysqlbinlog mysql-bin.000001 >/data/tmp/mysqlbinlog.sql [root@localhost mysql]# cat /data/tmp/mysqlbinlog.sql /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; # at 4 #200107 15:45:15 server id 1007231 end_log_pos 123 start: binlog v 4, server v 5.7.19-log created 200107 15:45:15 at startup # warning: this binlog is either in use or was not closed properly. rollback/*!*/; binlog ' czcuxg9/xg8adwaaahsaaaabaaqans43lje5lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaalnxreezgnaagaegaebaqeegaaxwaegggaaaaicagcaaaacgokkioaejqa aejxkj0= '/*!*/; # at 123 #200107 15:45:15 server id 1007231 end_log_pos 150 previous-gtids # [empty] # at 150 #200107 15:45:53 server id 1007231 end_log_pos 211 gtid last_committed=0 sequence_number=1 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:1'/*!*/; # at 211 #200107 15:45:53 server id 1007231 end_log_pos 304 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383153/*!*/; set @@session.pseudo_thread_id=3/*!*/; set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; set @@session.sql_mode=1344274432/*!*/; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\c utf8mb4 *//*!*/; set @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/; set @@session.lc_time_names=0/*!*/; set @@session.collation_database=default/*!*/; create database test1 /*!*/; # at 304 #200107 15:46:38 server id 1007231 end_log_pos 365 gtid last_committed=1 sequence_number=2 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:2'/*!*/; # at 365 #200107 15:46:38 server id 1007231 end_log_pos 629 query thread_id=3 exec_time=0 error_code=0 use `test1`/*!*/; set timestamp=1578383198/*!*/; create table `abc` ( `id` int(11) not null auto_increment, `c1` int(11) default null, primary key (`id`), key `shouji` (`c1`) using btree ) engine=innodb auto_increment=1 default charset=utf8 /*!*/; # at 629 #200107 15:47:15 server id 1007231 end_log_pos 690 gtid last_committed=2 sequence_number=3 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:3'/*!*/; # at 690 #200107 15:47:15 server id 1007231 end_log_pos 759 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383235/*!*/; begin /*!*/; # at 759 #200107 15:47:15 server id 1007231 end_log_pos 803 table_map: `test1`.`abc` mapped to number 220 # at 803 #200107 15:47:15 server id 1007231 end_log_pos 843 write_rows: table id 220 flags: stmt_end_f binlog ' gzcuxhn/xg8alaaaacmdaaaaanwaaaaaaaeabxrlc3qxaanhymmaagmdaai= gzcuxh5/xg8akaaaaesdaaaaanwaaaaaaaeaagac//wbaaaaaqaaaa== '/*!*/; # at 843 #200107 15:47:15 server id 1007231 end_log_pos 870 xid = 52 commit/*!*/; # at 870 #200107 15:47:23 server id 1007231 end_log_pos 931 gtid last_committed=3 sequence_number=4 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:4'/*!*/; # at 931 #200107 15:47:23 server id 1007231 end_log_pos 1000 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383243/*!*/; begin /*!*/; # at 1000 #200107 15:47:23 server id 1007231 end_log_pos 1044 table_map: `test1`.`abc` mapped to number 220 # at 1044 #200107 15:47:23 server id 1007231 end_log_pos 1093 write_rows: table id 220 flags: stmt_end_f binlog ' izcuxhn/xg8alaaaabqeaaaaanwaaaaaaaeabxrlc3qxaanhymmaagmdaai= izcuxh5/xg8amqaaaeueaaaaanwaaaaaaaeaagac//wcaaaaagaaapwdaaaaawaaaa== '/*!*/; # at 1093 #200107 15:47:23 server id 1007231 end_log_pos 1120 xid = 53 commit/*!*/; # at 1120 #200107 15:47:30 server id 1007231 end_log_pos 1181 gtid last_committed=4 sequence_number=5 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:5'/*!*/; # at 1181 #200107 15:47:30 server id 1007231 end_log_pos 1250 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383250/*!*/; begin /*!*/; # at 1250 #200107 15:47:30 server id 1007231 end_log_pos 1294 table_map: `test1`.`abc` mapped to number 220 # at 1294 #200107 15:47:30 server id 1007231 end_log_pos 1334 delete_rows: table id 220 flags: stmt_end_f binlog ' kjcuxhn/xg8alaaaaa4faaaaanwaaaaaaaeabxrlc3qxaanhymmaagmdaai= kjcuxib/xg8akaaaadyfaaaaanwaaaaaaaeaagac//wcaaaaagaaaa== '/*!*/; # at 1334 #200107 15:47:30 server id 1007231 end_log_pos 1361 xid = 54 commit/*!*/; set @@session.gtid_next= 'automatic' /* added by mysqlbinlog */ /*!*/; delimiter ; # end of log file /*!50003 set completion_type=@old_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/;
发现有很多base64编码的binlog语句，比如
binlog ' czcuxg9/xg8adwaaahsaaaabaaqans43lje5lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaalnxreezgnaagaegaebaqeegaaxwaegggaaaaicagcaaaacgokkioaejqa aejxkj0= '/*!*/;
这样的，这就需要加上--base64-output=decode-rows --verbose来解成一个sql语句。
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001 >/data/tmp/mysqlbinlog2.sql [root@localhost mysql]# cat /data/tmp/mysqlbinlog2.sql /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; # at 4 #200107 15:45:15 server id 1007231 end_log_pos 123 start: binlog v 4, server v 5.7.19-log created 200107 15:45:15 at startup # warning: this binlog is either in use or was not closed properly. rollback/*!*/; # at 123 #200107 15:45:15 server id 1007231 end_log_pos 150 previous-gtids # [empty] # at 150 #200107 15:45:53 server id 1007231 end_log_pos 211 gtid last_committed=0 sequence_number=1 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:1'/*!*/; # at 211 #200107 15:45:53 server id 1007231 end_log_pos 304 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383153/*!*/; set @@session.pseudo_thread_id=3/*!*/; set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; set @@session.sql_mode=1344274432/*!*/; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\c utf8mb4 *//*!*/; set @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/; set @@session.lc_time_names=0/*!*/; set @@session.collation_database=default/*!*/; create database test1 /*!*/; # at 304 #200107 15:46:38 server id 1007231 end_log_pos 365 gtid last_committed=1 sequence_number=2 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:2'/*!*/; # at 365 #200107 15:46:38 server id 1007231 end_log_pos 629 query thread_id=3 exec_time=0 error_code=0 use `test1`/*!*/; set timestamp=1578383198/*!*/; create table `abc` ( `id` int(11) not null auto_increment, `c1` int(11) default null, primary key (`id`), key `shouji` (`c1`) using btree ) engine=innodb auto_increment=1 default charset=utf8 /*!*/; # at 629 #200107 15:47:15 server id 1007231 end_log_pos 690 gtid last_committed=2 sequence_number=3 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:3'/*!*/; # at 690 #200107 15:47:15 server id 1007231 end_log_pos 759 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383235/*!*/; begin /*!*/; # at 759 #200107 15:47:15 server id 1007231 end_log_pos 803 table_map: `test1`.`abc` mapped to number 220 # at 803 #200107 15:47:15 server id 1007231 end_log_pos 843 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=1 ### @2=1 # at 843 #200107 15:47:15 server id 1007231 end_log_pos 870 xid = 52 commit/*!*/; # at 870 #200107 15:47:23 server id 1007231 end_log_pos 931 gtid last_committed=3 sequence_number=4 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:4'/*!*/; # at 931 #200107 15:47:23 server id 1007231 end_log_pos 1000 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383243/*!*/; begin /*!*/; # at 1000 #200107 15:47:23 server id 1007231 end_log_pos 1044 table_map: `test1`.`abc` mapped to number 220 # at 1044 #200107 15:47:23 server id 1007231 end_log_pos 1093 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=2 ### @2=2 ### insert into `test1`.`abc` ### set ### @1=3 ### @2=3 # at 1093 #200107 15:47:23 server id 1007231 end_log_pos 1120 xid = 53 commit/*!*/; # at 1120 #200107 15:47:30 server id 1007231 end_log_pos 1181 gtid last_committed=4 sequence_number=5 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:5'/*!*/; # at 1181 #200107 15:47:30 server id 1007231 end_log_pos 1250 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383250/*!*/; begin /*!*/; # at 1250 #200107 15:47:30 server id 1007231 end_log_pos 1294 table_map: `test1`.`abc` mapped to number 220 # at 1294 #200107 15:47:30 server id 1007231 end_log_pos 1334 delete_rows: table id 220 flags: stmt_end_f ### delete from `test1`.`abc` ### where ### @1=2 ### @2=2 # at 1334 #200107 15:47:30 server id 1007231 end_log_pos 1361 xid = 54 commit/*!*/; set @@session.gtid_next= 'automatic' /* added by mysqlbinlog */ /*!*/; delimiter ; # end of log file /*!50003 set completion_type=@old_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/;
这样就能看出来具体的sql了。
还可以根据时间来筛选语句：
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -v --start-datetime="2020-01-07 15:47:14" --stop-datetime="2020-01-07 15:47:24" mysql-bin.000001 >/data/tmp/mysqlbinlog3.sql [root@localhost mysql]# cat /data/tmp/mysqlbinlog3.sql /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; # at 4 #200107 15:45:15 server id 1007231 end_log_pos 123 start: binlog v 4, server v 5.7.19-log created 200107 15:45:15 at startup # warning: this binlog is either in use or was not closed properly. rollback/*!*/; # at 629 #200107 15:47:15 server id 1007231 end_log_pos 690 gtid last_committed=2 sequence_number=3 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:3'/*!*/; # at 690 #200107 15:47:15 server id 1007231 end_log_pos 759 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383235/*!*/; set @@session.pseudo_thread_id=3/*!*/; set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; set @@session.sql_mode=1344274432/*!*/; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\c utf8mb4 *//*!*/; set @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/; set @@session.lc_time_names=0/*!*/; set @@session.collation_database=default/*!*/; begin /*!*/; # at 759 #200107 15:47:15 server id 1007231 end_log_pos 803 table_map: `test1`.`abc` mapped to number 220 # at 803 #200107 15:47:15 server id 1007231 end_log_pos 843 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=1 ### @2=1 # at 843 #200107 15:47:15 server id 1007231 end_log_pos 870 xid = 52 commit/*!*/; # at 870 #200107 15:47:23 server id 1007231 end_log_pos 931 gtid last_committed=3 sequence_number=4 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:4'/*!*/; # at 931 #200107 15:47:23 server id 1007231 end_log_pos 1000 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383243/*!*/; begin /*!*/; # at 1000 #200107 15:47:23 server id 1007231 end_log_pos 1044 table_map: `test1`.`abc` mapped to number 220 # at 1044 #200107 15:47:23 server id 1007231 end_log_pos 1093 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=2 ### @2=2 ### insert into `test1`.`abc` ### set ### @1=3 ### @2=3 # at 1093 #200107 15:47:23 server id 1007231 end_log_pos 1120 xid = 53 commit/*!*/; set @@session.gtid_next= 'automatic' /* added by mysqlbinlog */ /*!*/; delimiter ; # end of log file /*!50003 set completion_type=@old_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/;
根据位置筛选，比我我们上面用show binlog events in 'mysql-bin.000001'\g;看到的有条删除类型的语句
*************************** 20. row ***************************
log_name: mysql-bin.000001
pos: 1294
event_type: delete_rows
server_id: 1007231
end_log_pos: 1334
info: table_id: 220 flags: stmt_end_f
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -v --stop-position="1334" mysql-bin.000001 >/data/tmp/mysqlbinlog4.sql you have new mail in /var/spool/mail/root [root@localhost mysql]# cat /data/tmp/mysqlbinlog4.sql /*!50530 set @@session.pseudo_slave_mode=1*/; /*!50003 set @old_completion_type=@@completion_type,completion_type=0*/; delimiter /*!*/; # at 4 #200107 15:45:15 server id 1007231 end_log_pos 123 start: binlog v 4, server v 5.7.19-log created 200107 15:45:15 at startup # warning: this binlog is either in use or was not closed properly. rollback/*!*/; # at 123 #200107 15:45:15 server id 1007231 end_log_pos 150 previous-gtids # [empty] # at 150 #200107 15:45:53 server id 1007231 end_log_pos 211 gtid last_committed=0 sequence_number=1 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:1'/*!*/; # at 211 #200107 15:45:53 server id 1007231 end_log_pos 304 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383153/*!*/; set @@session.pseudo_thread_id=3/*!*/; set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; set @@session.sql_mode=1344274432/*!*/; set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\c utf8mb4 *//*!*/; set @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=224/*!*/; set @@session.lc_time_names=0/*!*/; set @@session.collation_database=default/*!*/; create database test1 /*!*/; # at 304 #200107 15:46:38 server id 1007231 end_log_pos 365 gtid last_committed=1 sequence_number=2 rbr_only=no set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:2'/*!*/; # at 365 #200107 15:46:38 server id 1007231 end_log_pos 629 query thread_id=3 exec_time=0 error_code=0 use `test1`/*!*/; set timestamp=1578383198/*!*/; create table `abc` ( `id` int(11) not null auto_increment, `c1` int(11) default null, primary key (`id`), key `shouji` (`c1`) using btree ) engine=innodb auto_increment=1 default charset=utf8 /*!*/; # at 629 #200107 15:47:15 server id 1007231 end_log_pos 690 gtid last_committed=2 sequence_number=3 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:3'/*!*/; # at 690 #200107 15:47:15 server id 1007231 end_log_pos 759 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383235/*!*/; begin /*!*/; # at 759 #200107 15:47:15 server id 1007231 end_log_pos 803 table_map: `test1`.`abc` mapped to number 220 # at 803 #200107 15:47:15 server id 1007231 end_log_pos 843 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=1 ### @2=1 # at 843 #200107 15:47:15 server id 1007231 end_log_pos 870 xid = 52 commit/*!*/; # at 870 #200107 15:47:23 server id 1007231 end_log_pos 931 gtid last_committed=3 sequence_number=4 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:4'/*!*/; # at 931 #200107 15:47:23 server id 1007231 end_log_pos 1000 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383243/*!*/; begin /*!*/; # at 1000 #200107 15:47:23 server id 1007231 end_log_pos 1044 table_map: `test1`.`abc` mapped to number 220 # at 1044 #200107 15:47:23 server id 1007231 end_log_pos 1093 write_rows: table id 220 flags: stmt_end_f ### insert into `test1`.`abc` ### set ### @1=2 ### @2=2 ### insert into `test1`.`abc` ### set ### @1=3 ### @2=3 # at 1093 #200107 15:47:23 server id 1007231 end_log_pos 1120 xid = 53 commit/*!*/; # at 1120 #200107 15:47:30 server id 1007231 end_log_pos 1181 gtid last_committed=4 sequence_number=5 rbr_only=yes /*!50718 set transaction isolation level read committed*//*!*/; set @@session.gtid_next= '0f943fa6-3117-11ea-a98d-005056b351ef:5'/*!*/; # at 1181 #200107 15:47:30 server id 1007231 end_log_pos 1250 query thread_id=3 exec_time=0 error_code=0 set timestamp=1578383250/*!*/; begin /*!*/; # at 1250 #200107 15:47:30 server id 1007231 end_log_pos 1294 table_map: `test1`.`abc` mapped to number 220 # at 1294 #200107 15:47:30 server id 1007231 end_log_pos 1334 delete_rows: table id 220 flags: stmt_end_f ### delete from `test1`.`abc` ### where ### @1=2 ### @2=2 rollback /* added by mysqlbinlog */ /*!*/; set @@session.gtid_next= 'automatic' /* added by mysqlbinlog */ /*!*/; delimiter ; # end of log file /*!50003 set completion_type=@old_completion_type*/; /*!50530 set @@session.pseudo_slave_mode=0*/;
其他选项可以根据需求添加使用。
上一篇： 组件与实例的关系