MySQL学习记录 —— 이십이 MySQL服务器文件系统（2）

---

1、日志文件的整体简介

中继服务器的数据来源于集群中的主服务。每次做一些操作时，把操作保存到重做日志，这样崩溃时就可以从重做日志把之前的操作拿回来。

• 默认情况下，除 Windows 上的错误日志外，不启用任何日志，Linux下默认开启错误日志和二进制日志
• 在服务器运行期间可以控制一般查询和慢查询日志的禁用与开启，也可以更改日志文件名
• 一般查询日志和慢查询日志记录可以写入日志表、日志文件或两者同时写入

• 默认情况下，所有启用的日志将写人数据目录，可以通过刷新日志强制服务器关闭并重新打开日志文件
• 通过 FLUSH LOGS 语句刷新日志来强制服务器关闭并重新打开日志文件，也可以使用mysqladmin 的 flush-logs 或 refresh 参数，或mysqldump 的 --flush-logs 或 --master-data 选项
• 中继日志仅用于主从复制过程中的从服务器。

2、一般、慢查询日志

如果启用一般查询日志和慢查询日志，日志的输出方式可以指定为日志文件或 mysql 系统库中的 general_log 和 slow_log 表，也可以两者同时指定。log_output 系统变量指定日志输出的形式，但并不会真正的启用日志。 log_output 有三个值，分别是： TABLE (表)、 FILE (文件)、 NONE (不输出)，可以同时指定多个值，并用逗
号隔开，未指定值时默认是 FILE ，如果列表中存在 NONE 则其他的不生效，即 NONE 的优先级最高。

通过设置 general_log 系统变量的值来控制一般查询日志的 开启 1 与 禁用 0 ，如果要为日志指定自定义的路径或文件名可以使用 general_log_file 系统变量。

通过设置 slow_query_log 系统变量的值来控制慢查询日志的 开启 1 与 禁用 0 ，如果要为日志指定自定义的路径或文件名可以使用 slow_query_log_file 系统变量。

两个log_file系统变量都需要绝对路径。

以选项文件中的配置为例：

[mysqld]
#日志写入表和文件
log_output=TABLE,FILE
#两个日志可以同时开, 可以只开一个 
#开启一般查询日志
general_log=1
#指定自定义的文件名 
general_log_file=/var/lib/mysql/general.log 
#开启慢查询日志
slow_query_log=1 
#指定自定义的文件名
slow_query_log_file=/var/lib/mysql/slow_query.log

如果没用_log_file系统变量来指定路径，那么默认路径在启动mysql后查看

show variables like 'general_log_file';
show variables like 'slow_query_log_file';

运行时修改：

SET [GLOBAL|SESSION] variable_name=value

可以修改日志的输出形式，输出到的文件。若只对当前会话禁用或启用一般查询日志记录，将 SESSION作用域的 sql_log_off 变量设置为 ON 或 OFF。

日志文件在mysql中就是一个表，可以通过 SQL 语句的条件查询过滤日志内容，，从而选择满足特定条件的日志记录。比如，某个客户端的日志；客户端程序连接到服务器就能用SQL语句查询表中的日志信息，这属于应用程序级别，无需用一个有相应权限的系统用户来登录服务器主机访问文件系统。

1、一般查询日志

一般查询日志记录客户端连接或断开连接的信息，也会记录从客户端接收的每个SQL语句。不过它很耗费服务器资源，所以默认为不开启。

默认日志文件名位host_name.log，用 general_log_file=file_name 修改。

记当客户端连接的日志行，使用 connection_type 来指示用于建立连接的协议。 TCP/IP 表示不使用SSL建立的TCP/IP连接、 SSL/TLS 表示使用SSL建立的TCP/IP连接、 Socket 表示Unix套接字文件连接、Named Pipe 表示Windows命名管道连接、 Shared Memory 表示Windows共享内存连接。

Mysqld按照接收到SQL语句的顺序将语句写入查询日志，这个顺序可能与语句执行的顺序不同。

SQL语句查询日志

select * from mysql.general_log\G

查询到的内容有一些就看不出来什么意思，用CAST()来转换一下。

