MySQL 日志（二）

本篇将继续介绍MySQL日志的相关内容

一、二进制日志

简介

注意事项

删除二进制日志

查看二进制日志

二进制日志的格式

二、服务器日志维护

一、二进制日志

简介

二进制日志中主要记录了MySQL的更改事件（不包含SELECT和SHOW),例如：表的创建和表中数据的修改（增删改）。二进制日志还会将这些更改语句的执行时间记录下来，开启二进制日志会对服务器的性能产生一定消耗。二进制日志中还会记录可能进行数据修改的语句，例如没有匹配到具体行的删除和更新语句。

二进制日志主要用于下面这两种场景：

主从节点数据复制：在主从复制时，从节点服务器会去获取主节点的二进制日志文件，并在从节点服务器上重新执行日志中所记录的修改语句，从而保证主从服务器数据的一致性。
数据恢复：当服务器根据某一时间点的备份文件进行数据恢复时，会去重新执行二进制日志文件中记录的该时间后记录的修改语句，以使服务器恢复到一个最新的状态。

二进制日志中记录的语句中如果包含了用户的密码等信息，会对该信息进行加密处理，不会以文本的形式显示，以保证安全。

我们可以通过下面这条SQL来查询有关二进制日志的相关系统变量

show variables like '%bin%'

对于与二进制日志有关的状态变量，我们可以根据下面这条SQL查询：

SHOW STATUS LIKE '%bin%';

注意事项

接下来我们来看一下二进制日志相关的注意事项：

• 在Linux中默认启用二进制日志，也就是说，log_bin的默认值为on，如果需要禁用二进制日志可以值定--skip-log-bin 或者 --disable-log-bin选项，如果同时指定了--log-bin选项则后指定的选项优先

• 选项--log-bin的值会被用于指定二进制日志文件的基本名，如果没有指定该选项的值，则默认使用基本名称binlog，通常建议指定一个名称

• 二进制日志文件的文件名由基本名 + 数字扩展名组成，服务器每创建一个新的二进制日志文件时，都会在上一个日志文件的数字扩展名的基准上加1来新的数字扩展名。当服务器中发生如下事件时，会自动创建一个新的二进制日志文件：

服务器启动或者重新启动
服务器执行刷新日志操作
当日志文件大小超过max_binlog_size（该选项表示为单个日志的最大字节数，最小值为4kb，最大为1GB），需要注意的是，二进制日志的值可能会超过max_binlog_size,因为二进制日志是以事务为单位的，它会完整的记录整个任务，，不会出现拆分的情况，因此当记录一个大事务时，即使当前已经超过max_binlog_size指定的值了，也会继续把事务记录完。

• mysqld会生成一个包含二进制日志文件名的日志索引文件，默认情况下，该索引文件与二进制日志文件具有相同的基本名，扩展名为index。该名称可以通过--log-bin-index来修改。

• 二进制日志文件及其索引文件默认保存在数据目录中，可以通过--log-bin指定绝对路径加基本名来进行修改。--log-bin对应的系统变量为log_bin_basename

• 在MySQL5.7中，启用二进制日志必须指定服务器ID，具体为指定sever_id的值。而在MySQL8.0中，server_id默认为1，在集群环境中，每台MySQL服务器都必须有一个唯一的服务器id.

• 二进制日志会在事务或者语句完成之后，释放锁和提交事务之前进行日志的记录，以保证日志按事务提交的顺序进行记录

• 在一个未提交的事务中，对支持事务的表（InnoDB表）的修改的更改会被缓存，直到服务器收到commit语句，并在commit执行之前，才会将之前的缓存写入到二进制日志文件中。

• 如果发生回滚，则在整个事务中记录一个回滚语句，但是对不支持事务的表（MyISAM表)的修改不能回滚，所有这些修改都会被记录到日志文件，然后复制到从节点

• 非事务的表在执行后会立即存储到二进制日志文件中。而事务表如果没有手动开启事务，则会在每次执行修改语句时都会自动开启一个事务。

