BinLog日志

一、概述

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

DDL
----Data Definition Language 数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML
----Data Manipulation Language 数据操纵语言
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

最主要的使用场景有两个：
1.）主从复制中，使slave同步master的数据。
2.）数据恢复，定时全备与binlog恢复指定数据？

 

2.binlog图解

binlog日志中创建语句会被格式化显示；创建库是小写,创建表是大写。

 

3.相关命令：

查看所有binlog日志：show master logs;

刷新binlog日志：flush logs；

清空binlog日志：reset master;

 

二、根据binlog日志选择性恢复

在开启GTID的情况下用mysqlbinlog命令根据binlog日志恢复时需要添加“--skip-gtids=true”否则既不成功也不报错。而且使用这种恢复方式要求MySQL中不存在无法执行sql语句的环境。例如：在这个binlog日志的任职期间删除过上个binlog任职时创建的数据库，如此在恢复时会出现提示“找不到数据库的状态”而退出。不过我们可以利用binlog日志文件来选择性恢复：1.）指定恢复开始的与结束的时间点。2.）指定开始恢复与结束的pos点。

注意：binlog日志的格式row时，内容缺省是加密的，需要加参数“--base64-output=decode-rows -v”解析查看。

1.）基于时间点的恢复：

--start-datetime ：开始恢复点
--stop-datetime  ：结束恢复点
命令格式：
mysqlbinlog --skip-gtids=true --start-datetime="2017-11-14 17:10:57"  mysql-bin.000053 |mysql -uroot -p123456    #恢复指定时间点之后的数据
mysqlbinlog --skip-gtids=true --start-datetime="***"  --stop-datetime="***" mysql-bin.000056 |mysql -uroot -p123456   #恢复某个时间段的数据

2.）基于pos位置的恢复：

--start-position ：起始恢复点
--stop-position  ：结束恢复点
命令格式：
mysqlbinlog --skip-gtids=true --stop-position=1121 mysql-bin.000055 |mysql -uroot -p123456    #恢复某个pos之前的数据
mysqlbinlog --skip-gtids=true --start-position=*** --stop-position=*** mysql-bin.000056 |mysql -uroot -p123456   #恢复某段pos之间的数据

 

三、多个binlog日志恢复

如果没有备份的话，我们需要恢复数据时，需要从最开始的binlog逐个恢复。所我们要时常做好全备并刷新binlog，以便可以快速定位需要回复的数据。

在使用多个binlog log日志的还原最好将所有文件使用一个连接完成，如果使用不同连接的话有时会导致不安全，比如第一个日志创建了临时表，但是导入完成后临时表会被删除，这是第二个需要用到临时表的binlog日志就会因找不到表而报“unknown table”。所以建议使用如下方法恢复:

1.)所有二进制文件放在单个连接里:

mysqlbinlog   binlog.000001  binlog.000002  |   mysql   -u   root   -p

2.）将所有二进制文件写在一个文件里执行

mysqlbinlog    binlog.000001  >  /tmp/qk.sql  
mysqlbinlog    binlog.000002  >> /tmp/qk.sql
mysql  -u   root  -p -e  "source /tmp/qk.sql"  

 

四、案例：

binlog日志恢复报错：

ERROR 1049 (42000): Unknown database 'user'
ERROR 1046 (3D000): No database selected

 

原因：

这是因为binlog日志文件是在GTID模式下生成的，再查看binlog日志可以看到每个事务开始前，会执行“SET @@SESSION.GTID_NEXT=source_id:transaction_id”GTID事务，但是这些GTID已经执行过了，从Executed_Gtid_Set中可以看到。所以我们在执行解析的binlog文件时所有的事务都被忽略。（已经存在于Executed_Gtid_Set集合中的GTID会跳过）

解决：

1.）由于是GTID导致的，关闭该功能即可。也可以临时关闭GTID。
2.）在使用GTID时，想通过binlog日志来恢复数据，可以在mysqlbinlog解析日志时指定 “--skip-gtids=true”，这样的话解析出来的文件中就不会包含SET @@SESSION.GTID_NEXT。不过得保证mysqlbinlog版本为3.4及以上。

注意：

当未开启GTID是binlog日志中是这样的：SET @@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;，这时如果启用了GTID然后同步的话，会提示GTID已开始的错误:ERROR 1782 (HY000): @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
这时可重新加--skip-gtids=true解析下之前的binlog备份，然后再导入。或则临时关闭GTID。