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_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(即默认记录)
默认情况下配置,两类日志都是没有开启的:
// 默认情况下,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. 日志表相关优势和特性
- 有标准的日志格式
- 日志可以通过SQL查询访问
- 可以通过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日志格式类型(行、语句、混合)
二进制日志中记录的事件格式取决于二进制日志记录格式:
- 基于行的日志记录(默认):
--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行。
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 TABLE、ALTER 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服务的日志相关内容进行简要介绍,在开发或者性能优化时候,可以通过开启合适的日志记录,方便快速定位和排查问题。
-
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 ↩︎