SELECT DATE_FORMAT(event_time, '%Y-%m-%d %H:%i:%s') AS time, 
 user_host, 
 thread_id, 
 server_id, 
 command_type, 
 CAST(argument AS CHAR) AS query 
FROM mysql.general_log;

像这样，在查询DATE_FORMAT表时，把argument转换成char类型的。而select后的as和cast后的as都是重命名的意思。

除此之外，不启动mysql的情况下，查询Linux目录下的日志文件也行，通常在/var/lib/mysql下。

2、慢查询日志

慢查询日志由执行时间超过系统变量 long_query_time 指定的秒数的 SQL 语句组成，并且查询的行数要大于系统变量 min_examined_row_limit 指定值。被记录的慢查询需要进行优化，可以用 mysqldumpslow客户端程序对慢日志进行分析汇总。

获取初始锁的时间不计入执行时间，mysqld在执行完SQL语句并释放所有锁后才将符合条件的语句写入慢速查询日志，因此日志顺序可能与执行顺序不同。也就是说慢查询记录的时间是执行SQL语句并返回结果，释放锁的时间。

使用 --log-short-format 选项，以简要格式记录慢查询日志。

要记录管理语句，启用 log_slow_admin_statements 系统变量。管理语句包括 ALTER、TABLE 、 ANALYZE TABLE 、 CHECK TABLE 、 CREATE INDEX 、 DROP INDEX 、OPTIMIZE TABLE 和 REPAIR TABLE 。

要记录不使用索引的查询，启用 log_queries_not_using_indexes 系统变量。当记录不使用索引的查询时，日志会快速增长，通过设置系统变量log_throttle_queries_not_using_indexes 限制每分钟写入慢查询日志同类查询的数量，默认值是0，表示无限制。

通常设置启用或不启用就是1和0，或者ON和OFF，具体查看官方文档 MySQL 8.4。

修改可以在配置文件修改，也可以在mysql运行时修改，运行时修改就是：

SET [GLOBAL|SESSION] variable_name=value

当然全局还是会话也要看官方文档，变量有3种类型，全局，会话，全局及会话。

FILE格式

如果启用慢查询日志并将 FILE 作为输出目标，每条语句前面都用一行来表示日志的字段，该行以 # 字符开头并包含以下内容：

Query_time： SQL语句的执行时间，单位秒
Lock_time：获取锁的时间，单位秒
Rows_sent：发送到客户端的行数
Rows_examined：服务器扫描数据表中具体数据行的数量

就像这样

启用–log-slow-extra[={OFF|ON}]系统变量会让更多字段写入到FILE中，之后的博客再详细写。

TABLE格式

慢查询日志表的表结构：

SHOW CREATE TABLE mysql.slow_log;

query_time是查询总耗时，lock_time是争抢mysql级别的锁的时间，rows_sent是返回结果集中数据行的数量，rows_examined是服务器扫描数据表中具体数据行的数量，db是数据库名，两个insert是关于插入的记录，server_id是选项文件中配置的server-id，sql_text是具体执行的SQL语句。

建议使用日志文件的形式来记录慢查询日志。

3、错误日志

错误日志一般会记录mysqld 启动和关闭的次数、诊断消息，以及服务器运行期间发生的错误和警告；错误日志默认使用UTF-8 ( utf8mb3 )编码格式，并使用英语生成记录。

错误日志的输出位置是控制台或者指定文件。配置文件中mysqld部分用 --log-error 选项来指定默认错误日志目标，可以指定控制台或文件。

如果错误日志输出目标是控制台，则服务器将 log_error 系统变量设置为 stderr。否则，将以文件形式输入错误日志，并以 log_error 的值为文件名。

如果显式写出 --log-error 但没有指定具体文件，则默认路径是数据目录中host_name.err 的文件；可以通过指定绝对路径，来更改默认的日志位置

[mysqld]
log-error=/var/log/mysql/error_log.err # 自定义错误日志的路径

字段

time ：件时间戳，精度为微秒；
msg ：事件消息字符串；
prio ：事件优先级，包括 System event（System是数据库系统）（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；
上面三个是MySQL内部维护的一套错误代码的定义

