二进制日志的作用是记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如含有insert、update、delete、create、drop、alter等关键字的语句。
二进制日志最重要的作用有2个，具体如下。 第一个是记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，MySQL的备份将无法完整还原数据。 第二个是实现主从复制功能，具体见MySQL主从复制的相关内容。
在/etc/my.cnf log-bin=/data/mysql/bin/binlog.bin
[root@songls data]# grep log_bin /etc/my.cnf log_bin #<==默认情况下记录日志前缀为“主机名-bin”。 mysql> show variables like ‘log_bin’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | log_bin | ON | #<==记录binlog开关。 ±--------------±------+ 1 row in set (0.00 sec)
mysql> show variables like ‘%log_bin’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | log_bin | ON | #<==记录binlog开关。 | sql_log_bin | ON | #<==临时不记录binlog开关。 ±--------------±------+ 2 rows in set (0.00 sec) 有个参数可以实现在开启binlog功能的前提下，临时不记录binlog，示例如下： mysql> set session sql_log_bin = OFF; #<==临时停止记录binlog，注意是session 级别，不影响其他会话。 Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%log_bin’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | log_bin | ON | | sql_log_bin | OFF | #<==已关闭。 ±--------------±------+ 2 rows in set (0.00 sec) mysql> create database oldgirl; #<==建库测试。 Query OK, 1 row affected (0.00 sec)
mysql> show binary logs; #<==查看binlog文件列表及位置点。 ±------------------±----------+ | Log_name | File_size | ±------------------±----------+ | oldboy-bin.000001 | 143 | | oldboy-bin.000002 | 168 | | oldboy-bin.000003 | 168 | | oldboy-bin.000004 | 9299 | | oldboy-bin.000005 | 211 | #<==最新的binlog文件及位置点，也可以通过 “show master status;”来确定。 ±------------------±----------+ 5 rows in set (0.00 sec) mysql> system mysqlbinlog oldboy-bin.000005|grep “oldgirl” #<==过滤binlog文件，没有记录binlog。 mysql> set session sql_log_bin = On; #<==开启开关。 Query OK, 0 rows affected (0.00 sec) mysql> drop database oldgirl; #<==删除数据库。 Query OK, 0 rows affected (0.00 sec) mysql> system mysqlbinlog oldboy-bin.000005|grep “oldgirl” #<==继续过滤，发现记录了binlog。 drop database oldgirl 到这里，读者应该知道sql_log_bin的功能了吧，这个功能通常用于在用户使用mysql恢复数据时不希望恢复的数据SQL记录到binlog里的情况。当然，还有其他的应用场景。 4.二进制日志文件的刷新条件 1）数据库重启会自动刷新binlog为新文件。 2）执行“mysqldump -F”或“mysqladmin flush-logs”会将binlog刷新为新文件。 3）binlog文件达到1GB左右时，会自动刷新binlog为新文件。 4）人为配置切割及调整。 binlog最大值控制参数及默认大小查看方法如下： mysql> show variables like ‘max_binlog_size’; ±----------------±-----------+ | Variable_name | Value | ±----------------±-----------+ | max_binlog_size | 1073741824 | ±----------------±-----------+ 1 row in set (0.00 sec)
除了很多按序列生成的binlog文件列表之外，还有一个索引文件，例如下文里的oldboy-bin.index： [root@oldboy data]# pwd /application/mysql/data [root@oldboy data]# ls -l oldboy-bin.* -rw-rw----. 1 mysql mysql 143 Mar 3 05:50 oldboy-bin.000001 -rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000002 -rw-rw----. 1 mysql mysql 168 Mar 3 05:57 oldboy-bin.000003 -rw-rw----. 1 mysql mysql 9299 Mar 19 19:34 oldboy-bin.000004 -rw-rw----. 1 mysql mysql 211 Mar 19 20:15 oldboy-bin.000005 -rw-rw----. 1 mysql mysql 100 Mar 19 19:34 oldboy-bin.index 索引文件的文件名和binlog文件一样，只是扩展名为index，查看索引文件内容的命令如下： [root@oldboy data]# cat oldboy-bin.index ./oldboy-bin.000001 ./oldboy-bin.000002 ./oldboy-bin.000003 ./oldboy-bin.000004 ./oldboy-bin.000005 binlog索引文件的控制参数为： mysql> show variables like ‘log_bin_index’; ±--------------±------------------------------------------------+ | Variable_name | Value | ±--------------±------------------------------------------------+ log_bin_index | /application/mysql-5.6.40/data/oldboy-bin.index | ±--------------±------------------------------------------------+ 1 row in set (0.00 sec)
binlog日志很重要，不能随意清除，有些读者看到所维护的服务器空间满了，竟然会直接删除binlog物理文件，这样的操作是错误的，应避免。那么如何正确删除binlog文件呢？ 首先，要确定什么时候可以删除binlog。 理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3~7天的本地binlog文件。 下面来看看具体的删除方式。 （1）设置参数自动删除binlog 设置参数自动删除binlog是每个管理员都应该做的，参数设置示例如下。 假设参数为： expire_logs_days = 7 #<==删除7天前的日志 该参数默认是没有配置的，生产中可以同时实现在线更改以及永久更改配置文件： mysql> show variables like ‘expire_logs_days’; ±-----------------±------+ | Variable_name | Value | ±-----------------±------+ | expire_logs_days | 0 | ±-----------------±------+ 1 row in set (0.00 sec)
mysql> set global expire_logs_days = 7; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘expire_logs_days’; ±-----------------±------+ | Variable_name | Value | ±-----------------±------+ | expire_logs_days | 7 | ±-----------------±------+ 1 row in set (0.00 sec)
[root@oldboy data]# grep expir /etc/my.cnf expire_logs_days = 7 （2）从最开始一直删除到指定的文件位置（不含指定文件） 这种方法一般用于处理临时的需求，操作如下： [root@oldboy data]# cp oldboy-bin.* /tmp 登录数据库时执行如下命令： mysql> show binary logs; ±------------------±----------+ | Log_name | File_size | ±------------------±----------+ | oldboy-bin.000001 | 143 | | oldboy-bin.000002 | 168 | | oldboy-bin.000003 | 168 | | oldboy-bin.000004 | 9299 | | oldboy-bin.000005 | 211 | ±------------------±----------+ 4 rows in set (0.00 sec) mysql> purge binary logs to ‘oldboy-bin.000002’; Query OK, 0 rows affected (0.00 sec) mysql> show binary logs; ±------------------±----------+ | Log_name | File_size | ±------------------±----------+ | oldboy-bin.000002 | 168 | #<==序列000002以前的就没了。 | oldboy-bin.000003 | 168 | | oldboy-bin.000004 | 9299 | | oldboy-bin.000005 | 211 | ±------------------±----------+ 4 rows in set (0.00 sec) （3）按照时间删除binlog日志 这种方法也是用于处理临时的需求，操作如下： [root@oldboy data]# ls -l --time-style=long-iso oldboy-bin* -rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000002 -rw-rw----. 1 mysql mysql 168 2018-03-03 05:57 oldboy-bin.000003 -rw-rw----. 1 mysql mysql 9299 2018-03-19 19:34 oldboy-bin.000004 -----------------------±---------------------+ 2 rows in set (0.00 sec) （2）max_binlog_size 该参数用于设置binlog日志的最大大小，默认为1GB，但是该值并不能严格控制binlog的大小。若binlog大小接近1GB，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。该参数的默认值查询结果为： mysql> show variables like ‘%max_binlog_size%’; ±----------------±-----------+ | Variable_name | Value | ±----------------±-----------+ | max_binlog_size | 1073741824 | ±----------------±-----------+ 1 row in set (0.00 sec) （3）sync_binlog 这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。 对于“sync_binlog”参数的说明具体如下。 ·“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。 ·“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。 该参数默认的设置是0，示例如下： mysql> show variables like ‘%sync_binlog%’; ±--------------±------+ | Variable_name | Value | ±--------------±------+ | sync_binlog | 0 | ±--------------±------+ 1 row in set (0.00 sec) 设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。
