01. 二进制日志

二进制日志（binlog）记录了所有的DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包含数据查询语言。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

01. 日志的位置和格式

当用--log-bin[=file_name]选项启动时，mysqld开始将数据变更情况写入日志文件。如果没有给出file_name值，默认为主机名后边跟“-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数DATADIR（数据目录）指定的目录。

mysql 5.5 日志的格式有三种:

1. statement

mysql 5.1 版本之前都采用这种方式，顾名思义，日志中记录的都是语句（statement），每一条对数据造成修改的SQL语句都会记录在日志中，通过mysqlbinlog工具，可以清晰的看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库执行一次。这种格式的优点是日志记录清晰易读、日志量少，对I/O影响较小。缺点是在某些情况下slave的日志复制会出错（基于系统时间的一些语句）。

2. row

mysql 5.1 版本之后，出现了这种新的日志格式。它将每一行的变更记录到日志中，而不是记录SQL语句。这种格式的优点是记录每一行的变化细节，不会出现某些情况下无法修复的情况。缺点是日志量大，对I/O影响较大（例如更新一张100万条数据的大表）。

3. mixed

这是目前mysql默认的日志格式，即混合了statement和row两种格式。默认情况下采用statement，但在一些特殊情况下，采用row来进行日志记录。例如，采用NDB存储引擎，此时对表的DML语句全都采用row，客户端使用了临时表，客户端使用了不确定函数，比如current_name()等。因为这种不确定的函数在主从中得到的值可能不同，导致主从数据产生不一致。mixed格式尽可能的利用两种模式的优点，而避开他们的缺点。

可以在global个和session级别对binlog_format进行日志格式设置，但一定要谨慎操作，确保从库的复制能够正常进行。

02. 日志的读取

由于日志以二进制方式存储，不能直接读取，需要用mysqlbinlog工具来查看，语法如下：

[root@ufo128 mysql]# cat my-medium.cnf | grep -E "(^binlog_format)|(^log-bin)"
log-bin=mysql-bin
binlog_format=mixed
[root@ufo128 mysql]# mysqlbinlog -vv mysql-bin.000001 | more

# rpm安装包默认使用 my-medium.cnf 内置的配置文件
[root@ufo128 mysql]# pwd
/usr/share/mysql
[root@ufo128 mysql]# ll *.cnf
-rw-r--r-- 1 root root  4688 Jan 27  2017 my-huge.cnf
-rw-r--r-- 1 root root 19751 Jan 27  2017 my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root  4662 Jan 27  2017 my-large.cnf
-rw-r--r-- 1 root root  4673 Jan 27  2017 my-medium.cnf
-rw-r--r-- 1 root root  2441 Jan 27  2017 my-small.cnf

03. 日志的删除

对于比较繁忙的OLTP（在线事务处理）系统。由于每天生成的日志量大，这些日志如果长时间不清理，将会对磁盘空间带来很大的浪费。因此，定期删除日志是DBA维护mysql数据库的一个重要工作内容。

方式1

执行"reset master"命令，该命令将删除所有的binlog日志，新编号从"000001"开始。

查看删除前的日志，结果中的"mysql-bin.index"是日志文件的索引，记录了最大日志的序号。

mysql> system ls -ltr mysql-bin*
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.000006
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.index
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.000007
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.000008
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.000009

mysql> reset master;
mysql> OK, 0 rows affedted (0.08 sec)

查看删除后的日志

mysql> system ls -ltr mysql-bin*
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.index
-rw-rw---- 1 mysql mysql 145 aug 10 04:04 mysql-bin.000001

方式2

删除mysql-bin.000006之前的所有日志

mysql> purge master logs to 'mysql-bin.000006';

方式3
删除指定日期之前产生的所有日志

mysql> purge master logs before '2021-02-06 23:00:00';

方式4

设置参数 --expire_logs_day参数，此参数的含义是日志的过期天数，过了指定的天数后，日志将会被自动删除。（新设置的参数，重启数据库才会生效，也会触发一次删除）

[root@ufo128 share]# cat /usr/share/mysql/my-medium.cnf | grep expire_logs_day
expire_logs_day=3

此时修改系统时间，或刷新日志文件都有可能触发删除操作。

[root@ufo128 ~]# date -s '2021-02-05 00:00:00'
或
[root@ufo128 ~]# mysqladmin flush-log

04. 其他操作

由于二进制日志对数据的完整性和安全性起着非常重要的作用，因此，还有一些更小粒度的管理参数。

• --binlog-do-db=db_name，告诉主服务器，在当前数据库的所有操作都会记录到二进制文件中，其他数据库则会忽略记录。
• --binlog-ignore-db=db_name，告诉主服务器，当前数据库的所有操作不会被记录，其他数据库则会被记录。

上边的两个参数可以使用多次，指定多个数据库。

• --inodb-safe-binlog 经常和 --sync-binlog（没写N次日次同步到磁盘）配合使用，使事务在日志中的记录更加安全。
• set sql_log_bin=0，具有super权限的用户可以禁止将自己的语句记录到日志。使用时一定要特别小心，因为他可能会造成主从数据不一致。