MySQL 日志简介

总览

MySQL Server 有以下⼏种⽇志，可以纪录服务器正在发⽣的运动。

⽇志范例	⽇志信息
⼀般查询⽇志 (General query log)	已建⽴的客⼾端毗连和从客⼾端吸收到的语句
错误⽇志 (Error log)	mysqld在启动、运⾏或停⽌时遇到的标题
慢查询⽇志 (Slow query log)	执⾏时间凌驾 long_query_time 指定秒数的查询
⼆进制⽇志 (Binary log)	更改数据的语句（也⽤于主从复制）
中继⽇志 (Relay log)	从源服务器吸收到的数据更改
DDL⽇志（metadata log）	DDL 语句执⾏的利用
回滚⽇志/取消⽇志(undo log)	⽤于事件的回滚利用
重做⽇志(redo log)	⽤于服务器瓦解规复

• 默认情况下，除 Windows 上的错误⽇志外，不启⽤任何⽇志，Linux下默认开启错误⽇志和⼆进制⽇志
  
• 通过 FLUSH LOGS 语句革新⽇志来强礼服务器关闭并重新打开⽇志⽂件，也可以使⽤mysqladmin 的 flush-logs 或 refresh 参数，或mysqldump 的 --flush-logs 或 --master-data 选项
  

⼀般查询日志和慢查询日志的输出情势

在服务器运⾏期间可以控制⼀般查询和慢查询⽇志的禁⽤与开启，也可以更改⽇志⽂件名,⼀般查询⽇志和慢查询⽇志纪录的输出⽅式可以指定为⽇志⽂件或 mysql 体系库中的 general_log 和 slow_log 表，也可以两者同时指定。
log_output 体系变量指定⽇志输出的情势，但并不会真正的启⽤⽇志。 log_output 可以有三个值，分别是：ABLE (表)、 FILE (⽂件)、 NONE (不输出)，可以同时指定多个值，并⽤逗号隔开，未指定值时默认是 FILE ，假如列表中存在 NONE 则其他的不⽣效，也就是说 NONE 的优先级最⾼。

• 通过设置 general_log 体系变量的值来控制⼀般查询⽇志的 开启 1 与 禁⽤ 0 ，假如要为⽇志指定⾃界说的路径或⽂件名可以使⽤ general_log_file 体系变量
  
• 通过设置 slow_query_log 体系变量的值来控制慢查询⽇志的 开启 1 与 禁⽤ 0 ，假如要为⽇志指定⾃界说的路径或⽂件名可以使⽤ slow_query_log_file 体系变量
  
• 以选项⽂件 /etc/mysql/my.cnf 中的设置为例，将⼀般查询⽇志和慢查询⽇志写⼊⽇志⽂件，并指定⾃界说的⽇志路径
  

2. [mysqld]
3. #⽇志⽂件
4. log_output=FILE
5. #开启⼀般查询⽇志
6. general_log=1
7. #指定⾃定义的⽂件名
8. general_log_file=/var/lib/mysql/general.log
9. #开启慢查询⽇志
10. slow_query_log=1
11. #指定⾃定义的⽂件名
12. slow_query_log_file=/var/lib/mysql/slow_query.log

复制代码

• 也可以在运⾏ mysql 时修改 log_output 的值，以更改⽇志的输出情势，通过语句控制
  

2. set global log_output=[FILE, TABLE, NONE];
3. set global general_log=0或1;
4. set global slow_query_log=0或1;
5. set global general_log_file=自定义文件路径;
6. set global slow_query_log_file=自定义文件路径;

复制代码

假如我们选择⽇志纪录以表的情势输出，我们可以查察⽇志表的结构，可以使⽤以下语句：

2. show create table mysql.general_log\G # ⼀般查询⽇志
3. show create table mysql.slow_log\G # 慢查询⽇志

复制代码

⼀般查询日志

General query log，⼀般查询⽇志，纪录客⼾端毗连或断开毗连的信息，也会纪录从客⼾端吸收的 每个SQL语句。假如开启将会产⽣⼤量的内容，⾮常淹灭服务器资源，以是默以为关闭(不开启)， 要启⽤⼀般查询⽇志可以使⽤：请使⽤ general_log=1，默认⽇志⽂件名为 host_name.log ，可以⽤ general_log_file=file_name 修改。此中日志的文件结构和表结构参数大要划一，我们学习表结构即可，general_log的表结构如下：

