mysqlbinlog命令详解

1.1. 初步了解binlog

mysqlbinlog用于处理二进制的日志文件，如果想要查看这些日志文件的文本内容，就需要使用mysqlbinlog工具。

1.1.1. binglog简介

1、MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

　　a、DDL
　　----Data Definition Language 数据库定义语言 
　　主要的命令有create、alter、drop等，ddl主要是用在定义或改变表(table)的结构,数据类型，表之间的连接和约束等初始工作上，他们大多在建表时候使用。

　 b、DML
　 ----Data Manipulation Language 数据操纵语言
　主要命令是slect,update,insert,delete,就像它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

1.1.2. mysqlbinlog常见的选项

a、--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
b、--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
c、--start-position：从二进制日志中读取指定position 事件位置作为开始。
d、--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

1.1.3. binglog对性能的损耗

一般来说开启binlog日志大概会有1%的性能损耗。

1.1.4. binlog日志的使用场景

a、mysql主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
b、数据恢复：通过mysqlbinlog工具来恢复数据。

binlog日志包括两类文件：

1)、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。

2)、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

1.1.5. 常用的binlog日志操作命令

1.1.5.1. 查看所有binlog日志列表

show master logs;

1.1.5.2. 查看master状态

即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值。

show master status;

1.1.5.3. flush 刷新log日志

自此刻开始产生一个新编号的binlog日志文件;

flush logs;

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；

1.1.5.4. 重置（清空）所有binlog日志

注意：慎重操作

reset master;

1.1.6. 查看binlog日志内容

常用有两种方式：

1.1.6.1. 方法一、使用mysqlbinlog自带查看命令法

注意：

a、binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看。
b、binlog日志与数据库文件在同目录中。
c、在Mysql5.5以下版本使用mysqlbinlog命令时如果报错，就加上"--no-defaults"选项
d、使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析分析：


解释：
server id 1:数据库主机的服务号
end_log_pos 796 :sql结束时的pos节点
thread_id=11:线程号

也可根据时间点查看

mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"

1.1.6.2. 方法二、show binlog events

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）
b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
c、LIMIT【offset】：偏移量(不指定就是0)
d、row_count :查询总条数（不指定就是所有行）

上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！

a、查询第一个最早的binlog日志：

show binlog events\G;

b、指定查询mysql-bin.000002这个文件

show binlog events in 'mysql-bin.000002'\G;

c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：

show binlog events in 'mysql-bin.000002' from 624\G;

d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。

show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

1.1.7. 利用binlog日志恢复mysql数据

通过分析，造成库ops数据破坏的pos点区间是介于3064-3153之间（这是按照日志区间的pos节点算的），造成库ops1库破坏的pos区间是介于3218-3310之间，只要恢复到相应pos点之前就可以了。

a、恢复命令的语法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

eg:
mysqlbinlog --start-position=3153 --stop-position=3880 /application/mysql3306/mysql_data/mysql-bin.000002 | /application/mysql3307/bin/mysql -uroot -S /application/mysql3307/logs/mysql.sock -p123456 -v

b、常用参数选项解释：

--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=ops指定只恢复ops数据库(一台主机上往往有多个数据库，只限本地log日志)

c、不常用选项：

-u --user=name 连接到远程主机的用户名
-p --password[=name]连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server从某个Mysql服务器上读取binlog日志

d、小结：实际是将读出的binlog日志内容，通过管道符传递给myslq命令。这些命令，文件尽量写成绝对路径；

e、完全恢复（需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉）(此方法测试未通过)

所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

1.2. mysqlbinlog命令的语法

mysqlbinlog [options] logfile1 logfile2 ...

1.2.1. 常用命令

解析 binlog 排查问题

如果只是解析出来查看，可以加 --base64-output=decode-rows 不显示行格式的内容：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

解析指定 GTID 的事务

用来分析某个事务做了什么：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199

解析指定范围的 binlog

a. 时间范围

