MySQL日志的7种类型详解，看这一篇就够了

​

一、前言

日志文件中记录着MySQL数据库运行期间发生的变化；包括MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等，是MySQL数据库的重要组成部分。

MySQL日志分类:

• 错误日志
• 查询日志
• 慢查询日志
• 事务日志(Redo log)
• 二进制日志
• 中继日志

二、错误日志

默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，可以使用下面SQL来查看位置

SHOW VARIABLES LIKE 'log_error';
这将显示错误日志文件的位置。然后，使用文件查看工具
如cat、less或文本编辑器打开并查看错误日志文件的内容 。

2.1 错误日志配置

为了方便管理，用户可以根据自己的需求来配置错误日志存储位置和日志级别，配置参数如下:

• log-error: 用于指定错误日志的文件路径和名称。
• log-error-verbosity: 控制错误日志记录的详细程度，可以设置为1（仅错误）、2（错误和警告）、3（错误、警告和注释）。
• log-timestamps: 控制是否在错误日志中包含时间戳，可以设置为UTC或SYSTEM（本地系统时区）。
  错误日志不会被自动释放，但可以通过日志旋转工具（如logrotate）来管理其大小，防止日志文件过大。管理员应定期检查并根据需要旋转或归档错误日志 。

2.2 错误日志记录信息

1.服务器启动和关闭过程中的信息

• 未必是错误信息，比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等

2.服务器运行过程中的错误信息

• 比如sock文件找不到，无法加载mysql数据库的数据文件，如果忘记初始化mysql或data dir路径找不到，或权限不正确等 都会记录在此

3.事件调度器运行一个事件时产生的信息

• 一旦mysql调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中

4.在从服务器上启动从服务器进程时产生的信息

• 在复制环境下，从服务器进程的信息也会被记录进错误日志

2.3 例子

通过AI生成的，大家能理解就行。

------------------------ MySQL Error Log ------------------------

# 第一个错误：插件初始化失败
2023-10-05T14:32:15.123456Z 1 [ERROR] [MY-010323] [Server] Plugin 'auth_socket' init function returned error.
# 这条错误消息表示'auth_socket'插件在初始化过程中失败。这通常与插件文件缺失、配置错误或权限不足有关。

# 第二个错误：端口冲突
2023-10-05T14:32:15.123457Z 1 [ERROR] [MY-010020] [Server] Can't start server: Bind on TCP/IP 

