Mysql (五) - Mysql服务器日志的类型和使用

1. Mysql日志类型概述

Mysql日志可以帮助定位和分析问题,其相关类型有1

  • Error Log:启动,运行或停止mysqld时遇到的问题
  • Query Log:建立客户端连接和从客户端收到的语句
  • Binary Log:更改数据的语句,注意用于数据同步
  • Realy Log(中继日志):从复制主服务器收到的数据更改
  • Slow Query Log:执行时间超过long_query_time秒的查询
  • DDL Log:DDL语句执行的元数据操作

1.1. Mysql日志维护:日志刷新

Mysql服务默认情况下,除错误日志外,其他日志都没有开启;默认情况,日志写在数据目录中;

日志刷新:当发出FLUSH LOGS声明时,或者强制重启Mysqld服务会发生日志刷新;二进制日志在其大小超过max_binlog_size时候会自动刷新日志文件;

mysqladmin flush-logs | refresh
mysqldump  --flush-logs | --master-data

1.2. SQL查询、慢查询日志运行时开启

Mysql可以在运行时控制常规查询和慢查询日志,可以启用或禁用日志记录,也可以更改日志文件名。

同时,也可以告诉服务器将常规查询和慢查询记录表、日志文件或两者均可。

1.3. 中继日志

中继日志(Realy Log)主要用于主从同步,用于保存主服务器上的数据更改,后续提供给从属服务器上同步进行;2

1.4. 日志安全

由于Mysql日志中,可能含带敏感信息,故需要注意信息安全问题:3

2. Mysql常规查询日志、慢查询配置相关

针对Mysql的SQL查询日志、SlowQuery日志,我们可以将其存储在mysql数据库general_logslow_log表中或者是指定存储文件中,或者两者同时开启!

2.1. 在mysqld服务启动时候设置日志

  • log_output=FILE|TABLE|NONE:默认是FILE,可以设定成TABLE(记录到日志表),或者NONE(关闭日志)
  • general_log=0|1 :查询日志是否开启
  • general_log_file=file:查询存储位置
  • slow_query_log=0|1
  • slow_query_log_file=file
  • sql_log_off:0|1:是否关闭sql日志,默认是off(即默认记录)

默认情况下配置,两类日志都是没有开启的:

// 默认情况下,general_log_file和slow_query_log_file已设置了,但均未启用
mysql> show variables where variable_name like "%slow_query%" or variable_name like "%general%" or variable_name like "%log_output" or  variable_name like "%sql_log_off";
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| general_log         | OFF                                  |
| general_log_file    | /var/lib/mysql/2430103bc0bb.log      |
| log_output          | FILE                                 |
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/2430103bc0bb-slow.log |
| sql_log_off         | OFF                                  |
+---------------------+--------------------------------------+

2.2. 基于服务运行时,手动进行日志设置

同在服务启动时候配置一致,额外还要一个sql_log_off

  • general_log=ON|OFF
  • slow_query_log=ON|OFF
  • sql_log_off=0|1:是否对当前会话禁用对常规查询日志的日志记录

2.3. 基于my.cnf配置,通过服务配置+重启设置

如果是新创建的子目录,Mysql服务要有读写权限,切目录需要提前创建好!

[mysqld]
...
 # sql query log
log_output=FILE,TABLE
general_log=ON
general_log_file=/var/lib/mysql/general_logs/sql-query.log

 # slow sql log
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow_logs/slow-query.log
long_query_time=3

2.4. 日志表相关优势和特性

  1. 有标准的日志格式
  2. 日志可以通过SQL查询访问
  3. 可以通过Mysql客户端连接服务器进行相关日志查询(无需登录机器)

考虑到sql表日志性能等问题,采用CSV storage,并禁用了ALTER TABLE、DROP TABLE、INSERT、DELETE、UPDATE等操作,支持日志表清空TRUNCATE TABLE和重命名RENAME TABLE,mysqldump备份不会备份sql日志表。

