Mysql （五） - Mysql服务器日志的类型和使用

2019/05/28 共 2731 字 9 分钟阅读
DATABASE mysql 日志处理

AI 摘要: Mysql日志类型概述了Error Log, Query Log, Binary Log, Realy Log, Slow Query Log，以及DDL Log，并提到了刷新日志的操作。在开发和性能优化过程中，合理地配置和利用这些日志可以帮助快速定位问题。

1. Mysql日志类型概述

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

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时候会自动刷新日志文件；

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

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

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

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

1.3. 中继日志

中继日志（Realy Log）主要用于主从同步，用于保存主服务器上的数据更改，后续提供给从属服务器上同步进行；²

1.4. 日志安全

由于Mysql日志中，可能含带敏感信息，故需要注意信息安全问题：³

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

针对Mysql的SQL查询日志、SlowQuery日志，我们可以将其存储在mysql数据库general_log和slow_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(即默认记录)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// 默认情况下，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服务要有读写权限，切目录需要提前创建好！

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
[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. 日志表相关优势和特性

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

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

考虑到日志维护，可以定期做日志表轮询（保留一张备份表general_log2）：

1
2
3
4
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. 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. 常规查询日志示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
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
2
3
4
5
6
7
8
// 方式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可用于显示复制从属中继日志文件内容。

1
2
// 通过-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日志格式类型（行、语句、混合）

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

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

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

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

6. Mysql慢查询日志（优化很有用）

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

1
2
3
4
5
6
7
8
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的定期邮件告警使用！⁵

7. Mysql DDL日志

DDL日志或元数据日志记录由数据定义语句（如DROP TABLE、ALTER TABLE）生成的元数据操作

8. Mysql服务器日志维护

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

日志轮转维护方式：

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

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

1
2
3
4
5
// 基于mysqladmin命令轮转
mysqladmin flush-logs
mysqladmin refresh
mysqldump --flush-logs
mysqldump --master-data

小结

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

Mysql服务器日志：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html ↩︎
中继日志：https://dev.mysql.com/doc/refman/8.0/en/slave-logs-relaylog.html ↩︎
日志通过密码安全：https://dev.mysql.com/doc/refman/8.0/en/password-logging.html ↩︎
错误日志：https://dev.mysql.com/doc/refman/8.0/en/error-log.html ↩︎
慢查询日志：https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html ↩︎