2. Create Table: CREATE TABLE `general_log` (
3.   `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
4.   `user_host` mediumtext NOT NULL,
5.   `thread_id` bigint unsigned NOT NULL,
6.   `server_id` int unsigned NOT NULL,
7.   `command_type` varchar(64) NOT NULL,
8.   `argument` mediumblob NOT NULL
9. ) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='General log'

复制代码

此中connection_type 来指⽰⽤于建⽴毗连的协议。TCP/IP 表⽰不使⽤SSL建⽴的TCP/IP毗连、SSL/TLS 表⽰使⽤SSL建⽴的TCP/IP毗连、Socket 表⽰Unix套接字⽂件毗连、Named Pipe 表⽰Windows定名管道毗连、 Shared Memory 表⽰Windows共享内存毗连。
argument 表现表利用的具体SQL语句，不外这部分内容被加密成了一段乱码，如下

这时我们就必要利用 cast 函数举行转化，函数原型如下：

2. CAST(expression AS target_type)

复制代码

此中，expression 是要转换的表达式或值，target_type 是目的数据范例。现在我们要将argument转化为我们能看得懂的笔墨，就必要利用如下代码

2. cast(argument as char)

复制代码

• 查询表中的⽇志内容
  

1. select date_format(event_time, '%Y-%m-%d %H:%i:%s') as time,  user_host,  thread_id,  server_id,  command_type,  
2. cast(argument as char) as query from mysql.general_log\G

复制代码

截取部分信息如下

慢查询日志

• 慢查询⽇志由执⾏时间凌驾体系变量 long_query_time 指定的秒数（默以为10秒）的 SQL 语句构成，而且查抄的⾏数⼤于体系变量 min_examined_row_limit 指定值。被纪录的慢查询必要进⾏优化，可以使⽤ mysqldumpslow客⼾端步调对慢⽇志进⾏分析汇总。
  
• 默以为关闭(不开启)，要启⽤慢查询⽇志可以使⽤：请使⽤ slow_query_log=1 .
  
• 默认⽇志⽂件名为 host_name-slow.log ，可以使⽤ slow_query_log_file=file_name 修改
  
• 默认情况下，不纪录管理语句，也不纪录不使⽤索引的查询，要纪录不使⽤索引的查询，启⽤ log_queries_not_using_indexes 体系变量。当纪录不使⽤索引的查询时，⽇志会快速增⻓，通过设置体系变量 log_throttle_queries_not_using_indexes 限定每分钟写⼊慢查询⽇志同类查询的数目，默认值是0，表⽰⽆限定；要纪录管理语句，启⽤ log_slow_admin_statements 体系变量。管理语句包罗 ALTER TABLE 、 ANALYZE TABLE 、 CHECK TABLE 、 CREATE INDEX 、 DROP INDEX 、OPTIMIZE TABLE 和 REPAIR TABLE 。
  

慢查询⽇志表的表结构如下

2. Create Table: CREATE TABLE `slow_log` (
3.   `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
4.   `user_host` mediumtext NOT NULL,
5.   `query_time` time(6) NOT NULL,
6.   `lock_time` time(6) NOT NULL,
7.   `rows_sent` int NOT NULL,
8.   `rows_examined` int NOT NULL,
9.   `db` varchar(512) NOT NULL,
10.   `last_insert_id` int NOT NULL,
11.   `insert_id` int NOT NULL,
12.   `server_id` int unsigned NOT NULL,
13.   `sql_text` mediumblob NOT NULL,
14.   `thread_id` bigint unsigned NOT NULL
15. ) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='Slow log'

复制代码

• start_time:纪录SQL语句实验完写slow log的时间。
  
• user_host:实验SQL语句的用户和主机名。
  
• query_time:SQL语句的实验时间，不包罗锁期待时间。
  
• lock_time:实验SQL语句前，期待锁的时间。
  
• rows_sent:SQL语句返回的效果集行数。
  
• rows_examined:SQL聚集实验时扫描的行数。
  
• db:实验SQL语句的库名。
  
• last_insert_id:与插入利用干系的ID。
  
• insert_id:与插入利用干系的ID。
  
• server_id:服务器ID。
  
• sql_text:实验的SQL语句。
  
• thread_id:实验SQL的线程ID。
  

错误日志

错误⽇志⼀般会纪录mysqld 启动和关闭的次数、诊断消息，以及服务器运⾏期间发⽣的错误和警 告；比方MySQL必要⾃动查抄或修复⼀个表，就会在错误⽇志中写⼊⼀条纪录。错误⽇志默认使⽤ UTF-8 ( utf8mb3 )编码格式，并使⽤英语⽣成纪录。Windows 的默认错误日志路径

在Windows体系中，mysqld使⽤ --log-error 和 --console 选项来确定默认的错误⽇志⽬标是控制台还是⽂件，规则如下：

• 假如指定了 --console 选项，默认在控制台输堕落误⽇志，假如 --console 和 --log-error 同时指定，则 --console 优先级更⾼，而且 --log-error 将失效。
  
• 假如没有指定 --log-error 大概没有指定具体的⽂件名，默认在数据⽬录中⽣成名为host_name.err 的⽇志⽂件。
  
• 可以通过指定绝对路径，来更改默认的⽇志位置。
  

