mysqlbinlog命令详解
1. 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
本文来自博客园，作者：数据库小白（专注），转载请注明原文链接：https://www.cnblogs.com/xulinforDB/p/17496178.html