--start-datetime、--stop-datetime 解析出指定时间范围内的 binlog，这个只适合粗略的解析，不精准，因此不要用来回放 binlog。有个小技巧：如果只能确定大概的时间范围，而且不确定在哪个 binlog 中，可以直接解析多个 binlog。比如大概在 11:20-12:00 内做了个表删除操作，但这个时间内有多个 binlog，可以这样：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. 偏移量范围

--start-position、--stop-position 解析 binlog 指定偏移量范围内的 binlog。如果同时指定了 --start-position 和 --stop-position，并且是解析多个 binlog，则 --start-position 只对第一个 binlog 生效，--stop-position 只对最后一个 binlog 生效。

这个常用场景是：已经解析过一次 binlog 并取得目标事务的 起始 position 后，精确的解析这一段 binlog：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows  --start-position='537' --stop-position='945' mysql-bin.000204
# at 537           "起始位置是 GTID event 前的这个 position"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes 
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/; 
...
... 
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/; 
COMMIT /*!*/; 
# at 945          "结束位置是 COMMIT event 后的这个 position"

c. GTID 范围

--include-gtids、--exclude-gtids 详细看参数解释。

回放 binlog

• 回放一定不能加 --base64-output=decode-rows 参数，因为不会解析出行格式（这是binlog真正有效的部分）；

回放也可以用上面指定范围的参数；

-解析 binlog 回放到本实例，不需要修改 server id，但要注意 GTID 是否已存在；

-GTID 已经存在，回放不会报错，但也不会真正回放这些事务，可以通过 --skip-gtids 参数跳过 GTID 的限制；

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

参数解释

--no-defaults

可以避免 my.cnf 里配了 [client] 某些 mysqlbinlog 没有的参数导致 mysqlbinlog 失败

-v

不加，只显示行格式（即那一串字符串），无法得到伪 SQL ：

加 -v，从行格式中重建伪SQL(带注释)，不显示 binlog_rows_query_log_events 参数效果：

-vv

加 -vv，从行格式中重建伪SQL并添加字段数据类型的注释，可以显示 binlog_rows_query_log_events 参数效果：

--base64-output=decode-rows

不显示行格式，如果同时加 -v 参数，可以从行格式中解码为带注释的伪 SQL：

--skip-gtids

不保留 GTID 事件信息，这样回放 binlog 时会跟执行新事务一样，生成新的 GTID 。对比如下：

--include-gtids

只解析出指定的 GTID 的事务：

[root@localhost 3306]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows \
> --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037-614040' mysql-bin.000199 |grep GTID
#200807 17:32:17 server id 2  end_log_pos 194 CRC32 0xc840be04  Previous-GTIDs
#200807 17:32:17 server id 2  end_log_pos 3818435 CRC32 0x9fdea913  GTID    last_committed=3    sequence_number=5   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614037'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5726909 CRC32 0x51b51cc1  GTID    last_committed=4    sequence_number=6   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614038'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5727523 CRC32 0x758852f1  GTID    last_committed=6    sequence_number=7   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614039'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 7635997 CRC32 0x47c43f83  GTID    last_committed=6    sequence_number=8   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614040'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

--exclude-gtids

不解析指定的 GTID 的事务

1.3. mysqlbinlog的选项

-d, --database=name      仅显示指定数据库的转储内容。
-o, --offset=#           跳过前N行的日志条目。
-r, --result-file=name   将输入的文本格式的文件转储到指定的文件。
-s, --short-form         使用简单格式。
--set-charset=name       在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name    转储日志的起始时间。
--stop-datetime=name     转储日志的截止时间。
-j, --start-position=#   转储日志的起始位置。
--stop-position=#        转储日志的截止位置。
--rewrite-db=name        -- rewrite-db=' mydb1- >mydb2' I mysql ，将mydb1的二进制日志应用到mydb2中

1.4. 实例：

1.4.1. 跳过N个条目

 mysqlbinlog -o 10000 mysqld-bin.000001

1.4.2. 查看特定时间的条目

根据时间截取 --start-datetime   --stop-datetime

例：mysqlbinlog --stop-datetime="2017-08-16 15:00:00" mysqld-bin.000001