MySQL 日志

本文最后更新于：2024年3月18日 凌晨

MySQL 日志

  • Mysql 有4种类型的日志：Error Log,Genaral Query Log,Binary Log 和 Slow Query Log

Error Log

  • 记录Mysql运行过程中的Error,Warning,Note等信息，系统出错或者某条记录出问题可以查看Error日志。
  • Mysql的错误日志默认以${hostname}.err存放在Mysql的日志目录，可以通过以下语句查看：
1
2
3
4
5
6
mysql> show variables like "log_error";
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| log_error     | /tmp/mysql.log |
+---------------+---------------
  • 错误日志配置：/etc/my.cnf中添加log-error = ${filename}来开启MySQL错误日志。
1
log_error = /tmp/mysql.log
  • 查看错误日志。
1
2
3
4
5
6
7
8
9
10
11
bash-3.2# tail -f /tmp/mysql.log
2015-12-23T02:22:41.467311Z 0 [Note] IPv6 is available.
2015-12-23T02:22:41.467324Z 0 [Note]   - '::' resolves to '::';
2015-12-23T02:22:41.467350Z 0 [Note] Server socket created on IP: '::'.
2015-12-23T02:22:41.584287Z 0 [Note] Event Scheduler: Loaded 0 events
2015-12-23T02:22:41.584390Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: ready for connections.
Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew
2015-12-23T02:22:42.540786Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151223 10:22:42
151223 10:22:51 mysqld_safe A mysqld process already exists
2015-12-23T02:25:30.984395Z 2 [ERROR] Could not use /tmp/mysql_query.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL GENERAL_LOG=ON" or restart the MySQL server.
2015-12-23T07:28:03.923562Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 61473ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

General Query Log

  • 记录MySQL的日常日志，包括查询，修改，更新等的每条sql
  • 查看MySQL是否启用了查询日志。
1
2
3
4
5
6
7
8
mysql> show global variables like "%general%";
+----------------------------------------+----------------------+
| Variable_name                          | Value                |
+----------------------------------------+----------------------+
| general_log                            | OFF                  |
| general_log_file                       | /tmp/mysql_query.log |
+----------------------------------------+----------------------+
2 rows in set (0.00 sec)
  • 查询日志配置：/etc/my.cnf中添加general-log-file = ${filename}
1
log_bin = /tmp/mysql_query.log
  • Mysql打开general log日志后，所有的查询语句都可以在general log文件中输出，如果打开，文件会非常大，建议调试的时候打开，平时关闭。
1
2
3
4
5
mysql> set global general_log = on;
Query OK, 0 rows affected (0.01 sec)

mysql> set global general_log = off;
Query OK, 0 rows affected (0.01 sec)
  • 注意：如果打开了日志功能，但是没有写入日志，那就有可能是MySQL对日志文件的权限不够，所以需要指定权限。
1
chown mysql:mysql /tmp/mysql_query.log

Binary Log

  • 二进制日志，包含一些事件，这些事件描述了数据库的改动，如建表，数据改动等，主要用于备份恢复，回滚操作等。
  • 作用
    • 包含了所有更新了数据或者已经潜在更新了数据（比如没有匹配任何行的一个DELETE)
    • 包含关于每个更新数据库（DML）的语句的执行时间信息。
    • 不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能。
    • 主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新。
    • 用于在主复制服务器上记录所有将发送给从服务器的语句。
    • 启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。
  • 格式:Binlog有3种格式。
    • STATMENT：每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。
      • 优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能，因为他只需要记录在master上所执行的语句的细节以及执行语句时候的上下文信息。
      • 缺点
        • 在statement模式下，由于是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。
        • 由于MySQL现在发展比较快，很多的新功能不断的加入，使MySQL的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现，在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。
    • ROW：日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。
      • 优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和触发无法被正确复制问题。
      • 缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
    • MIXED:MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

配置

  • 查看mysql中二进制文件的配置情况。
1
2
3
4
5
6
7
8
9
10
11
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    |
+---------------------------------+-------+
  • 二进制日志配置： /etc/my.cnf添加log_bin = ${filename}开启binlog
1
log_bin = /tmp/mysql-bin.log

  • log_bin：设置此参数表示启用binlog功能，并指定路径名称。

  • log_bin_index：设置此参数是指定二进制索引文件的路径与名称。

  • binlog_do_db：此参数表示只记录指定数据库的二进制日志。

  • binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志。

  • max_binlog_cache_size：此参数表示binlog使用的内存最大的尺寸。

  • binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

  • binlog_cache_use：使用二进制日志缓存的事务数量。

  • binlog_cache_disk_use：使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。

  • max_binlog_size:binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。

  • sync_binlog：这个参数直接影响MySQL的性能和完整性。

    • sync_binlog=0：当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
    • sync_binlog=n：在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
    • Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的，一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失。

  • 重启MySQL

1
2
3
$ mysql.server start
Starting MySQL
ERROR! The server quit without updating PID file (/usr/local/Cellar/mysql/5.7.9/data/mysql.pid).
  • 报错，查看错误日志，在/tmp/mysql.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
