MySQL二进制日志

一、Binary log

二进制日志是MySQL重要的日志之一，用于记录修改数据或有可能引起数据改变的MySQL语句，像select、show这种不会修改数据的语句，二进制日志是不会记录的。

这篇文章是对 MySQL 二进制日志文件做一个阐述，对于主从和恢复的相关文章，会后续写到。

二进制日志的主要作用：

（1）可以完成主从复制功能。主服务器 把所有修改数据的操作记录到 二进制日志 中，从服务器 请求主服务器的二进制日志中的事件，然后 从服务器 再在本地将事件进行回放，从而达到主从复制的目的。

（2）进行恢复操作。数据可以通过二进制日志，使用 mysqlbinlog 命令，实现基于时间点的恢复。

在my.cnf中使用配置参数 log_bin = /PATH/LOG_FILE 可以启用二进制日志

# vim /etc/my.cnf

log-bin = /usr/local/mysql/data/mysql-bin

root@localhost 23:45: [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql-bin 是二进制日志文件的文件名前缀，后面的数字是二进制日志的序列号，号码是按顺序来排列的，每次启动 MySQL、重启 MySQL、刷新日志（flush logs）或是到达了 max_binlog_size 值的大小，就会滚动binlog，生成一个新的binlog文件。mysql-bin.index 文件是binlog的索引文件，用来记录产生的二进制日志的序列号。

# ls /usr/local/mysql/data/mysql-bin.*
/usr/local/mysql/data/mysql-bin.000001  /usr/local/mysql/data/mysql-bin.000003
/usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.index

 # cat /usr/local/mysql/data/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003

二、涉及 Binary log 的相关参数

（1）max_binlog_size

该参数制定了 binlog 的最大值，如果超过该值会自动生成新的 binlog 文件（重启 MySQL 实例也会滚动 binlog）。从 MySQL 5.0 开始，该值的默认值为 1G。注意：一般情况下，生产线上我们 binlog 的生成时间的最小间隔保持在 2-5 分钟，所以该参数不要太大，可以调整为 256MB。

（2）binlog_cache_size

所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓存中的二进制日志写入到二进制日志文件。该缓存大小由 binlog_cache_size 的值来决定，它的默认大小是 32KB，并且 binlog_cache_size 是基于会话的，也就说当一个线程要开始一个事务时，MySQL 会自动分配一个该值大小的缓存。

注意：设置此值要小心，如果设置太小就会使用磁盘上的临时文件来记录。可以通过 show global status 来查看 Binlog_cache_disk_use 和 Binlog_cache_use 的使用情况来判定 binlog_cache_size 设置是否合适。如下显示结果中 Binlog_cache_disk_use = 0，表示没有使用磁盘上的临时文件，证明 binlog_cache_size 是够用的。生产线上建议 1-4MB

root@localhost 00:11: [(none)]> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

（3）binlog_format

binlog_format 代表二进制日志的记录格式，binlog 的格式有 statement、row、mixed 三种。生产线上为保险起见，一般给 row。

  1. statement：每一条会修改数据的SQL语句会记录到 binlog 中，5.1之前这都是默认格式，不建议生产线上使用。

优点：并不需要记录每一行的数据变化，减少了 binlog 的日志量，节约I/O，提高性能。

缺点：在某些情况下会导致master-slave中数据不一致。

  2. row：不记录每条SQL语句的上下文信息，仅仅记录哪条数据被修改了，然后修改成什么样了。5.6以后新增了一个参数binlog_rows_query_log_events，设置该参数，也可以在row模式下，看见用户的完整SQL语句。

优点：任何情况下都可以被复制，可以保证数据的精确性，这对主从复制来说是最安全可靠的。

缺点：会产生大量的日志。

  3. mixed：混合使用 row 和 statement 格式。对于 statement 无法复制的操作使用 row 模式保存 binlog，mysq l会依据SQL语句来自行判断所保存的日志格式。在 5.1 版本的时候是 statement 和 row 的过度格式，不推荐使用。

（4）sync_binlog

当 innodb_flush_log_at_trx_commit = 1 时，表示事务提交时，事务日志立马从内存刷血到磁盘中的事务日志文件中，而 sync_binlog 对于二进制日志的作用，就像 innodb_flush_log_at_trx_commit 对于事务日志的作用，由于二进制日志一开始存在于内存（binlog_cache）中，如果将 sync_binlog = 1，则表示每 1 次事务提交之后，都会将内存中的二进制日志立即同步到磁盘中的二进制日志文件中，如果将 sync_binlog = 0，则表示事务提交之后，MySQL不会记录将内存中的 binlog 刷写到磁盘中的binlog 日志文件中，而是由文件系统决定什么时候刷写，这可能取决于文件系统的缓存机制。

为了保证安全性，我们可以将 sync_binlog = 1。为了获得最佳性能，而不考虑安全性，可以将该值设为 0。

将 sync_binlog = 1 和 innodb_flush_log_at_trx_commit = 1，这就是MySQL的双一模式，可以保证数据库的安全性。

（5）expire_logs_days

二进制日志过期时间，单位为天，默认为0。一般情况下，我们可以设置的时间长一点。

（6）binlog_do_db 和 binlog_ignore_db

表示需要写入或忽略哪些库的二进制日志，默认为空，表示将所有库的操作都记录到二进制日志。

（7）log_slave_updates

级联复制。

三、 查看和清除 Binary log

既然都叫二进制日志了，那就别拿 cat 命令去查看它了好不好...

查看所有二进制日志

root@localhost 00:54: [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |    793937 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看指定的二进制日志文件

root@localhost 00:55: [(none)]> show binlog events in 'mysql-bin.000003'\G

从指定的事件位置开始查看

root@localhost 00:57: [(none)]> show binlog events in 'mysql-bin.000003' from 790628\G

从指定的事件位置开始查看，只显示 limit N 条事件

root@localhost 00:57: [(none)]> show binlog events in 'mysql-bin.000003' from 790628 limit 2\G

 

在命令行行中查看的话必须要使用 mysqlbinlog 命令，但是当正在执行 mysql 读写操作时建议不要直接打开正在使用的二进制日志文件，如果要打开的话，就先用 flush logs 滚动一下日志，再去查看。 -v 代表可以看到具体的执行信息， --base64-output 把二进制日志文件转化格式。

# cd /usr/local/mysql/data/

# mysqladmin -uroot -p flush-logs

# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001 > bin.log

# vim !$

指定偏移量来查看二进制日志事件

# mysqlbinlog --start-position=775241 --stop-position=775464  mysql-bin.000001 > bin2.log

 

二进制日志会记录大量的信息，如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是删除之后可能导致数据库崩溃时无法进行恢复，所以要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志是绝对绝对不可以删除的。

（1）自动清除

root@localhost 01:41: [(none)]> set global expire_logs_days = 7;

（2）手动清除

删除mysql-bin.000005之前的所有二进制日志，不包括mysql-bin.000005

root@localhost 01:43: [(none)]> purge binary logs to 'mysql-bin.000005';

root@localhost 01:44: [(none)]> show binary logs;

 

关于主从和时间点恢复的操作，会在后续文章中写到。