一、MySQL支持的日志

1.1、日志类型

??MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志：中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

1.2、日志的弊端

• 日志功能会降低MySQL数据库的性能
  • 例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多事件记录日志。
• 日志会占用大量的磁盘空间
  • 对于用户量非常大，操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。

二、慢查询日志（slow query log）

??MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上（不含10秒）的语句，认为是超出了我们的最大忍耐时间值。

??它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并针对性地进行优化，从而提高系统的整体效率。当我们的数据库发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。

??默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

-- 查询慢查询是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
-- 开启slow_query_log
SET GLOBAL slow_query_log = 'ON';
-- 修改long_query_time阈值
SET GLOBAL long_query_time = 1;
SET long_query_time = 1;
-- 查看慢查询的时间阈值设置
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
SHOW VARIABLES LIKE '%long_query_time%';
-- 查看慢查询数目
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
-- 关闭慢查询日志
SET GLOBAL slow_query_log = 'OFF';

想要永久设置需要修改my.cnf文件[mysqld]下增加或修改相应参数，然后重启服务器

[mysqld]
show_query_log=ON
show_query_time=3
log_output=FILE

??慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志即可。使用命令mysqldamin flush-logs来重新生成查询日志文件，具体命令如下，执行完毕后会在数据目录下重新生成慢查询日志文件。

mysqladmin -uroot -p flush-logs slow

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只能存在新的日志文件中，如果需要旧的查询日志，就必须事先备份。

三、通用查询日志（general query log）

??通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

-- 查看当前状态
SHOW VARIABLES LIKE '%general%';
-- 开启通用查询日志
SET GLOBAL general_log = ON; 
-- 关闭通用查询日志
SET GLOBAL general_log = OFF;

想要永久设置需要修改my.cnf文件[mysqld]下增加或修改相应参数，然后重启服务器

[mysqld]
general_log=ON
general_log_file=[path[filename]]

通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间。通用查询日志的目录默认为MySQL数据目录。在该目录下手动删除通用查询日志。

??使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文件。前提一定要开启通用日志。

mysqladmin -uroot -p flush-logs

四、错误日志（error log）

??错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止中的诊断信息，包括错误、警告和提示等。通过错误日志可以查看系统的运行状态，以便于即时发现故障、修复故障。如果MySQL服务器出现异常，错误日志时发现问题、解决故障的首选。

??在MySQL数据库中，错误日志功能时默认开启的。而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为mysqld.log（Linux系统）或hostname.err（mac系统）。如果需要指定文件名，则需要在my.cnf或my.ini中做如下配置：

[mysqld]
log-error=[path/[filename]]

 -- 查看日志
 SHOW VARIABLES LIKE 'log_err%';

??对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的硬盘空间。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除 。

??使用如下命令重新生成错误日志文件，具体命令如下:

mysqladmin -uroot -p flush-logs

??可能会报错，需要执行以下附加操作:

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

五、二进制日志（bin log）

5.1、二进制日志概述

??binlog 即 binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。它以事件形式记录保存在二进制文件中。通过这些信息，我们可以实现数据更新操作的全过程。

??binlog主要应用场景：

• 数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
• 数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

可以说，MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

-- 查看记录二进制日志是否开启
SHOW VARIABLES LIKE '%log_bin%';
-- 查看当前的二进制日志文件列表及大小
SHOW BINARY LOGS;
-- binlog格式查看
SHOW VARIABLES LIKE 'binlog_format';

• log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件
• log_bin_index1：是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
• log_bin_trust_function_creators：限制存储过程，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
• log_bin_use_v1_row_events：此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行（MySQL 5.6的默认值为2）

??binlog有3种格式，分别是ROW、Statement和Mixed。

• ROW：binlog的默认格式，5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
• Statement：每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
• Mixed：从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

想要永久设置需要修改my.cnf文件[mysqld]下增加或修改相应参数，然后重启服务器

[mysqld]
log-bin=[path/[filename]]
binlog_expire_logs_seconds=600 
max_binlog_size=100M

??当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

??所有对数据库的修改都会记录在binlog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了。

mysqlbinlog -v [path/[filename]]

??上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令，在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:查询总条数（不指定就是所有行）

??MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件。具体如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’ 
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

5.2、使用日志恢复数据

??如果MySQL服务器启动了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志恢复数据。mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql -uuser -ppass;

??使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

• filename：日志文件名
• option：可选项，比较重要的两对option参数是--start-date、--stop-date和--start-position、--stop-position
  • --start-date和--stop-date：可以指定恢复数据库的起始时间和结束时间
  • --start-position和--stop-position：可以指定恢复数据的开始位置和结束位置

使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复。

5.3、写入机制

??binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。我们可以通过binlog_cache_size参数控制单个线程binlog cache大小，如果存储内容超过这个参数，就要暂存到磁盘（swap）。binlog日志刷盘流程如下：

上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
上图的fsync，才是将数据持久化到磁盘的擦破做。

??write和fsync的时机，可以由参数sync_binlog控制，默认是0。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog会丢失。如下图：

??为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

5.4、两阶段提交

??在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

??假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后，从机用binlog日志恢复数据时，就会少这一次的操作，而原库因为redo log日志恢复，最终数据不一致。

??为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log写入拆分了两个步骤prepare和commit，这就是两阶段提交。使用两阶段提交后，写入binlog时发生异常时也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

六、中继日志（relay log）

??中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是：从服务器名 -relay-bin.序号。中继日志还有一个索引文件： 从服务器名 -relay- bin.index，用来定位当前正在使用的中继日志。

??中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看。

??如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之前不同 。而中继日志里是包含从服务器名的。在这种情况下，就可能导致你恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。解决的方法也很简单，把从服务器的名称改回之前的名称。