```sql
port: Address already in use
# 这条错误消息表明MySQL服务器无法启动，因为TCP/IP端口（默认为3306）已被其他进程占用。

# 第三个错误：服务器中止
2023-10-05T14:32:15.123458Z 1 [ERROR] [MY-010119] [Server] Aborting
# 由于前面的错误，MySQL服务器中止了启动过程。

# 新增的InnoDB错误：表空间损坏
2023-10-05T14:33:00.654321Z 1 [ERROR] [MY-011014] [InnoDB] Tablespace is missing or corrupted.
# 这条错误消息表示InnoDB表空间文件丢失或损坏。InnoDB表空间是存储表和索引的地方，如果损坏，可能导致数据丢失或无法访问。

------------------------ Detailed Error Information ------------------------

# 对第一个错误的详细解释
**Error 1: Plugin Initialization Failure**
- **Time**: 2023-10-05T14:32:15.123456Z
- **Error Code**: [MY-010323]
- **Description**: The 'auth_socket' plugin failed to initialize. This plugin is responsible for authentication and may be missing or corrupted.
- **Potential Causes**:
  - The plugin file is missing or damaged.
  - The plugin configuration in the MySQL configuration file is incorrect.
  - Insufficient permissions to load the plugin.

# 对第二个错误的详细解释
**Error 2: Port Conflict**
- **Time**: 2023-10-05T14:32:15.123457Z
- **Error Code**: [MY-010020]
- **Description**: MySQL cannot start because the TCP/IP port (default is 3306) is already in use by another process.
- **Potential Causes**:
  - Another MySQL server instance is already running.
  - A non-MySQL service is using the port.
  - The MySQL configuration file specifies an incorrect or already-used port.

# 对第三个错误的简要解释（由于是基于前两个错误的中止，所以解释较为简略）
**Error 3: Server Abort**
- Due to the preceding errors, MySQL has aborted the startup process.

# 对新增InnoDB错误的详细解释
**Error 4: InnoDB Tablespace Corruption**
- **Time**: 2023-10-05T14:33:00.654321Z
- **Error Code**: [MY-011014]
- **Description**: InnoDB tablespace is missing or corrupted, which may lead to data loss or inability to access tables.
- **Potential Causes**:
  - Hardware failure or corruption during normal operations.
  - Incorrect shutdown or crash of the MySQL server.
  - Bug in the InnoDB storage engine.

------------------------ Recommended Actions ------------------------

# 对于插件初始化失败的推荐行动
1. **Check Plugin Status**:
   - Verify that the 'auth_socket' plugin file exists in the plugin directory.
   - Check the MySQL configuration file for correct plugin configuration.
   - Ensure that the MySQL server has sufficient permissions to load the plugin.

# 对于端口冲突的推荐行动
2. **Resolve Port Conflict**:
   - Use the `netstat` or `ss` command to identify the process using port 3306.
   - Stop the conflicting service or change the MySQL port in the configuration file.
   - Restart the MySQL service after making any changes.

# 对于服务器中止的推荐行动（通常需要先解决前面的错误）
3. **Address Preceding Errors**:
   - Fix the errors that led to the server abort.
   - Restart the MySQL service once all issues are resolved.

# 对于InnoDB表空间损坏的推荐行动
4. **Handle InnoDB Tablespace Corruption**:
   - Take the server offline to prevent further corruption.
   - Attempt to restore from a backup if available.
   - Run InnoDB recovery tools if no backup is available, but note that this may not always be successful.
   - Consider consulting a database recovery service if data is critical and recovery is difficult.

------------------------ End of MySQL Error Log ------------------------

三、 查询日志

查询日志在MySQL中被称为general log(通用日志)，查询日志里的内容不要被"查询日志"误导，认为里面只存储select语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

• insert查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错
• update时也会查询因为更新的时候很可能会更新某一块数据
• delete查询，只删除符合条件的数据

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的select语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，所以，我们可以根据自己的实际情况来决定是否开启查询日志。

3.1 MySQL查询日志配置

所以如果你要判断MySQL数据库是否开启了查询日志，可以使用下面命令。general_log为ON表示开启查询日志，OFF表示关闭查询日志。

mysql> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

如果开启了查询日志，参数log_output控制着查询日志的存储方式， log_output可以设置为以下4种值:

• FILE : 表示日志存储在文件中

• TABLE : 表示日志存储在mysql库中的general_log表中

• FILE, TABLE : 表示将日志同时存储在文件和general_log表中，改值会徒增很多IO压力，一般不会这样设置

• NONE : 表示不记录日志，即使general_log设置为ON， 如果log_output设置为NONE，也不会记录查询日志

log_output不仅控制查询日志的输出，也控制着慢查询日志的输出，例如：
log_output设置为FILE，就表示查询日志和慢查询日志都存放在文件中，设置为TABLE，查询日志和慢查询日志都存放在mysql库中的general_log表中

查看log_output设置:

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

3.2 开启及关闭MySQL查询日志

• 方法1: 在配置文件中设置(不推荐)

#可以在my.cnf里添加,1开启（0关闭）,当然了,这样要重启才能生效,有点多余了
general-log = 1
log_output='table'
然后重启MySQL实例

• 方法2 : 通过命令设置

#也可以设置变量那样更改,1开启（0关闭）,即时生效,不用重启,首选当然是这样的了
set global general_log=1
set global log_output='table';

通过该方式设置，MySQL实例重启后，相关配置又恢复到默认值。如果只是短暂时间内使用，推荐使用命令行方式

3.3 查看查询日志信息

cat /usr/local/mysql/data/mysql_general.log 
#信息如下
2024-12-23T10:53:44.354216Z         8 Connect   root@localhost on  using Socket
2024-12-23T10:53:44.354828Z         8 Query     select @@version_comment limit 1
2024-12-23T10:53:48.702146Z         8 Query     select * from mysql.general_log
2024-12-23T10:55:23.044868Z         8 Query     show variables like '%general_log%'
2024-12-23T10:56:24.181179Z         8 Query     SELECT DATABASE()
2024-12-23T10:56:24.181422Z         8 Init DB   a
2024-12-23T10:56:24.182290Z         8 Query     show databases
2024-12-23T10:56:24.184544Z         8 Query     show tables
2024-12-23T10:56:24.187035Z         8 Field List        a1 
2024-12-23T10:57:59.015923Z         8 Query     select * from mysql.general_log
2024-12-23T10:58:43.596296Z         8 Query     CREATE TABLE shiyan ( event_time TIMESTAMP NOT NULL, user_host VARCHAR

3.4 修改查询日志名称或位置

mysql> show variables like 'general_log%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.01 sec)

mysql> set global general_log='OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log_file='/usr/local/mysql/data/mysql-general.log';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'general_log%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | ON                                      |
| general_log_file | /usr/local/mysql/data/mysql-general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)
2 rows in set (0.00 sec)

四、 慢查询日志

慢日志全称为慢查询日志（Slow Query Log），主要用来记录在 MySQL 中执行时间超过指定时间的 SQL 语句。通过慢查询日志，可以查找出哪些语句的执行效率低，以便进行优化。

默认情况下，MySQL 并没有开启慢日志，可以通过修改 slow_query_log 参数来打开慢日志。

4.1 MySQL慢日志相关的参数：

• low_query_log：是否启用慢查询日志，默认为0，可设置为0、1，1表示开启。
• slow_query_log_file：指定慢查询日志位置及名称，默认值为host_name-slow.log，可指定绝对路径。
• long_query_time：慢查询执行时间阈值，超过此时间会记录，默认为10，单位为s。
• log_output：慢查询日志输出目标，默认为file，即输出到文件。
• log_timestamps：主要是控制 error log、slow log、genera log
  日志文件中的显示时区，默认使用UTC时区，建议改为 SYSTEM 系统时区。
• log_queries_not_using_indexes：是否记录所有未使用索引的查询语句，默认为off。
• min_examined_row_limit：对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认为0。
• log_slow_admin_statements：慢速管理语句是否写入慢日志中，管理语句包含 alter table、create
  index 等，默认为 off 即不写入。

一般情况下，我们只需开启慢日志记录，配置下阈值时间，其余参数可按默认配置。对于阈值时间，可灵活调整，比如说可以设置为 1s 或 3s 。‍

mysql>  show variables like "%slow%";
+-----------------------------+--------------------------------------------+
| Variable_name               | Value                                      |
+-----------------------------+--------------------------------------------+
| log_slow_admin_statements   | OFF                                        |
| log_slow_extra              | OFF                                        |
| log_slow_replica_statements | OFF                                        |
| log_slow_slave_statements   | OFF                                        |
| slow_launch_time            | 2                                          |
| slow_query_log              | ON                                         |
| slow_query_log_file         | /usr/local/mysql/data/mysql_slow_query.log |
+-----------------------------+--------------------------------------------+
7 rows in set (0.01 sec)

4.2 慢查询日志分析工具 pt-query-digest

pt-query-digest 是分析MySQL查询日志最有力的工具，该工具功能强大，它可以分析binlog，Generallog，slowlog，也可以通过show processlist或者通过 tcpdump 抓取的MySQL协议数据来进行分析，比 mysqldumpslow 更具体，更完善。以下是使用pt-query-digest的示例:

• 直接分析慢查询文件
  pt-query-digest slow.log > slow_report.log

该工具可以将查询的剖析报告打印出来，可以分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间，次数，占比等，可以借助分析结果找出问题进行优化。

五、事务日志

数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。

数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。

但是万一数据库发生断电，因为缓存的数据没有写入磁盘，导致缓存在内存中的数据丢失而导致数据不一致怎么办？

为了保证事务的ACID特性，就不得不说MySQL InnoDB引擎的事务日志: 重做日志redo和回滚日志undo

innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

注: 在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切

5.1 Redo 日志

redo log参数

• innodb_log_files_in_group：redo log文件的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认2个，最大100个。

• innodb_log_file_size：文件设置大小，默认值为 48M，最大值为512G，注意最大值指的是整个 redolog系列文件之和。

• innodb_log_group_home_dir：文件存放路径

• innodb_log_buffer_size：Redo Log 缓存区，默认8M，可设置1-8M

• innodb_flush_log_at_trx_commit:3个值，具体如下

innodb_flush_log_at_trx_commit=0

事务发生过程，日志一直激励在redo log buffer中，跟其他设置一样
但是在事务提交时，不产生redo 写操作，而是MySQL内部每秒操作一次
从redo log buffer，把数据写入到系统中去。如果发生crash，即丢失1s内的事务修改操作

innodb_flush_log_at_trx_commit=1
每次commit都会把redo log从redo log buffer写入到system，并fsync刷新到磁盘文件中。

innodb_flush_log_at_trx_commit=2

每次事务提交时MySQL会把日志从redo log buffer写入到system
但只写入到file system buffer，由系统内部来fsync到磁盘文件。
如果数据库实例crash，不会丢失redo log
但是如果服务器crash，由于file system buffer还来不及fsync到磁盘文件，所以会丢失这一部分的数据。

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

• 如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
• 总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

5.2 redo log介绍

redo log 属于 MySQL 存储引擎 InnoDB 的事务日志。

MySQL 的数据是存放在磁盘中的，每次读写数据都需做磁盘 IO 操作，如果并发场景下性能就会很差。为此 MySQL 提供了一个优化手段，引入缓存 Buffer Pool。这个缓存中包含了磁盘中部分数据页（page）的映射，以此来缓解数据库的磁盘压力。

当从数据库读数据时，首先从缓存中读取。如果缓存中没有，则从磁盘读取后放入缓存；当向数据库写入数据时，先向缓存写入，此时缓存中的数据页数据变更，这个数据页称为脏页，Buffer Pool 中修改完数据后会按照设定的更新策略，定期刷到磁盘中，这个过程称为刷脏页。

如果刷脏页还未完成，可 MySQL 由于某些原因宕机重启。此时 Buffer Pool 中修改的数据还没有及时的刷到磁盘中，就会导致数据丢失，无法保证事务的持久性。

为了解决这个问题，引入了redo log。它记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。

redo log 用到了 WAL（Write-Ahead Logging）技术。这个技术的核心就在于修改记录前，一定要先写日志，并保证日志先落盘，才能算事务提交完成。

有了 redo log 再修改数据时，InnoDB 引擎会把更新记录先写在 redo log 中，在修改 Buffer Pool 中的数据。当提交事务时，调用 fsync 把 redo log 刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理。

注意：此时 redo log 的事务状态是 prepare，还未真正提交成功，
要等 bin log 日志写入磁盘完成才会变更为 commit，事务才算真正提交完成。

这样一来，即使刷脏页之前 MySQL 意外宕机也没关系。只要在重启时解析 redo log 中的更改记录进行重放，重新刷入磁盘即可。

5.3 undo日志

undo log 也是属于 MySQL 存储引擎 InnoDB 的事务日志。

undo log 属于逻辑日志，如其名主要起到回滚的作用，它是保证事务原子性的关键。记录的是数据修改前的状态，在数据修改的流程中，同时会记录一条与当前操作相反的逻辑日志到 undo log 中。

这样当某些原因导致服务异常事务失败，就可以借助 undo log 将数据回滚到事务执行前的状态，保证事务的完整性。

那可能有人会问：同一个事务内的一条记录被多次修改，那是不是每次都要把数据修改前的状态都写入 undo log 呢？

答案是不会的！

undo log 只负责记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改，所产生的修改记录会写入到 redo log。undo log 负责完成回滚，redo log 负责完成前滚。

未提交的事务，即事务未执行 commit。但该事务内修改的脏页中，可能有一部分脏块已经刷盘。如果此时数据库实例宕机重启，就需要用回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

未完全提交的事务，即事务已经执行 commit，但该事务内修改的脏页中只有一部分数据被刷盘，另外一部分还在 buffer pool 缓存上，如果此时数据库实例宕机重启，就需要用前滚来完成未完全提交的事务。将先前那部分由于宕机在内存上的未来得及刷盘数据，从 redo log 中恢复出来并刷入磁盘。

数据库实例恢复时，先做前滚，后做回滚。

undo log、redo log、bin log 三种日志都是在刷脏页之前就已经刷到磁盘了，相互协作最大限度保证了用户提交的数据不丢失。

undo log参数

• innodb_max_undo_log_size：控制最大undo tablespace文件的大小，该值默认大小为1G，truncate后的大小默认为10M。
• innodb_undo_tablespaces：设置undo独立表空间个数，范围为0-128，默认为0，0表示表示不开启独立undo表空间 且 undo日志存储在ibdata 文件中。
• innodb_undo_log_truncate：
  InnoDB的purge线程根据innodb_undo_log_truncate设置开启或关闭、innodb_max_undo_log_size的参数值，以及truncate的频率来进行空间回收和 undo file 的重新初始化。该参数生效的前提是，已设置独立表空间且独立表空间个数大于等于2个。
• innodb_purge_rseg_truncate_frequency：用于控制purge回滚段的频度，默认为128。

六、二进制日志

6.1 二进制简介

MySQL的二进制日志（binary log）是一个二进制文件，主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。

6.2 binlog的作用

• 恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。

• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外，binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。

在开启binlog的情况下，为了保证binlog与redo的一致性，MySQL将采用事务的两阶段提交协议。当MySQL系统发生崩溃时，事务在存储引擎内部的状态可能为prepared和commit两种。

对于prepared状态的事务，是进行提交操作还是进行回滚操作，这时需要参考binlog：如果事务在binlog中存在，那么将其提交；如果不在binlog中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

6.3 查看二进制日志

• 使用mysqlbinlog工具：这是查看二进制日志的标准工具。可以通过命令行使用mysqlbinlog命令查看日志内容。

 mysqlbinlog  --start-datetime="2024-12-23 00:00:00" 
 --stop-datetime="2024-12-24 23:59:59" /binlog日志的路径/mysql-bin.000001

• 在MySQL命令行中：可以使用SHOW BINARY LOGS;命令查看所有二进制日志文件的列表。

6.4 binlog的开启

如果想开启binlog，默认关闭，可以在MySQL配置文件中通过配置参数log-bin = [base-name]启动二进制日志。

如果不指定base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如mysql-bin.000001，所在目录为数据库所在目录（datadir）。顺序说一下，对于二进制文件当满足下面三种情况时会创建新的文件，文件后缀会自增。

你可能会有顾虑，当文件后缀从000001增长到999999时会怎样？有网友测试过，当文件达到999999时又会回到000001，并不会有什么异常。

6.5 binlog格式

binlog格式分为: STATEMENT、ROW和MIXED三种，详情如下:

• STATEMENT

  • Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。
  • 但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。
  • 比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。

• ROW

  • Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

  • 不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

  • 现在对于ROW格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据，所以可以基于这种模式做一些DBA工具，比如数据恢复，不同数据库之间数据同步等。

• MIXED

  • 在 Mixed 模式下，系统会自动判断该用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。

  • Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。

6.6 binlog的相关参数

• max_binlog_size：可以通过max_binlog_size参数来限定单个binlog文件的大小（默认1G）

对于binlog文件的大小，有个需要注意的地方是，binlog文件可能会大于max_binlog_size参数设定的阈值。

由于一个事务所产生的所有事件必须记录在同一个binlog文件中，所以即使binlog文件的大小达到max_binlog_size参数指定的大小，也要等到当前事务的所有事件全部写入到binlog文件中才能切换，这样就会出现binlog文件的大小大于max_binlog_size参数指定的大小的情况。

• binlog_cache_size

当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。

此外，binlog_cache_size是基于会话（session）的。也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。

当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。

通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

• sync_binlog

参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。
如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新binlog到磁盘；

sync_binlog为0表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；sync_binlog为N表示每N个事务提交会进行一次binlog刷新。

如果使用Innodb存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为1。不过该值为1时，确时会对数据库IO系统带来一定的开销。

但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生。

当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。

如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，由于COMMIT操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。

对于这个问题，MySQL使用了两阶段提交来解决的，简单说就是对于已经写入到binlog文件的事务一定会提交成功， 而没有写入到binlog文件的事务就会进行回滚，从而保证二进制日志和InnoDB存储引擎数据文件的一致性，保证主从复制的安全。

• binlog-do-db&binlog-ignore-db

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的二进制日志。默认为空，表示需要同步所有库的日志到二进制日志。

• log-slave-update

如果当前数据库是复制中的slave角色，则它不会将master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置log-slave-update。如果需要搭建master–>slave–>slave架构的复制，则必须设置该参数。

• binlog-format

binlog_format参数十分重要，用来设置二进制日志的记录格式,详情参考(6.5 binlog格式)

• log_bin_trust_function_creators

默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建。

七、中继日志(relay log)

7.1 什么是relay log？

Relay log，一般叫做中继日志，一般情况下它在MySQL主从同步读写分离集群的从节点才开启。主节点一般不需要这个日志。

master主节点的binlog传到slave从节点后，被写到relay log里，从节点的slave sql线程从relaylog里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

它的作用可以参考如下图，从图片中可以看出，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容，它里面的内容和master节点的binlog日志里面的内容是一致的。然后slave从节点从这个relaylog日志文件中读取数据应用到数据库中，来实现数据的主从复制。
​

7.2 relay log相关参数

• max_relay_log_size：relaylog日志文件的最大值，默认为0，表示和binlog日志文件大小一样。如果不为0，表示relaylog日志文件的最大值就是设置的对应的大小，该参数的单位为byte。

• relay_log：设置的relaylog的日志文件路径和名称。如果不设置路径，只是设置文件名，则路径是在datadir参数所指向的磁盘目录。如果设置了目录，则会把relaylog写在对应的目录下。如果设置了目录，则要注意这个目录需要让MySQL有对应的读写的权限才可以，否则启动MySQL数据库会失败。

• relay_log_basename：relaylog日志文件的基础名称，就是去掉文件后缀之后的文件名称。

• relay_log_index：relaylog日志的索引文件，里面记录了所有当前有效的relaylog日志文件列表。可以指定这个索引文件的名称和存储目录，不过我们一般配置它和relaylog在同一目录下。

• relay_log_info_file：这个文件里面，记录当前正在使用到的relaylog的文件名称和日志文件中的位置偏移量，以及master节点上面的binlog日志偏移量。这个文件的名称为relay-log.info，它的目录和relaylog在同级目录下。

• relay_log_info_repository：这个参数标识着上面的relay_log_info_file中的信息是记录在磁盘目录上，还是记录在表中。它有两个取值范围，分别是FILE或者TABLE。

7.3 查看中继日志

如何查看中继日志不像二进制日志那样直接被查看，因为它们通常不包含可读的SQL语句。但是，可以通过以下方式间接查看中继日志的内容：

• 使用mysqlbinlog工具：虽然中继日志的内容不是直接可读的，但可以使用mysqlbinlog工具来查看中继日志的元数据。

• 查看复制状态：通过SHOW SLAVE STATUS;命令，可以查看从服务器的复制状态，包括中继日志的文件和位置。

​以上就是今天分享的内容了，如果对您有帮助，请多多支持，谢谢！