subsystem ：事件发生的子系统。可能的值： InnoDB (InnoDB存储引擎）、 Repl (复制子系统)、 Server (其他)。

可选的错误日志字段：

OS_errno ：操作系统错误号；
OS_errmsg ：操作系统错误消息；
label ：与值对应的 prio 描述 ；
user ：客户端用户；
host ：客户端主机；
thread ：产生错误事件 的线程的 ID；
query_id ：查询 ID。

错误日志示例：

每一行从左到右分别是时间，优先级，错误码，子系统，日志详情。

错误码的说明参考官方文档

Error Summary Tables

Global Error Message Reference

当mysqld程序在运行时发生的错误会写到文件中，还有一些情况发生在客户端执行错误的SQL语句时，会出现提示，但不会写入文件：访问了不存在的库和表、语句错误、权限问题。

如果使用FLUSH ERROR LOGS 、 FLUSH LOGS 语句或 mysqladmin flush-logs 命令刷新错误日志，服务器会将正在写入的任何错误日志文件关闭并重新打开。

如果要手动重命名错误日志文件，可以在重命名操作之后执行刷新操作，服务器会以原文件名生成一个新的错误日志文件。

mv host_name.err host_name.err-old # 重命名日志文件
mysqladmin flush-logs # 刷新操作
mv host_name.err-old backup-directory # 把重命名的日志文件移动到备份目录

4、二进制日志

数据库的读写操作全都在一个服务器上，当数据量增大时，单台服务器性能就会出现瓶颈 。一个很有效的解决办法就是形成一个服务器集群，可以让每个服务器降低一点配置，但一定要有足够数量的节点。集群中有一个主服务器，其它为从服务器，让每个服务器都只执行一种操作；可以让主服务器进行数据写入，从服务器负责查询等操作；但服务器之间也要同步数据，这时候就需要有二进制日志，每个服务器所有的操作都生成二进制日志，主服务器的二进制日志的副本放在一个中继服务器上，也就是复制品，从服务器从中继服务器那里读取中继日志，也就是主服的二进制日志的副本，然后再进行各种操作，执行中继日志中的所有修改记录。

⼆进制日志包含数据库更改的"事件"，不会记录 SELECT 和 SHOW；二进制日志还包含每个语句更新数据时花费的时间信息，启动二进制日志，对服务器性能稍微有些影响；除了基于行的日志模式，它还包含可能进行更改数据的语句事件，例如 DELETE 操作没有匹配到查找到的行。

二进制日志的作用：

主从节点数据复制：从节点服务器读取主节点服务器上的二进制日志文件，并根据二进制日志中记录的事件在从节点上执行相同的操作，保证主从节点服务器上数据一致，实现数据复制功能。

数据恢复：从某个时间点恢复备份数据后，将重新执行备份时间点之后记录在二进制日志中的事件。这些事件使数据库从备份点更新到当前最新状态。

⼆进制日志的语句中如果涉及用户的密码，则由服务器进行加密，不会以纯文本形式出现。

查看二进制日志相关的系统变量：

show variables like '%bin%';

状态变量就是把variables换成status。

默认情况下启用二进制日志， log_bin 系统变量为 ON ；

禁用二进制日志，可以指定 --skip-log-bin 或 --disable-log-bin 选项。如果同时指定了 --log-bin 则后指定的选项优先；

选项 --log-bin[=base_name] 用于指定二进制日志文件的基本名称，如果不指定 --log-bin 选项，默认基本名称为 binlog ，建议为二进制日志指定⼀个基本名；

二进制日志文件名是由基本名 + 数字扩展名组成的，服务器每次创建⼀个新的日志文件时，数字扩展名都会增加，从而保证有序的文件系列。当服务器已启动或重新启动、服务器刷新日志、当前日志文件的大小达到max_binlog_size（单个日志文件的最大字节数，最小值 4096 字节，最大值和默认值 1GB）时服务器会创建一个新的日志文件。二进制日志文件大小可能会超出 max_binlog_size 设定的值，因为二进制日志在记录事务
时，会完整的记录整个事务，不存在把一个事务拆分的情况，如果遇到一个大事务时，即使记录整个事务会超过日志大小限制，也会保证事务的完整性；

mysqld还会创建一个包含二进制日志文件名的日志索引文件，默认情况下，这与二进制日志文件具有相同的基本名称，扩展名为 .index . 可以使用选项 --log-bin-index[=file_name]修改索引文件名；

二进制日志文件和索引文件的默认位置是数据目录。可以使用 --log-bin[=file_name] 选项指定自定义路径， file_name 格式 = 绝对路径 + 基本名。 --log-bin 对应的系统变量是log_bin_basename ；

要区分官方文档的这四个变量：

MySQL 8.4

MySQL 5.7 中，启用二进制日志必须指定服务器 ID，对应 server_id 选项，否则服务器将无法启动。在MySQL 8.0 中， server_id 系统变量默认设置为 1，在集群环境中，每台MySQL服务器必须有唯一的 server_id。

可以用客户端工具mysqlbinlog来查看二进制日志

mysqlbinlog binlog.000001 > binlog.000001

前面是二进制日志名，后面是导出的日志文件名，不过导出的是加密的。

SQL语句查看

show binlog events in 'binlog.000001' from N limit S;

在mysql查到的内容中，Pos表示当前这条日志在文件中的起始偏移量；Event_type是查询日志事件类型；End_log_pos是当前日志在文件中的结束偏移量；Info后面是具体执行的操作。

二进制日志有几种日志格式：

基于语句的日志格式，最初MySQL 是基于 SQL 语句复制实现主从节点同步，通过指定选项 --binlog-format=STATEMENT 使用此格式 。执行了什么语句就完整地写进去。

基于行的日志格式(默认)中，主节点将事件写入二进制日志，表示各个表的行受到的影响，可以通过指定选项 --binlog-format=ROW 使用此格式 。像下面的：

update student set age = 18 where id = 10;
update student set age = 18 where id = 11;
update student set age = 18 where id = 12;
...
update student set age = 18 where id = 19;
update student set age = 18 where id = 20;

当使用MySQL内部的一些函数时，必须以行的格式记录日志。

混合日志记录格式，默认情况下使用基于语句的日志记录，如果MySQL认为基于语句的格式不能保证主从复制过程中的数据安全时，会自动切换到基于行的日志格式，比如主节点在语句中用了UUID() 函数，那么日志文件中记录的是UUID生成的真实值而不是直接使用原始的SQL语句，使用混合日志格式中以指定选项 --binlog-format=MIXED。

设置二进制格式：

--binlog-format=[STATEMENT|ROW|MIXED]

5、日志维护

MySQL 服务器可以创建多种不同日志文件来帮助我们查看服务器的活动。但是必须定期清理这些文件，以免日志占用过多的磁盘空间。在启用日志的情况下，通常希望备份和删除旧的日志文件，并把日志写到新文件。

默认二进制日志的过期时间为30天，过期后将自动删除，要指定自定义过期时间，可以使用系统变量 binlog_expire_logs_seconds=N 单位为秒，在下一次启动服务器和刷新日志时删除过期日志文件。

强制使用新的日志文件可以手动刷新日志，当执行 FLUSH LOGS 语句或 mysqladmin flush-logs、mysqladmin refresh 、mysqldump --flush-logs、 mysqldump --master-data 命令时，会发生日志刷新。此外当二进制日志文件大小达到max_binlog_size 系统变量指定的值时，服务器会自动刷新二进制日志。

FLUSH BINARY LOGS # 刷新二进制日志
FLUSH ERROR LOGS # 刷新错误日志
FLUSH GENERAL LOGS # 刷新一般查询日志
FLUSH RELAY LOGS # 刷新中继日志
FLUSH SLOW LOGS # 刷新慢查询日志

刷新一般查询日志、慢查询日志或错误日志只是关闭并重新打开日志文件，如果要备份可以先重命名再执行刷新操作，一般查询日志、慢查询日志或错误日志文件名分别为：mysql.log 、mysql-slow.log 和 err.log。

要在运行时重命名一般、慢查询日志，要先禁用日志

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

然后在bash命令行中去mv重命名，最后恢复日志

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

这种方法各平台通用且不需要重启mysql服务器。

结束。