一、二进制日志及其作用

定义：

MySQL的二进制日志记录的是所有使mysql数据库的数据发生变更的操作信息（事件），即记录用户对数据库执行更改的所有sql语句。

1、数据库的复制

配置了主从复制的时候，主服务器（Master）会将其产生的二进制日志发送到从服务器（Slave），从服务器会利用这个二进制日志的信息在本地重做，实现主从同步。

2、数据库的恢复

MySQL可以在全备和差异备份的基础上，利用二进制日志进行基于时间点或者事物Id的恢复操作，原理等同于主从复制的日志重做。

3、分析数据库发生的变更（审计）

基于二进制日志本身的特征，即记录数据库发生变更的操作，从而可以通过分析特定时间段的二进制日志，来分析某一时间内对数据库的操作，或者查询一些变更发生的时间，判断是否有对数据库进行注入的攻击。

二、二进制日志的配置

在MySQL配置文件my.cnf中[mysqld] 选项处添加二进制日志的配置参数：

log-bin=mysql-bin

重启MySQL服务生效，注意该变量是只读的，不能动态修改。

log_bin是生成的bin-log的文件名，未指定位置，默认为MySQL数据目录，文件后缀则是6位数字的编码，从000001开始。按照上面的配置，生成的文件为： mysql_bin.000001、mysql_bin.000002......

三、二进制日志相关的变量

1、常用binlog相关变量

查看部分二进制日志相关变量的命令：

show global variables like '%binlog%';

max_binlog_size：单个二进制日志文件的最大值，如果超过该值，则生成一个新的二进制日志文件，后缀名加1，并记录到.index文件。

binlog_cache_size：二进制日志缓存大小，当使用事务的表存储引擎（如InnoDB）时，所有未提交的二进制日志会提交到一个缓存中，当该事务提交时，直接将缓存中的二进制日志写入到二进制日志文件，需要主要的是该变量是基于会话（session）的，所有该值的设置需要谨慎。可以通过show global status命令查看binlog_cache_use、binlog_cache_disk_use的状态，来判断当前binlog_cache_size的设置是否合适，其中binlog_cache_use记录了使用缓存写入二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写入二进制日志的次数。

sync_binlog：二进制日志每写缓冲多少次就同步到磁盘，默认为0，表示使用操作系统的缓冲来写二进制日志；如果为1，表示采用同步写磁盘的方式来写二进制日志，该方式能提高数据库的高可用性，但是会对数据库的IO性能带来影响。

binlog_format：记录二进制日志的格式，非常重要，它是动态参数，可以在数据库运行环境下进行更改，但注意更改的时候可能会引起复制出现问题，需要谨慎操作并更改后观察复制是否正常。

主要有三种格式：

1）STATEMENT：二进制日志文件记录的是逻辑SQL语句

优点：在 STATEMENT 模式下，不需要记录每一行数据的变化，减少了 binlog 日志量，节省 I/O 以及存储资源，提高性能。

缺点：在 STATEMENT 模式下，不是所有的 UPDATE 语句都能被复制。目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id等。

2）ROW：二进制日志记录的是表的行更改情况

优点：在 ROW 模式下，binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录哪一条记录被修改了，修改成什么样了。所以 ROW 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解，而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题，为数据库的恢复和复制带来了更好的可靠性。

缺点：在 ROW 模式下，所有的执行的语句记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，尤其是当执行 alter table 之类的语句的时候，产生的日志量是惊人的。因为 MySQL 对于 alter table 之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表，那么该表的每一条记录的变更都会被记录到日志中。

3）MIXED：默认采用STATEMENT格式进行二进制日志文件的记录，在一些特定情况下会使用ROW格式，可能的情况有：

a.表的存储引擎为NDB；

b.使用的UUID()、USER()、CURRENT_USER()、NOW()等不确定的函数；

c.使用了用户自定义函数（UDF）;

d.使用了INSERT DELAY语句；

e.使用了临时表。

expire_logs_days：二进制日志的过期时间，超过该时间的日志文件会自动删除。

max_binlog_cache_size：二进制日志能够使用的最大cache内存大小。当执行多语句事务时，max_binlog_cache_size 如果不够大，系统可能会报出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

max_binlog_stmt_cache_size：针对非事务语句。

binlog_checksum ：用作复制的主从校检， NONE表示不生成checksum，CRC-32表示使用这个算法做校检。