151224 00:37:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2015-12-23T16:37:34.643998Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-23T16:37:34.644124Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2015-12-23T16:37:34.644129Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2015-12-23T16:37:34.644189Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-12-23T16:37:34.644226Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld (mysqld 5.7.9-log) starting as process 24268 ...
2015-12-23T16:37:34.646468Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2015-12-23T16:37:34.646945Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2015-12-23T16:37:34.646978Z 0 [ERROR] Aborting

2015-12-23T16:37:34.646991Z 0 [Note] Binlog end
2015-12-23T16:37:34.647068Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: Shutdown complete

151224 00:37:34 mysqld_safe mysqld from pid file /usr/local/Cellar/mysql/5.7.9/data/mysql.pid ended
  • You have enabled the binary log, but you haven’t provided the mandatory server-id. Please refer to the proper server start-up parameters documentation：说明需要配置一个server-id
  • 在配置文件/etc/my.cn中添加server-id = 1，再重启MySQL，解决问题，而且在配置的bin-log同级目录增加了mysql-bin.000001,mysql-bin.index,mysql-bin.log三个文件，前两个是自动生成。
  • 登录MySQL，再次查看bin-log的状态，属于启用状态。
1
2
3
4
5
6
7
8
9
10
11
mysql> show variables like "%log_bin%";
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| log_bin                         | ON                   |
| log_bin_basename                | /tmp/mysql-bin       |
| log_bin_index                   | /tmp/mysql-bin.index |
| log_bin_trust_function_creators | OFF                  |
| log_bin_use_v1_row_events       | OFF                  |
| sql_log_bin                     | ON                   |
+---------------------------------+----------------------+

删除日志

  • binlog的删除可以手动删除或自动删除。

自动删除binlog

  • 通过binlog参数（expire_logs_days）来实现mysql自动删除binlog
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       869 |
+------------------+-----------+
1 row in set (0.00 sec)

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

mysql> set global expire_logs_days=3;

Query OK, 0 rows affected (0.00 sec)

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

手动删除binlog

1
2
3
4
mysql> reset master;   // 删除master的binlog
mysql> reset slave;    // 删除slave的中继日志。
mysql> purge master logs before '2012-03-30 17:20:00';  // 删除指定日期以前的日志索引中binlog日志文件。
mysql> purge master logs to 'mysql-bin.000001';   // 删除指定日志文件的日志索引中binlog日志文件。
  • 或者用操作系统命令直接删除。
1
2
3
4
5
mysql> set sql_log_bin=1/0; // 如果用户有super权限，可以启用或禁用当前会话的binlog记录。
mysql> show master logs; // 查看master的binlog日志。
mysql> show binary logs; // 查看master的binlog日志。
mysql> show master status; // 用于提供master二进制日志文件的状态信息。
mysql> show slave hosts; // 显示当前注册的slave的列表，不以--report-host=slave_name选项为开头的slave不会显示在本列表中。

查看日志

  • 通过MySQLbinlog 查看日志文件。
1
$ MySQLbinlog /tmp/mysql-bin.log

Slow Query Log

  • 记录Mysql 慢查询的日志。

配置

  • 查看日志功能是否开启。
1
2
3
4
5
6
7
8
9
10
mysql> show variables like "%slow%";
+---------------------------+----------------------------------------------------+
| Variable_name             | Value                                              |
+---------------------------+----------------------------------------------------+
| log_slow_admin_statements | OFF                                                |
| log_slow_slave_statements | OFF                                                |
| slow_launch_time          | 2                                                  |
| slow_query_log            | OFF                                                |
| slow_query_log_file       | /usr/local/var/mysql/tongkundeMacBook-Pro-slow.log |
+---------------------------+----------------------------------------------------+

  • slow_query_log为OFF ，说明未开启慢日志。

  • 查看默认设置的慢查询的时间。

1
2
3
4
5
6
mysql> show variables like "%long_query%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
  • 可以看出，默认是10秒。
  • 打开/etc/my.cnf，加上慢查询的配置。
1
2
3
slow-query-log = 1
slow-query-log-file = /tmp/mysql-slow.log
long_query_time = 1 #设置满请求时间，设置查多少秒的查询算是慢查询。
  • 保存退出后要重启MySQL
1
$ mysql.server restart;
  • 打开慢日志功能。
1
2
mysql> set global slow_query_log = on;
ERROR 29 (HY000): File '/tmp/mysql-slow.log' not found (Errcode: 13 - Permission denied)
  • 报错，需要分配权限。
1
chown mysql:mysql /tmp/mysql-slow.log
  • 回到MySQL，再次打开慢日志。
1
2
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

查看日志

  • 先监控日志输出：tail -f /tmp/mysql-slow.log
  • 在MySQL中分别执行两句查询。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

mysql> SELECT sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.01 sec)
  • 查看日志文件的输出。
1
2
3
4
5
# Time: 2015-12-23T15:50:44.140140Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 3.003542  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1450885844;
SELECT sleep(3);
Software BackEnd Database MySQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！