2. [mysqld]
3. log-error=D:/log/MySQL/Error/error_log.err # ⾃定义错误⽇志的路径

复制代码

Unix 和Linux 的默认错误日志路径

在 Unix 和Linux 体系中，mysqld使⽤ --log-error 选项来指定默认错误⽇志⽬标，可以指定控制 台或是⽂件，假如是⽂件，规则如下：

• 假如错误⽇志输出⽬标是控制台，则服务器将 log_error 体系变量设置为 stderr，否则，将以⽂件情势输⼊错误⽇志，并以 log_error 的值为⽂件名。
  
• 假如显⽰写出 --log-error 但没有指定具体⽂件，则默认路径是数据⽬录中 host_name.err 的⽂件。
  
• 可以在/etc/mysql/my.cnf 中通过指定绝对路径，来更改默认的⽇志位置。
  

2. [mysqld]
3. log-error=/var/log/mysql/error_log.err # ⾃定义错误⽇志的路径

复制代码

错误日志中事故的字段

• time ：事故时间戳，精度为微秒
  
• msg ：事故消息字符串
  
• prio ：事故优先级，包罗 System event - 体系(0)、 Error event - 错误(1)、 Warning event - 告诫(2)或 Note/information event - 关照/提⽰事故(3)，值越⼩优先级越⾼
  
• err_code ：事故错误代码
  
• err_symbol ：以字符串情势表⽰的事故错误符，比方 'ER_DUP_KEY' 
  
• SQL_state ：事故 SQLSTATE 值，与 err_symbol 对应，比方 'ER_DUP_KEY' 对应的 SQLSTATE为23000
  
