MySQL日志（二）—— 二进制日志binlog

一、binlog介绍

        二进制日志（binary log）是MySQL数据库的二进制日志，记录了对MySQL数据库执行更改的所有的DDL和DML操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。一般来说开启binlog日志会有一定的性能损耗。根据MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。

        若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。

        比如执行一条SQL，运行结果：

mysql> update t_user set name='zhang' where id=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

        无论是增量备份还是主从复制，都是需要开启mysql-binlog日志，并且在磁盘故障的时候可以利用mysql-binlog恢复数据。

二、binlog使用场景

        二进制日志主要有以下几种作用

2.1、恢复（recovery）

        某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志，利用mysqlbinlog工具来恢复数据。

2.2、复制（replication）

        其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步。mysql replication在master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

        阿里巴巴的 canal 就是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费

2.3、审计（audit）

        用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

三、binlog数据格式

        binlog有三种格式：STATEMENT、ROW、MIXED 。

        查看当前使用的binlog数据格式

show variables like 'binlog_format';

3.1、STATMENT模式

        STATEMENT基于SQL语句的复制(statement-based replication, SBR)，每一条修改数据的sql语句会记录到binlog中。

        优点：不需要记录每一条SQL语句与每行的数据变化，减少了binlog日志量，减少了磁盘IO，提高性能。

        缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)。

3.2、ROW模式

        基于行的复制(row-based replication, RBR)：二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。Row模式的日志内容会非常清楚的记录下每一行数据修改的细节。

        优点：不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

        缺点：将参数binlog_format设置为ROW，会对磁盘空间要求有一定的增加。每行的更改都会记录到日志中，这样会产生大量的日志内容，比如update操作，binlog会记录所有修改的数据，造成binlog日志量会很大，执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，每一条修改的记录都会记录到日志中。另外由于复制是采用传输二进制日志方式实现的，因此复制的网络开销也有所增加。

        binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性。但是这样会使二进制文件大小的增加

3.3、MIXED模式

混合模式复制(mixed-based replication, MBR)：STATEMENT和ROW模式的混合使用。MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况有：

1）表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。

2）使用了UUID（）、USER（）、CURRENT_USER（）、FOUND_ROWS（）、ROW_COUNT（）等不确定函数。

3）使用了INSERT DELAY语句。

4）使用了用户定义函数（UDF）。

5）使用了临时表（temporary table）。

并不是所有的修改都会以ROW 模式来记录，遇到表结构变更的时候就会以STATEMENT模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

四、开启binlog日志

4.1、查看binlog日志开启状态

binlog日志默认不开启，log_bin值为OFF，binlog日志位置（log_bin_basename）

mysql> show variables like 'log_bin%'; -- ON为开启

4.2、开启binglog

4.2.1、查看MySQL配置文件位置

[root@jeespring ~]# mysqld --verbose --help |grep -A 1 'Default options' 
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

4.2.2、修改MySQL配置

        在MySQL配置文件/etc/my.cnf文件中的[mysqld]节中添加下面的配置文件：

[mysqld]
log-bin=/var/lib/mysql/mysql-bin
server-id=1
binlog_format=MIXED