考虑到日志维护,可以定期做日志表轮询(保留一张备份表general_log2):

USE mysql;
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;

3. Mysql错误日志

Mysql错误日志包含mysqld服务启动和关闭时候的记录,它还包含诊断消息,例如在服务器启动和关闭期间以及服务器运行时发生的错误,警告和注释(当mysqld_safe注意到mysqld出现异常时,它会重新启动 mysqld并将mysqld restarted消息写入错误日志。)

3.1. 错误日志存储

在Linux中,--log-error选项来确定默认错误日志目标是控制台还是文件:

  • 如果log_error是stderr,则默认错误日志目标是控制台
  • 如果log_error不是stderr,则默认错误日志目标是指示文件名

另外,还以设置日志的消息格式、错误类型过滤、错误消息明细等4

4. Mysql常规查询日志(调试很有用)

当需要调试客户端中存在错误并想要确切知道客户端发送给mysqld的内容时,通用查询日志非常有用。

与mysql-binlog对比,query-log写入的顺序和实际执行sql顺序可能有差异,但binlog则不会;此外,query-log会包含查询语句,这些在binglog不会出现;

当基于binlog做主从同步时候,binlog如果是基于statement-based而非row-based格式(binlog_format=ROW),这些语句不会被写入从库的查询日志中。

如果没有为常规查询日志文件指定名称,则默认名称为host_name.log

SQL常规查询日志密码部分会被忽略掉,可以通过--log-raw在服务器启动期间启用。

时区指定log_timestamps可以限定通用日志、错误日志、慢查询日志的时区设定,或者通过设置系统time_zone来设定。

4.1. 常规查询日志示例

2019-05-28T05:14:16.073839Z    24 Query begin
2019-05-28T05:14:58.553810Z    24 Query select uid,name from users where uid = 100
2019-05-28T05:15:44.942917Z    24 Query update users set name="heyman" where uid = 100
2019-05-28T05:15:48.611630Z    24 Query select uid,name from users where uid = 100
2019-05-28T05:15:59.568489Z    25 Connect   root@172.19.0.1 on  using SSL/TLS
2019-05-28T05:15:59.575781Z    25 Query select @@version_comment limit 1
2019-05-28T05:16:09.841528Z    25 Query SELECT DATABASE()
2019-05-28T05:16:09.846615Z    25 Init DB   gorm
2019-05-28T05:16:09.853376Z    25 Query show databases
2019-05-28T05:16:09.862143Z    25 Query show tables
2019-05-28T05:16:09.867356Z    25 Field List    products
2019-05-28T05:16:09.870023Z    25 Field List    suppliers
2019-05-28T05:16:09.872698Z    25 Field List    users
2019-05-28T05:16:17.433497Z    25 Query select uid,name from users where uid = 100
2019-05-28T05:16:25.824388Z    24 Query commit
2019-05-28T05:16:28.045527Z    25 Query select uid,name from users where uid = 100
2019-05-28T05:18:31.176398Z    25 Query help 'create table'
2019-05-28T05:20:09.536564Z    24 Query create table address (id int(10) primary key, address varchar(255))

4.2. 连接类型说明

  • TCP/IP:没有SSL建立的TCP/IP连接
  • SSL/TLS:TCP/IP connection established with SSL
  • Socket:Unix socket file connection
  • Named Pipe:命名管道连接
  • Shared Memory:共享内存连接

4.3. 常规日志维护:日志刷新

// 方式1:基于mysqladmin 刷新命令执行
$ mv host_name.log host_name-old.log
$ mysqladmin flush-logs
$ mv host_name-old.log backup-directory
// 方式2:mysql内执行变量设置+命令行操作
$ SET GLOBAL general_log = 'OFF';
$ mv host_name.log host_name-old.log
$ SET GLOBAL general_log = 'ON';

5. Mysql二进制日志

Mysql的Binlog用于主从同步,仅包含有对数据库更改的"事件”,比如创建、修改表,新增、更改、删除记录,包含语句执行的时间长度信息,不包含诸如查询、显示语句内容(这块可以通过开启常规查询日志开启)。