• subsystem ：事故发⽣的⼦体系。大概的值： InnoDB (InnoDB存储引擎）、 Repl (复制⼦体系)、 Server (其他)
  

示比方下：

2. 2023-07-27T14:15:59.267828+08:00 1 [System] [MY-011012] [Server] Starting
3. upgrade of data directory.
4. 2023-07-27T14:15:59.267942+08:00 1 [System] [MY-013576] [InnoDB] InnoDB
5. initialization has started.
6. 2021-07-27T14:16:11.478109+08:00 1 [System] [MY-013577] [InnoDB] InnoDB
7. initialization has ended.

复制代码

每一行日志内容从左到右依次为 time，prio，err_code，subsystem，错误具体详情。
假如使⽤ flush error logs 、flush logs（革新全部日志） 语句或 mysqladmin flush-logs （革新全部日志）下令革新错误⽇志，服务器会将正在写⼊的任何错误⽇志⽂件关闭并重新打开，假如重新打开时找不到错误日志文件就会重新天生一个新的日志文件。 假如要⼿动重定名错误⽇志⽂件，可以在重定名利用之后执⾏革新利用，服务器会以原⽂件名⽣成 ⼀个新的错误⽇志⽂件，比方⽇志⽂件名为 host_name.err ，可以按以下步调利用：

2. mv host_name.err host_name.err-old # 重命名⽇志⽂件
3. mysqladmin flush-logs # 刷新操作
4. mv host_name.err-old backup-directory # 把重命名的⽇志⽂件移动到备份⽬录

复制代码

二进制日志

二进制⽇志包罗数据库更改的"事故"，不会纪录 SELECT 和 SHOW ，但会纪录表的创建利用或 表数据的更改，⼆进制⽇志还包罗每个语句更新数据时耗费的时间信息，启动⼆进制⽇志，对服务 器性能稍微有些影响。⼆进制⽇志的作⽤：

• 主从节点数据复制：从节点服务器读取主节点服务器上的⼆进制⽇志⽂件，并根据⼆进制⽇志中纪录的事故在从节点上执⾏雷同的利用，包管主从节点服务器上数据⼀致，实现数据复制功能。
  
• 数据规复：从某个时间点规复备份数据后，将重新执⾏备份时间点之跋文录在⼆进制⽇志中的事故。这些事故使数据库从备份点更新到当前最新状态。
  

在mysql上查察⼆进制⽇志干系的体系变量

2. show variables like '%bin%';

复制代码

• 默认情况下启⽤⼆进制⽇志， log_bin 体系变量为 ON
  
• 禁⽤⼆进制⽇志，可以指定 --skip-log-bin 或 --disable-log-bin 选项
  
• 选项 --log-bin=base_name ⽤于指定⼆进制⽇志⽂件的根本名称，假如不指定 --log-bin 选项，默认根本名称为 binlog ，发起为⼆进制⽇志指定⼀个根本名
  
• ⼆进制⽇志⽂件名是由根本名+数字扩展名构成的，服务器每次创建⼀个新的⽇志⽂件时，数字扩展名都会增长，从⽽包管有序的⽂件系列，发⽣以下事故时，服务器都会在创建⼀个新的⽇志⽂件:1.服务器已启动或重新启动；2.服务器革新⽇志；3.当前⽇志⽂件的⼤⼩到达 max_binlog_size (单个⽇志⽂件的最⼤字节数，最⼩值 4096 字节，最⼤值和默认值 1GB)
  

• mysqld还会创建⼀个包罗⼆进制⽇志⽂件名的⽇志索引⽂件，默认情况下，这与⼆进制⽇志⽂件具有雷同的根本名称，扩展名为 .index . 可以使⽤选项 --log-bin-index=file_name修改索引⽂件名
  

• ⼆进制⽇志⽂件和索引⽂件的默认位置是数据⽬录//var/lib/mysql。可以使⽤ --log-bin=file_name选项指定⾃界说路径， file_name 格式 = 绝对路径+根本名。 --log-bin 对应的体系变量是 log_bin_basename 
  

设置⽇志输出位置
查察二进制日志

• 使⽤客⼾端⼯具mysqlbinlog查察
  

2. mysqlbinlog binlog.000011 > binlog.000011

复制代码

左边 binlog.000011 为二进制日志文件，右边 binlog.000011 为导出的文件。

• 通过SQL语句查察
  

2. show binlog events in 'binlog.000011' from N limit S;

复制代码

比方，我们利用如下代码

2. show binlog events in 'binlog.000011' limit 10;

复制代码

• Log_name: 二进制日志文件名
  
• Pos: 当前这条日志在文件的起始偏移量
  
• Event_type: 日志范例
  
• Server_id: 服务器Id，表明是哪台主机
  
• End_log_pos: 当前日志在文件中的竣事偏移量
  
• Info: 具体实验的利用
  

设置日志输出位置

我们可以在/etc/mysql/my.cnf添加如下设置，改变日志的输出位置，使其更规范

2. # 服务器节点
3. [mysqld]
4. # ⼀般查询⽇志和慢查询⽇志记录⽅式为⽂件
5. log-output=FILE
6. # 开启⼀般查询⽇志
7. general-log=1
8. # ⼀般查询⽇志路径和⽂件名
9. general_log_file=/var/log/mysql/general.log
10. # 开启慢查询⽇志
11. slow-query-log=1
12. # 慢查询⽇志路径和⽂件名
13. slow_query_log_file=/var/log/mysql/slow_query.log
14. # 慢查询⽇志时间限制
15. long_query_time=10
16. # 错误⽇志路径和⽂件名
17. log-error=/var/log/mysql/error.log
18. # ⼆进制⽇志路径和基本名
19. log-bin=/var/log/mysql/binlog.log

复制代码

服务器日志维护

MySQL 服务器可以创建多种差别的⽇志⽂件来资助我们查察服务器的运动。但是必须定期清算这 些⽂件，以免⽇志占⽤过多的磁盘空间。在启⽤⽇志的情况下，通常渴望备份和删除旧的⽇志⽂件，并把⽇志写到新⽂件。

• 默认⼆进制⽇志的逾期时间为30天，逾期后将⾃动删除，要指定⾃界说逾期时间，可以使⽤体系变量 binlog_expire_logs_seconds=N 单元为秒，在下⼀次启动服务器和革新⽇志时删除逾期⽇志⽂件
  
• 逼迫使⽤新的⽇志⽂件可以⼿动革新⽇志，当执⾏ flush logs 语句或 mysqladmin flush-logs 、 mysqladmin refresh 、 mysqldump --flush-logs 、 mysqldump --master-data 下令时，会发⽣⽇志革新。别的当⼆进制⽇志⽂件⼤⼩到达max_binlog_size 体系变量指定的值时，服务器会⾃动革新⼆进制⽇志。
  
• flush logs⽀持可选的修饰符以启⽤个别⽇志的选择性革新：
  

2. flush binary logs # 刷新⼆进制⽇志
3. flush error logs # 刷新错误⽇志
4. flush general logs # 刷新⼀般查询⽇志
5. flush relay logs # 刷新中继⽇志
6. flush slow logs # 刷新慢查询⽇志

复制代码

• 革新⼀般查询⽇志、慢查询⽇志或错误⽇志只是关闭并重新打开⽇志⽂件，假如要备份可以先重定名再执⾏革新利用，⽐如⼀般查询⽇志、慢查询⽇志或错误⽇志⽂件名分别为： mysql.log 、mysql-slow.log 和 err.log ，可以在下令⾏中使⽤如下⼀系列下令
  

2. cd mysql-data-directory #进⼊⽇志⽬录
3. mv mysql.log mysql.log.old #重命名⼀般查询⽇志
4. mv mysql-slow.log mysql-slow.log.old #重命名慢查询⽇志
5. mv err.log err.log.old #重命名错误⽇志
6. mysqladmin flush-logs #刷新⽇志

复制代码