【MySQL】MySQL日志文件总结

MySQL数据库中根据日志文件的不同，可分为以下几种，下面将分别介绍。

一 错误日志

1 描述

记录MySQL的启动、停止信息以及在MySQL运行过程中的错误信息，类似于Oracle数据库的alert log，默认是开启。错误日志文件一般存放在数据目录下，以error.log作为文件名的结尾。

错误日志不光记录着是错误的信息，在MySQL 5.7初始化数据库中，加上--initialize参数，会生成一个临时的数据库初始密码，记录在log-error中。

2 参数

[mysqld]中的参数：

1）log_err（默认开启）

log_error=[path/[file_name]]，如果不指定文件名，则默认hostname.err，修改后重启生效；

mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.02 sec)

2）log_warnings

该参数在5.7.2之后被不建议使用，取而代之的是log_error_verbosity；

3）log_error_verbosity

该参数有以下几种值选项，分别为：

• 1：仅仅记录错误；
• 2：记录错误和警告；
• 3：默认值，记录错误、警告和普通日志；

3 试验

1）查看错误日志

mysql> show variables like 'log_error';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| log_error | ./strong.oracle.com.err |
+---------------+-------------------------+
1 row in set (0.01 sec)

2）错误日志维护

可以直接删除错误日志文件，删除后，错误日志不会自动重建，需要flush logs或者重启数据库。