Binlog记录时机:在语句或事务完成commit之后,或完成任何事务提交之前但释放任何锁定,或者无事务的立即执行,都会记录到二进制日志记录,这可确保以提交顺序记录日志。

Binlog有两个重要作用:主从同步+备份还原,虽然开启有一定性能损耗,但在安全备份面前,利大于弊;

考虑到二进制安全问题,可以设置binlog_encryption=1,使得二进制被加密;

mysqlbinlog可用于显示复制从属中继日志文件内容。

// 通过-v查看SQL明细
$ mysqlbinlog ./binlog.000009 -v

5.1. Binlog相关配置参数

二进制日志文件和二进制日志索引文件的默认位置是数据目录,

  • log_bin=0|1,默认是自动开启的
  • log_bin_basename:二进制基础名称部分
  • log_bin_index:二进制索引文档文件
  • server_id=1 : 5.7版本中必须指定,8版本中默认为1,在主从结构中,必须指定
  • binlog_error_action:错误处理
  • binlog_expire_logs_seconds:二进制日志过期期限

5.2. Binlog日志格式类型(行、语句、混合)

二进制日志中记录的事件格式取决于二进制日志记录格式:

  1. 基于行的日志记录(默认):--binlog-format=ROW,可以避免负载非确定性语句的问题
  2. 基于语句的日志记录:--binlog-format=STATEMENT,使用基于语句的复制,可能存在复制非确定性语句的问题(比如存储引擎设置或限制)。
  3. 基于混合的日志记录:--binlog-format=MIXED

5.3. 针对同步过程中出错处理

  • ABORT_SERVER:默认,暂停二进制日志记录并关闭mysqld服务,需要通过修复后再重启。
  • IGNORE_ERROR:服务器继续正在进行的事务并记录错误,然后暂停二进制日志记录,但mysqld服务可以继续执行更新。

6. Mysql慢查询日志(优化很有用)

慢查询日志包含执行时间超过long_query_time秒的SQL语句,并且至少需要检查min_examined_row_limit行。

mysql> show variables where variable_name in ("long_query_time", "min_examined_row_limit");
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| long_query_time        | 3.000000 |
| min_examined_row_limit | 0        |
+------------------------+----------+
2 rows in set (0.01 sec)

慢查询日志,可以基于mysqldumpslow导出,比如作为DBA的定期邮件告警使用!5

7. Mysql DDL日志

DDL日志或元数据日志记录由数据定义语句(如DROP TABLEALTER TABLE)生成的元数据操作

8. Mysql服务器日志维护

MySQL服务器可以创建几个不同的日志文件,以帮助您查看正在进行的活动。但是,您必须定期清理这些文件,以确保日志不占用太多磁盘空间。

日志轮转维护方式:

  • Linux:基于mysql-log-rotate + cron
  • mysql执行FLUSH LOGS语句:FLUSH BINARY LOGS,然后会生成一个新的binlog.xxx文件
  • 基于Mysql管理命令

Know: 当二进制日志的大小达到max_binlog_size系统变量的值时,将刷新二进制日志

// 基于mysqladmin命令轮转
mysqladmin flush-logs
mysqladmin refresh
mysqldump --flush-logs
mysqldump --master-data

小结

针对Mysql服务的日志相关内容进行简要介绍,在开发或者性能优化时候,可以通过开启合适的日志记录,方便快速定位和排查问题。


  1. Mysql服务器日志:https://dev.mysql.com/doc/refman/8.0/en/server-logs.html ↩︎

  2. 中继日志:https://dev.mysql.com/doc/refman/8.0/en/slave-logs-relaylog.html ↩︎

  3. 日志通过密码安全:https://dev.mysql.com/doc/refman/8.0/en/password-logging.html ↩︎

  4. 错误日志:https://dev.mysql.com/doc/refman/8.0/en/error-log.html ↩︎

  5. 慢查询日志:https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html ↩︎