• 当处理事务的线程启动时，会分配一个大小为binlog_cache_size的缓冲区来缓存修改语句，如果语句的大小比缓冲区的值大，线程则打开一个临时文件来存储事务，临时文件在线程结束时删除。binlog_cache_use状态变量表示缓冲区存储事务的数量，binlog_cache_disk_use则表示有缓冲区中的事务有多少使用了临时文件，结合这两个状态变量的值，可以将binlog_cache_size的值进行调优，以获得一个最为合适的大小，从而避免使用临时文件。max_binlog_cache_size用来设置缓冲区的最大空间，设置的默认值和最大值都为 4GB，最小为4096字节，如果事务语句大于这个值，事务将会执行失败然后回滚。

删除二进制日志

我们可以使用reset master语句来删除所有的二进制日志文件，或者使用purge binary logs来删除一部分二进制日志文件，具体如下：

reset master #重置二进制日志文件和索引文件为初始状态

purge binary logs to 二进制日志文件 #删除指定的二进制日志文件前面的所有日志文件并更新索引文件

purge binary logs before 时间 #删除指定时间前的所有二进制日志文件并更新索引

查看二进制日志

下面我们来看一下如何查看二进制日志文件：

首先，我们可以通过前面提到过多mysqlbinlog程序来查看，语法如下：

mysqlbinlog 二进制日志文件 > 要导出到的文件地址

其次我们还可以通过SQL语句来查看，具体如下：

show binlog events in 日志名称 from 从第几条语句开始查 limit 查多少条

二进制日志的格式

在二进制日志中有三种不同的格式，具体如下：

基于语句的日志格式（STATEMENT)，记录具体执行的SQL语句
基于行的日志格式(ROW , 默认)，记录表中每一行所受到的影响
混合日志记录格式（MIXED)，该种格式下，默认使用基于语句的日志记录。如果MySQL认为基于语句的格式不能保证主从复制的数据安全，会自动切换到基于行的日志格式，例如主节点在语句中用了UUID函数，如果记录原始语句会导致从节点生成的值和主节点的不一样，从而导致主从节点数据不一样，因此次时使用基于行的格式来记录UUID的真实值，而不再记录原始SQL语句。

我们可以通过--binlog-format来指定使用哪种格式。

基于行与基于语句的区别，可以参考下面的内容：

基于语句： update student set age = 18 where id betwenn 10 and 20；

基于行： update student set age = 18 where id = 10

                 update student set age = 18 where id = 11

                 update student set age = 18 where id = 12

                 update student set age = 18 where id = 13

. ....

                 update student set age = 18 where id = 20

二、服务器日志维护

MySQL服务器会创建很多日志文件来记录MySQL的执行情况，但是，随着时间的推移，日志文件可能会增长的非常大，从而耗费大量的磁盘空间，因此我们需要定期备份或者删除旧的日志文件。

在MySQL中，二进制日志文件默认的过期时间为30天，过期后会自动进行删除，，该期限可以通过系统变量binlog_expire_logs_sencond来指定单位为秒，当我们需要使用新的日志文件时，我们可以执行FLUSH LOGS 、 mysqladmin flush-logs，mysqladmin refresh、mysqldump --flush-logs、mysqldump -- master -date等命令来刷新日志。另外当二进制日志文件大小达到max_binlog_size时会自动刷新日志。flush logs支持刷新某一种日志，具体如下：

FLUSH BINARY LOGS # 刷新⼆进制⽇志

FLUSH ERROR LOGS # 刷新错误⽇志

FLUSH GENERAL LOGS # 刷新⼀般查询⽇志

FLUSH RELAY LOGS # 刷新中继⽇志

FLUSH SLOW LOGS # 刷新慢查询⽇志 1

需要注意的是一般查询日志、错误日志，慢查询日志只是关闭并重新打开日志，因此在执行日志刷新时需要特殊操作一下，具体可以参考前面错误日志的刷新。

最后还需要注意在运行时对一般日查询日志和慢查询日志进行改名时，需要先禁用日志（设置general_log、slow_query_log为OFF），然后再重命名，最后再重新启用这两个日志。因为当我们在运行时重命名日志后，MySQL会因找不到原来的日志而保错