[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 79058 May 11 10:14 strong.oracle.com.err
[root@strong mydb]# rm strong.oracle.com.err
rm: remove regular file `strong.oracle.com.err'? y
[root@strong mydb]# ll strong.oracle.com.err
ls: cannot access strong.oracle.com.err: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ll strong.oracle.com.err
-rw-r-----. 1 mysql mysql 0 May 11 10:39 strong.oracle.com.err

注：也可以使用mysql> flush logs;重建错误日志。

二 常规日志

1 描述

由log moudle写入，记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。包括错误的记录，mysqld的所有动作，默认关闭。

平时不开启常规日志，出现问题时候再开启，防止大量日志充满空间。

2 参数

[mysqld]中的参数：

1）general_log

默认关闭，{0-关闭，1-开启}，可以在线打开和关闭；

2）general_log_file

指定日志文件名称和路径；

3）log_output

该参数有如下选项，分别为：

• TABLE：便于统计写入一个CSV引擎的表mysql.general_log中；
• FIFLE：默认值，存储在文件；
• TABLE,FILE：同时存放在表和文件中；
• NONE：不存放日志；

注：如果log_output=NONE，但是general_log为1，则默认不写常规日志；

4）sql_log_off

默认值为OFF，设置该值为ON，则不写常规日志；

3 试验

1）查看常规日志设置

mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | OFF |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.01 sec)
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.02 sec)

2）打开常规日志

mysql> set global general_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /u01/data/mydb/strong.log |
+------------------+---------------------------+
2 rows in set (0.02 sec)

3）查看常规日志内容

[root@strong mydb]# more strong.log
/usr/local/mysql/bin/mysqld, Version: 5.7.25 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /u01/data/mydb/3306.sock
Time Id Command Argument
2019-05-11T02:57:09.481561Z 2 Query show variables like 'general%'
2019-05-11T02:58:34.571239Z 2 Query show databases
2019-05-11T02:58:48.788272Z 2 Query SELECT DATABASE()
2019-05-11T02:58:48.788990Z 2 Init DB mysql
2019-05-11T02:58:52.107128Z 2 Query show tables

4）常规日志维护

[root@strong mydb]# rm strong.log
rm: remove regular file `strong.log'? y
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong.log
strong.log

注：如果设置为常规日志存储在TABLE，也可从表general_log查看常规日志。

三 慢查询日志

1 描述

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值（long_query_time、min_examined_row_limit、log_slow_admin_statements和log_queries_not_using_indexes）的语句，默认关闭。

注意：慢查询日志不会计算语句在开始执行之前等待锁的时间（initial slow query），mysqld只会从语句等待的锁释放掉，并开始执行开始计算时间（执行开始后的锁定时间会记录）。所以慢查询日志中的语句顺序和语句的发出顺序或者general log的顺序不同。

2 参数

[mysqld]中的参数：

1）slow_query_log

默认关闭，{0-关闭，1-开启}，可以在线打开和关闭；

2）slow_query_log_file

指定慢查询日志的名称和位置；

3）long_query_time

设置SQL执行的阈值；

4）log_output

该参数有如下选项，分别为：

• TABLE：便于统计写入一个CSV引擎的表mysql.slow_log中；
• FIFLE：默认值，存储在文件；
• TABLE,FILE：同时存放在表和文件中；
• NONE：不存放日志；

注：如果log_output=NONE，但是slow_query_log为1，则默认不写慢查询日志；

5）log_queries_not_using_indexes

默认值为OFF，记录没有用索引的查询；

6）log_throttle_queries_not_using_indexes

记录没有使用索引查询的语句条数在一分钟内记录的条数；

注：如何消除上线后未使用索引查询的隐患，线上开启5秒即可。

7）min_examined_row_limit

要检查的行数大于等于N时才记录为慢查询，前提是必须满足long_query_time和log_queries_not_using_indexes约束；

8）log_slow_admin_statements

管理语句是否记录，比如:ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE和REPAIR TABLE。

9）log_slow_slave_statements

从库的语句执行是否记录；

3 试验

1）开启慢查询

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

2）查看慢查询日志

注：慢查询日志可以使用percona-toolkit工具的pt-query-digest查看。

3）慢查询日志维护

[root@strong mydb]# rm strong-slow.log
rm: remove regular file `strong-slow.log'? y
[root@strong mydb]# ls strong-slow.log
ls: cannot access strong-slow.log: No such file or directory
[root@strong mydb]# mysqladmin -uroot -pmysql -S /u01/data/mydb/3306.sock flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@strong mydb]# ls strong-slow.log
strong-slow.log

四 二进制日志

1 描述

以“事件”的形式保存，描述数据的更改，包含关于每个更新数据库的语句的执行时间。

二进制日志的主要作用：

• 主从复制：在主服务器上把所有修改数据的操作记录到binlog中，通过网络发送给从服务器，从而达到主从同步；
• 恢复操作：数据可以通过binlog日志，使用mysqlbinlog命令，实现基于时间点和位置的恢复操作；

二进制日志记录在语句或事务完成之后立即执行（而不是在任何锁释放或任何提交完成之前写入），这确保日志以提交顺序记录。

• 对于不支持事务的表，执行之后马上会被记录到二进制日志中去；
• 对于未提交的事务中，更改事务表（例如InnoDB表）的所有更新（UPDATE、DELETE或INSERT）将被缓存，直到服务器收到COMMIT语句，在这一点上，mysqld在执行COMMIT之前将整个事务写入二进制日志；
• 对非事务性表的修改无法回滚。如果回滚的事务包括对非事务性表的修改，则会在最后使用ROLLBACK语句记录整个事务，以确保对这些表的修改进行复制。

注：binlog只记录完整事务。

启用二进制日志选项数据库性能会降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。

2 参数

[mysqld]中的参数：

1）log_bin

默认关闭，{0-关闭，1-开启}，不可以在线打开和关闭；

2）log_bin_basename

不指定路径则为hostname-bin.00000N；

3）log_bin_index

不指定路径则为hostname-bin.index；

注：文件路径需要在log_bin中设置，而不是log_bin_basename，所有的日志都建议自己起名字，不要用默认。

4）binlog_cache_size

内存中缓存二进制的缓冲区大小，所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓存中的binlog写入二进制日志文件，该缓存大小由该参数决定，默认大小为32K，并且binlog_cache_size是基于会话的，也就是当一个线程要开始一个事务时，MySQL会自动分配一个该值大小的缓存，如果一个语句大于这个值，线程将打开一个临时文件来存储事务，当线程结束时，临时文件被删除。

注：对于大的事务，二进制日志会超过max_binlog_size设定的值，但事务仅仅写入一个二进制日志。设置此值需小心，如果设置太小就使用磁盘上的临时文件来记录，可通过show global status命令查看binlog_cache_use和binlog_cache_disk_use的使用情况，来判断当前的binlog_cache_size的设置是否合适。

binlog_cache_use状态变量显示用于存储语句的此缓冲区（可能还有一个临时文件）的事务数；

binlog_cache_disk_use状态变量显示有多少这些事务实际上必须使用临时文件；

这两个变量可用于将binlog_cache_size调整到足够大的值，以避免使用临时文件。

5）max_binlog_size

二进制日志的大小，该参数指定了单个binlog的最大值，如果超过该值就会自动生成新的binlog文件（重启MySQL实例也会生成新的binlog）；

6）max_binlog_cache_size

限制用于缓存多语句事务的总大小，如果一个事务大于这个值，它将失败并回滚；

7）sync_binlog

{0，1}二进制内容从缓冲区落入磁盘的时机。

5.7.7以后，sync_binlog参数被默认设置为1，减少了binlog中的不一致可能性。

8）expire_logs_days

日期过期时间，一定要和备份相结合；

9）binlog_format

binlog模式，有以下几种：

• 基于语句：当时语句的原样写入；
• 基于行：只记录受影响的行；
• 混合模式：默认用语句模式，遇到下面请看用基于行模式：
  • uuid()；
  • UDF；
  • temporary；
  • rows_found/row_count；
  • user()/current_user()；
  • LOAD_FILE()；

MySQL中的复制功能最初是基于从主库到从库的SQL语句的传输，称为基于语句的日志记录，可以使用--binlog-format=STATEMENT启动服务器使用此格式；

在基于行的日志记录中，主库将事件写入二进制日志，记录表中各个行如何被改变，因此，重要的是，表始终使用主键来确保可以有效的识别行，可以使用--binlog-format=ROW启动基于行的日志记录；

注：在MySQL 5.7.7之前，基于语句的日志记录格式是默认的，在MySQL 5.7.7及更高的版本中，基于行的记录格式是默认的。

使用混合日志记录（MIXED），默认使用基于语句的日志记录，但是如下所述，在某些情况下，记录模式将自动切换到基于行的记录，可以使用--binlog-format=MIXED启动基于混合模式的日志记录。

使用基于语句的二进制日志记录，主机将执行的查询写入二进制日志，这是一种非常快速、紧凑、高效的记录方法，在大多数情况下没有问题，然而，如果查询的设计方式使得数据修改是非确定性的，主从库的数据可能会变得不同。

3 试验

1）查看二进制日志配置

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)

2）开启二进制日志

在my.cnf配置文件增加下面两行记录，保存然后重启MySQL实例：

log_bin=/u01/data/mydb/binlog/mysql-bin

log_bin_index=/u01/data/mydb/binlog/mysql-bin.index

3）查看二进制日志的格式

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.02 sec)

注：修改二进制日志格式需要super权限；

4）查看二进制日志内容

可使用mysqlbinlog工具：

• 审计SQL语句和数据库的更改；
• 定点恢复；

具体参考：启用MySQL的Binlog

5）查看二进制日志

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)

6）切换二进制日志

mysql> flush logs;
Query OK, 0 rows affected (0.14 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)

7）清理所有二进制日志，并从新开始记录二进制日志

mysql> reset master;
Query OK, 0 rows affected (0.13 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)

8）清理指定编号之前的二进制日志

mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.04 sec)

9）清理指定时间之前的二进制日志

mysql> purge binary logs before '2019-05-11 17:38:56';
Query OK, 0 rows affected (0.16 sec)

注：不建议手动rm删除二进制日志，会造成index文件不一致。

五 中继日志

主从复制中，从服务器上一个很重要的文件，从服务器IO线程将主服务器的二进制日志读取过来并记录到从服务器本地文件（relay log）中，然后从服务器上的SQL线程会读取中继日志的内容并应用到从服务器。