binlog_format=MIXED(加入此参数才能记录到insert语句

4.2.3、binlog其他属性

#binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 1000m
#binlog缓存大小
binlog_cache_size = 1m
#binlog最大缓存大小
max_binlog_cache_size = 1024m
#binlog最大缓存大小
sync_binlog = 1

        1、max_binlog_size

        max_binlog_size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。从MySQL 5.0开始的默认值为1073 741824，代表1G（在之前版本中max_binlog_size默认大小为1.1G）。

        2、binlog_cache_size

        当使用InnoDB存储引擎时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。

        此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。

        3、binlog_cache_use、binlog_cache_disk_use

        binlog_cache_use 记录了使用临时文件写二进制日志的次数。binlog_cache_disk_use 记录了使用临时文件写二进制日志的次数。

mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 54    |
+-----------------------+-------+
2 rows in set (0.00 sec)

        使用缓冲次数为54，临时文件使用次数为0。看来32KB的缓冲大小对于当前这个MySQL数据库完全够用，暂时没有必要增加binlog_cache_size的值。

再来看一下生产环境配置 ：

        使用缓冲次数为1374686，临时文件使用次数为37，可以考虑增加binlog_cache_size的值。

        4、sync_binlog

        在默认情况下，二进制日志并不是在每次写的时候同步到磁盘。因此，当系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这会给恢复和复制带来问题。

        参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘， sync_binlog 的默认值为0。

1. sync_binlog=0 的时候，表示每次提交事务都只写入到缓冲区，不同步到磁盘；

2. sync_binlog=1 表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。

3. sync_binlog=N(N>1) 的时候，表示每次提交事务都写入到缓冲区，但累积 N 个事务后才同步到磁盘。

4.2.4、重启mysql服务

        配置完成后，需要重启数据库，在/var/lib/mysql下会产生两个日志文件mysql-bin.index、mysql-bin.000001

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

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

service mysqld restart

4.2.5、查看binlog日志文件

        登录MySQL，查看MySQL

mysql> show binary logs;

        当前记录日志文件mysql-bin.000001，初始位置154。 

五、使用binlog恢复数据

5.1、查看当前的binlog日志文件

show master status;

查看binlog日志文件里面的内容

mysql> show binlog events in 'mysql-bin.000001';

5.2、新增数据

新建binlog数据库，新建表t_user，并新增一条数据，SQL如下：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_user`(`id`, `name`, `address`) VALUES (1, 'zhang', '北京');

5.3、查看添加过数据的binlog日志文件

此时mysql-bin.000001日志文件的保存了事件信息 ，开始、结束操作位置等

5.4、刷出日志

为了方便，我们重新开启一个新的日志，这样上面的操作，就只保留在mysql-bin.000001中了，后续的操作会保存在新生成mysql-bin.000002日志文件中。

mysql> flush logs;

5.5、恢复数据

开始演示恢复表操作，首先删除t_user表，此时删除的操作记录会保存到mysql-bin.000002中

5.6、使用mysqlbinlog恢复数据

使用show binlog查看binlog日志记录信息

show binlog events in 'mysql-bin.000001';

我们从图上可以看出，建表的起始位置是384，使用mysqlbinlog命令来恢复表和数据

mysqlbinlog --start-position=384 mysql-bin.000001|mysql -uroot -p

 5.7、查看恢复后的数据

七、binlog常用命令

1、查看产生的日志文件

mysql> show binary logs;

2、查看当前使用的日志文件，即记录操作的最后一个日志文件

mysql> show master status;

3、查看日志事件信息 ，开始、结束操作位置等

mysql> show binlog events;

4、查看mysql-bin.000001日志文件的事件信息 ，开始、结束操作位置等

mysql> show binlog events in 'mysql-bin.000001';

5、产生新日志，后面的操作会写到新日志中，日志名mysql-bin.00000X+1

mysql> flush logs;

6、清空所有的日志，从mysql-bin.000001开始重新记录日志

mysql> reset master;

7、查询mysql-bin.000001中记录的操作，不显示sql，需要配置mysql的环境变量

mysqlbinlog mysql-bin.000001;

8、查询mysql-bin.000001中记录的操作，会显示sql语句，需要配置mysql的环境变量

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001;

9、指定查询 mysql-bin.000001 这个文件，从pos点:410开始查起：

  mysql> show binlog events in 'mysql-bin.000021' from 410;

10、指定查询 mysql-bin.000001 这个文件，从pos点:410开始查起，查询2条

  mysql> show binlog events in 'mysql-bin.000021' from 410 limit 2;

附：

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

选项解析：

  IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)

  FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

  LIMIT [offset,] 偏移量(不指定就是0)

  row_count 查询总条数(不指定就是所有行)