log_bin_trust_function_creators：默认为OFF，这个参数开启会限制存储过程、函数以及触发器的创建。

四、二进制日志的管理

1、查看所有的日志文件

MariaDB [(none)]> show binary logs;

或者

MariaDB [(none)]> show master logs;

2、查看正在写入的日志文件

MariaDB [(none)]> show master status;

3、查看当前binlog文件内容

MariaDB [(none)]> show binlog events;

或者

MariaDB [(none)]> show binlog events in 'mysql-bin.xxxxxx';

或者

MariaDB [(none)]> show binlog events in 'mysql-bin.xxxxxx' from xxx;

Log_name：此条log存在哪个文件中

Pos：log在bin-log中的开始位置

Event_type：log的类型信息

Server_id：可以查看配置中的server_id,表示log是哪个服务器产生

End_log_pos：log在bin-log中的结束位置

Info：log的一些备注信息，可以直观的看出进行了什么操作

4、手动启用新的日志文件,一般备份完数据库后执行

MariaDB [(none)]> flush logs;

5、手动删除二进制日志

1）删除指定fileName之前的日志文件

purge binary logs to fileName;    

2）删除指定时间之前的文件

purge binary logs before '2017-12-31 23:59:59';    

3）删除指定日志

purge binary logs before date_sub( now( ), interval 7 day); 

或者

purge master logs before date_sub( now( ), interval 7 day); 

4）删除所有二进制日志，并重新开始记录。

MariaDB [(none)]> reset master;

6、二进制日志文件导出及查看

1）按时间点导出

mysqlbinlog --start-datetime="2018-01-01 00:00:00" --stop-datetime="2018-01-02 00:00:00" mysql-bin.000005 > /data/tmp/date_20180101.log 

2）按事件位置点导出

mysqlbinlog --start-position=1000  --stop-position=2000 mysql-bin.000005 > /data/tmp/posi_1000-2000.log 

7、恢复部分数据

强烈建议：做任何恢复之前都给数据库做一个完整备份，新建库进行恢复。

 binlog是记录着mysql所有事件的操作，可以通过binlog做完整恢复，基于时间点的恢复，以及基于位置点的恢复。

1）完整恢复，先执行上次完整备份恢复，再执行自上次备份后产生的二进制日志文件恢复。

mysqlbinlog mysql-bin.000005 | mysql -uroot -p

这样数据库就可以完全的恢复到崩溃前的完全状态。

2）基于时间点的恢复，如果确认误操作时间点为2018-01-20 10:00:00，执行如下

mysqlbinlog --stop-date='2018-01-20 9:59:59' mysql-bin.000005 | mysql -uroot -p

然后跳过误操作的时间点，继续执行后面的binlog。

mysqlbinlog --start-date='2018-01-20 10:01:00' mysql-bin.000005 | mysql -uroot -p

3）取两个时间点之间的事件

mysqlbinlog --start-datetime="2018-01-20 11:00:00" --stop-datetime="2018-01-20 12:00:00" mysql-bin.000001 | mysql -u root -p

4）基于位置点恢复

如果两个时间点之间可能涉及到的不只是误操作，也有可能有正确的操作也被跳过去了，那么可以执行位置点恢复。

通过查看日志文件信息，确认1122-1133为误操作点。

首先执行从1开始至1122之间的事件，不包括位置点为1122的事件，

mysqlbinlog --stop-position=1122 mysql-bin.000005 | mysql -uroot -p 

然后执行从1134开始的事件。

mysqlbinlog --start-position=1134 mysql-bin.000005 | mysql -uroot -p 

5）取两个位置点之间的事件

mysqlbinlog --start-position=1001 --stop-position=2000 mysql-bin.000001 | mysql -uroot -p

五、思考：

1、开启二进制日志影响性能吗？

有一些性能损耗，但是性能开销非常小（slightly slower），另外，开启binlog带来的好处要远远超过带来的性能开销。官方文档的介绍如下所示：Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

2、二进制日志与重做日志的区别？

1）二进制日志是数据库级别的文件，会记录所有与mysql有关的日志记录，包括InnoDB等其他存储引擎的日志，主要用于恢复数据库和建立集群；重做日志是InnoDB级别的文件，用来记录Innodb存储引擎本身的事务日志，主要用于数据恢复，保证事务的持久性和可靠性。

2）记录的内容不同，二进制日志文件记录的都是关于一个事务的具体操作内容，即逻辑变化情况；InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；

3）写入的时间也不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。