一、日志简介
MySQL Server 有以下几种日志，可以记录服务器正在发生的活动。
| 日志类型 | 日志信息 |
|---|---|
| 错误日志 (Error log) | mysqld 在启动、运行或停止时遇到的问题 |
| 一般查询日志 (General query log) | 已建立的客户端连接和从客户端接收到的语句 |
| 慢查询日志 (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 表，也可以两者同时指定。表的优势是可以像别的表一样用sql语句查看
2.1 启动时的日志控制
- 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 系统变量。
示例，以选项文件中的配置为例：
- 将一般查询日志写入日志表和日志文件
[mysqld]
#⽇志写⼊表和⽂件
log_output=TABLE,FILE
#开启⼀般查询⽇志
general_log=1
- 仅将一般查询日志和慢查询日志写入日志表
[mysqld]
log_output=TABLE #⽇志写⼊表
general_log=1 #开启⼀般查询⽇志
slow_query_log=1 #开启慢查询⽇志
- 将一般查询日志和慢查询日志写⼊日志文件，并指定自定义的日志路径
[mysqld]
#⽇志⽂件
log_output=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
2.2 运行时的日志控制
- 在运行时修改 log_output 的值，以更改日志的输出形式，通过语句控制。
- 语法：SET [GLOBAL|SESSION] variable_name=value
- SET GLOBAL log_output=[FILE, TABLE, NONE]
- general_log[={0|1}] 和 slow_query_log[={0|1}] 可以表示启用和禁用一般查询日志和慢查询日志
- general_log_file 和 slow_query_log_file 表示通用查询日志和慢查询日志文件名称
- 将 SESSION 作用域的 sql_log_off 变量设置为 ON 或 OFF，只对当前会话禁用或启用一般查询日志记录
2.3 使用日志表的优点
- 可以通过 SQL 语句的条件查询过滤日志内容，从而选择满足特定条件的日志记录。比如，某个客户端的日志；
- 可以通过客户端程序连接到服务器并查询表中的日志信息，无需登录服务器主机访问文件系统。
- 日志记录具有标准格式，可查看日志表的结构，可以使用以下语句：
SHOW CREATE TABLE mysql.general_log; # ⼀般查询⽇志
SHOW CREATE TABLE mysql.slow_log; # 慢查询⽇志
三、一般查询日志
3.1 介绍
- General query log - 一般查询日志，记录客户端连接或断开连接的信息，也会记录从客户端接收的每个 SQL 语句。如果开启将会产生大量的内容，非常耗费服务器资源，所以默认为关闭（不开启），要启用一般查询日志可以使用：--general_log=[0/1]
- 默认日志文件名为 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 语句的顺序将语句写入查询日志，这个顺序可能与语句执行的顺序不同。
mysql> SHOW CREATE TABLE mysql.general_log;
CREATE TABLE `general_log` (
# 发⽣时间
`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`thread_id` bigint unsigned NOT NULL,
`server_id` int unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='General log'3.2一般查询日志示例
- 查询表中的日志内容
# 查看⽇志
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;
- 查看文件中的日志内容
root@iZm5egpp4a85g2tfliaeikZ:/var/lib/mysql# cat general.log
Time Id Command Argument
2023-09-20T08:15:17.260045Z 0 Execute CREATE TABLE
performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on
smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'
2023-09-20T08:15:34.552667Z 8 Connect root@localhost on
using SSL/TLS
2023-09-20T08:15:34.553787Z 8 Query select @@version_comment
limit 1
2023-09-20T08:15:52.115372Z 8 Quit
D:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.33 (MySQL
Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
2023-09-20T08:18:39.590248Z 0 Execute CREATE TABLE
performance_schema.innodb_redo_log_files(
`FILE_ID` BIGINT NOT NULL COMMENT 'Id of the file.',
`FILE_NAME` VARCHAR(2000) NOT NULL COMMENT 'Path to the file.',
`START_LSN` BIGINT NOT NULL COMMENT 'LSN of the first block in the file.',
`END_LSN` BIGINT NOT NULL COMMENT 'LSN after the last block in the file.',
`SIZE_IN_BYTES` BIGINT NOT NULL COMMENT 'Size of the file (in bytes).',
`IS_FULL` TINYINT NOT NULL COMMENT '1 iff file has no free space inside.',
`CONSUMER_LEVEL` INT NOT NULL COMMENT 'All redo log consumers registered on
smaller levels than this value, have already consumed this file.'
)engine = 'performance_schema'
2023-09-20T08:30:20.610448Z 8 Connect root@localhost on
using SSL/TLS
2023-09-20T08:30:20.611135Z 8 Query select @@version_comment
limit 1
2023-09-20T10:54:28.761464Z 8 Query SHOW ENGINES
2023-09-21T04:11:28.305034Z 9 Connect root@localhost on
using SSL/TLS
2023-09-21T04:11:28.305966Z 9 Query select @@version_comment
limit 1
2023-09-21T04:11:33.265039Z 9 Query show variables like
'%binlog%'
2023-09-21T04:13:22.536601Z 9 Query show status like
'%binlog%'
2023-09-21T04:14:27.739316Z 9 Query show variables like
'%log_bin%'
2023-09-21T04:14:54.400781Z 9 Query show variables like
'%bin%'
2023-09-21T04:18:34.087865Z 9 Query show status like '%bin%'
2023-09-21T04:20:04.670226Z 9 Query show warnings
2023-09-21T04:20:09.147985Z 9 Query show variables like
'%bin%'
2023-09-21T04:20:15.472509Z 9 Query show warnings
2023-09-21T06:44:30.559525Z 9 Query show databases
2023-09-21T06:44:39.424602Z 9 Query SELECT DATABASE()
2023-09-21T06:44:39.435176Z 9 Init DB information_schema
2023-09-21T06:44:42.778188Z 9 Query show tables
2023-09-21T06:44:54.251857Z 9 Query select * from
CHARACTER_SETS
2023-09-21T06:56:20.895563Z 9 Query select * from COLLATIONS
2023-09-21T06:56:55.344475Z 9 Query select * from COLLATIONS
where CHARACTER_SET_NAME = 'utf8mb4'
2023-09-21T07:06:29.713623Z 9 Query SELECT DATABASE()
2023-09-21T07:06:29.714064Z 9 Init DB mysql
2023-09-21T07:06:37.070756Z 9 Query select * from user
2023-09-22T11:28:14.602069Z 13 Quit
四、慢查询日志
- 慢查询日志由执行时间超过系统变量 long_query_time 指定的秒数的 SQL 语句组成，并且检查的行数大于系统变量 min_examined_row_limit 指定值。被记录的慢查询需要进行优化，可以使用 mysqldumpslow 客户端程序对慢日志进行分析汇总。
- 获取初始锁的时间不计入执行时间，mysqld 在执行完 SQL 语句并释放所有锁后才将符合条件的语句写入慢速查询日志，因此日志顺序可能与执行顺序不同。
4.1 慢查询日志参数
- long_query_time 的默认值是 10，最小值是 0
- 默认情况下，不记录管理语句，也不记录不使用索引的查询
- 默认为关闭（不开启），要启用慢查询日志可以使用：--slow_query_log=[0/1]
- 默认日志文件名为 host_name-slow.log，可以使用 slow_query_log_file=file_name 修改
- 使用 --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，表示无限制
4.2 慢查询日志内容
文件格式：
- 如果启用慢查询日志并将 FILE 作为输出目标，每条语句前面都用一行来表示日志的字段，该行以 # 字符开头并包含以下内容：
Query_time：SQL 语句的执行时间，单位秒
Lock_time：获取锁的时间，单位秒
Rows_sent：发送到客户端的行数
Rows_examined：服务器检查的行数
- 启用 --log-slow-extra[={OFF|ON}] 系统变量会将以下额外字段写入到 FILE 中，TABLE 形式不受影响
Thread_id：线程标识符
Errno：错误码，没有发生错误则为 0
Killed：如果语句被终止，用错误码表示原因，如果语句正常终止则为 0
Bytes_received：接收到 SQL 语句的 Bytes 值
Bytes_sent：返回给客户端的 Byte 值
Read_first：索引中第一个条目被读取的次数，如果这个值很高，表明服务器正在执行大量完整索引扫描
Read_last：读取索引中最后一个键的请求数，使用 ORDER BY 时关注
Read_key：基于索引读取一行数据的请求数。如果这个值很高，表明表为当前查询建立了正确的索引
Read_next：按索引排序读取下一行的请求数，查询具有范围约束的索引列，或者进行索引扫描，此值将递增
Read_prev：按索引排序读取前一行的请求数。主要用于优化 ORDER BY
Read_rnd：基于固定位置读取一行的请求数。这个值很高表示，正在执行大量需要对结果进行排序的查询，可能有很多查询进行了全表扫描，或者没有正确使用索引的连接
Read_rnd_next：读取数据文件中下一行的请求数。如果进行大量的表扫描，这个值会很高。通常，表示表没有建立正确的索引，或者查询没有利用索引
Sort_merge_passes：排序算法完成的归并次数，如果这个值很大，考虑增加 sort_buffer_size 系统变量的值
Sort_range_count：使用范围进行排序的次数
Sort_rows：排序的行数
Sort_scan_count：通过扫描表完成的排序数
Created_tmp_disk_tables：服务器在执行语句时创建内部磁盘临时表的数量
Created_tmp_tables：服务器在执行语句时创建的内部临时表的数量
Start：执行 SQL 语句开始时间
End：执行 SQL 语句结束时间
表格式 ：
慢查询日志表结构：
mysql> SHOW CREATE TABLE mysql.slow_log;
CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE
CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int NOT NULL,
`rows_examined` int NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int NOT NULL,
`insert_id` int NOT NULL,
`server_id` int unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb3 COMMENT='Slow log'
慢查询日志示例：
Time Id Command Argument
# Time: 2023-10-22T03:34:57.593209Z
# User@Host: root[root] @ localhost [::1] Id: 10
# Query_time: 24.224840 Lock_time: 24.201250 Rows_sent: 0 Rows_examined: 1
use test_db;
SET timestamp=1697945673;
UPDATE ACCOUNT SET NAME='张三' where id = 1;
# Time: 2023-10-22T03:35:31.918024Z
# User@Host: root[root] @ localhost [::1] Id: 9
# Query_time: 19.012846 Lock_time: 19.012325 Rows_sent: 1 Rows_examined: 1
SET timestamp=1697945712;
select * from account where id < 2 FOR SHARE;
# Time: 2023-10-22T04:20:40.347153Z
# User@Host: root[root] @ localhost [::1] Id: 10
# Query_time: 50.412714 Lock_time: 50.411844 Rows_sent: 0 Rows_examined: 0
SET timestamp=1697948389;
UPDATE ACCOUNT SET NAME='张三' where id = 1;
# Time: 2023-10-22T09:27:02.918811Z
# User@Host: root[root] @ localhost [::1] Id: 10
# Query_time: 50.482737 Lock_time: 50.480508 Rows_sent: 0 Rows_examined: 0
SET timestamp=1697966772;
INSERT INTO child (id) VALUES (101);
五、错误日志
错误日志⼀般会记录mysqld启动和关闭的次数、诊断消息，以及服务器运行期间发生的错误和警 告；例如MySQL需要自动检查或修复⼀个表，就会在错误日志中写入一条记录。错误日志默认使用 UTF-8 ( utf8mb3 )编码格式，并使用英语生成记录。
5.1 配置错误日志
错误日志输出的位置，可以是控制台或指定文件，"控制台"表示 stderr 标准错误输出。
5.2 Windows的默认错误日志路径
- 在 Windows 系统中，mysqld 使用 --log-error 和 --console 选项来确定默认的错误日志目标是控制台还是文件，规则如下：
- 如果指定了 --console 选项，默认在控制台输出错误日志，如果 --console 和 --log-error 同时指定，则 --console 优先级更高，并且 --log-error 将失效。
- 如果没有指定 --log-error 或者没有指定具体的文件名，默认在数据目录中生成名为 host_name.err 的日志文件。
- 可以通过指定绝对路径，来更改默认的日志位置。
[mysqld]
log-error=D:/log/MySQL/Error/error_log.err # ⾃定义错误⽇志的路径5.3 Unix 和Linux 系统的默认错误日志路径
- 在 Unix 和 Linux 系统中，mysqld 使用 --log-error 选项来指定默认错误日志目标，可以指定控制台或是文件，如果是文件，规则如下：
- 如果错误日志输出目标是控制台，则服务器将 log_error 系统变量设置为 stderr。否则，将以文件形式输入错误日志，并以 log_error 的值为文件名。
- 如果显示写出 --log-error 但没有指定具体文件，则默认路径是数据目录中 host_name.err 的文件。
- 可以通过指定绝对路径，来更改默认的日志位置。
[mysqld]
log-error=/var/log/mysql/error_log.err # ⾃定义错误⽇志的路径5.4 错误日志中事件的字段
核心错误事件字段：
- 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 (其他)。
可选错误事件字段：
- OS_errno：操作系统错误号；
- OS_errmsg：操作系统错误消息；
- label：与值对应的 prio 描述；
- user：客户端用户；
- host：客户端主机；
- thread：产生错误事件的线程的 ID；
- query_id：查询 ID。
可以通过以下SQL查看已定义的错误类型：
mysql> SELECT *
FROM performance_schema.events_errors_summary_global_by_error
WHERE SUM_ERROR_RAISED <> 0\G
*************************** 1. row ***************************
ERROR_NUMBER: 1064
ERROR_NAME: ER_PARSE_ERROR
SQL_STATE: 42000
SUM_ERROR_RAISED: 1
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2025-01-21 11:01:49
LAST_SEEN: 2025-01-21 11:01:49
*************************** 2. row ***************************
ERROR_NUMBER: 1146
ERROR_NAME: ER_NO_SUCH_TABLE
SQL_STATE: 42S02
SUM_ERROR_RAISED: 2
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2025-01-21 11:01:49
LAST_SEEN: 2025-01-21 11:01:49
*************************** 3. row ***************************
ERROR_NUMBER: 1317
ERROR_NAME: ER_QUERY_INTERRUPTED
SQL_STATE: 70100
SUM_ERROR_RAISED: 1
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2025-01-21 11:01:49
LAST_SEEN: 2025-01-21 11:01:49
5.5 错误日志示例
2025-01-21 11:01:49.267828+08:00 1 [System] [MY-011012] [Server] Starting
upgrade of data directory.
2025-01-21 11:01:49:59.267942+08:00 1 [System] [MY-013576] [InnoDB] InnoDB
initialization has started.
2025-01-217T14:16:11.478109+08:00 1 [System] [MY-013577] [InnoDB] InnoDB
initialization has ended.
2025-01-21T14:16:11.719225+08:00 2 [Warning] [MY-010772] [Server] db.opt file
not found for binlog database. Using default Character set.
2025-01-21T14:16:11.731900+08:00 2 [ERROR] [MY-013140] [Server] Invalid utf8
character string: 'C9F3C5'
2025-01-21T14:16:11.733024+08:00 2 [ERROR] [MY-013140] [Server] Invalid utf8
character string: 'C9F3C5'
2025-01-21T14:16:12.687947+08:00 2 [Warning] [MY-010772] [Server] db.opt file
not found for tmpdir database. Using default Character set.
2025-01-21T14:16:12.992579+08:00 0 [ERROR] [MY-010022] [Server] Failed to
Populate DD tables.
2025-01-21T14:16:12.994141+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2025-01-21T08:23:49.135468Z 1 [Note] A temporary password is generated for
root@localhost: S3Gw_#UBNoqf
5.6 刷新错误日志文件和重命名
- 如果使用 FLUSH ERROR LOGS、FLUSH LOGS 语句或 mysqladmin flush-logs 命令刷新错误日志，服务器会将正在写入的任何错误日志文件关闭并重新打开。
- 如果要手动重命名错误日志文件，可以在重命名操作之后执行刷新操作，服务器会以原文件名生成一个新的错误日志文件，例如日志文件名为 host_name.err，可以按以下步骤操作：
mv host_name.err host_name.err-old # 重命名⽇志⽂件
mysqladmin flush-logs # 刷新操作
mv host_name.err-old backup-directory # 把重命名的⽇志⽂件移动到备份⽬录
六、二进制日志
6.1 介绍
- 二进制日志包含数据库更改的“事件”，不会记录 SELECT 和 SHOW，例如：记录表的创建操作或表数据的更改，二进制日志还包含每个语句更新数据时花费的时间信息，启动二进制日志，对服务器性能稍微有些影响。
- 除了基于行的日志模式，它还包含可能进行更改数据的语句事件，例如 DELETE 操作没有匹配到查找到的行。
- 二进制日志的作用：
- 主从节点数据复制：从节点服务器读取主节点服务器上的二进制日志文件，并根据二进制日志中记录的事件在从节点上执行相同的操作，保证主从节点服务器上数据一致，实现数据复制功能。在主从复制专题中我们重点讲解复制过程。
- 数据恢复：从某个时间点恢复备份数据后，将重新执行备份时间点之后记录在二进制日志中的事件。这些事件使数据库从备份点更新到当前最新状态。
- 二进制日志的语句中如果涉及用户的密码，则由服务器进行加密，不会以纯文本形式出现。
6.2 选项和变量
查看二进制日志相关的系统变量：
mysql> show variables like '%bin%';
+------------------------------------------------+-----------------------------
---+
| Variable_name | Value
|
+------------------------------------------------+-----------------------------
---+
| binlog_cache_size | 32768
|
| binlog_checksum | CRC32
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_encryption | OFF
|
| binlog_error_action | ABORT_SERVER
|
| binlog_expire_logs_auto_purge | ON
|
| binlog_expire_logs_seconds | 2592000
|
| binlog_format | ROW
|
| binlog_group_commit_sync_delay | 0
|
| binlog_group_commit_sync_no_delay_count | 0
|
| binlog_gtid_simple_recovery | ON
|
| binlog_max_flush_queue_time | 0
|
| binlog_order_commits | ON
|
| binlog_rotate_encryption_master_key_at_startup | OFF
|
| binlog_row_event_max_size | 8192
|
| binlog_row_image | FULL
|
| binlog_row_metadata | MINIMAL
|
| binlog_row_value_options |
|
| binlog_rows_query_log_events | OFF
|
| binlog_stmt_cache_size | 32768
|
| binlog_transaction_compression | OFF
|
| binlog_transaction_compression_level_zstd | 3
|
| binlog_transaction_dependency_history_size | 25000
|
| binlog_transaction_dependency_tracking | COMMIT_ORDER
|
| innodb_api_enable_binlog | OFF
|
| log_bin | ON
|
| log_bin_basename | D:\log\MySQL\8.0\bin_log
|
| log_bin_index |
D:\log\MySQL\8.0\bin_log.index |
| log_bin_trust_function_creators | OFF
|
| log_bin_use_v1_row_events | OFF
|
| log_statements_unsafe_for_binlog | ON
|
| max_binlog_cache_size | 18446744073709547520
|
| max_binlog_size | 1073741824
|
| max_binlog_stmt_cache_size | 18446744073709547520
|
| sql_log_bin | ON
|
| sync_binlog | 1
|
+------------------------------------------------+-----------------------------
---+
38 rows in set, 1 warning (0.00 sec)
查看二进制日志相关的状态变量：
mysql> show status like '%bin%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Com_binlog | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
+----------------------------+-------+
7 rows in set (0.00 sec)
- 默认情况下启用二进制日志，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 5.7 中，启用二进制日志必须指定服务器 ID，对应 server_id 选项，否则服务器将无法启动 在 MySQL 8.0 中，server_id 系统变量默认设置为 1，在集群环境中，每台 MySQL 服务器必须有唯一的 server_id
- 二进制日志记录事件支持三种格式类型：基于行的日志记录、基于语句的日志记录和混合日志记录，稍候具体介绍
- 二进制日志记录在语句或事务完成之后，释放锁或在提交完成之前进行 这样做是为了确保按照提交顺序记录日志
- 在一个未提交的事务中，对支持事务的表（如 InnoDB 表）的更改都会被缓存（UPDATE、DELETE 或 INSERT），直到服务器收到 COMMIT 语句，mysqld 在执行 COMMIT 之前将整个事务写入二进制日志
- 如果事务回滚，则在整个事务中记录一个 ROLLBACK 语句，但是对非事务性表（如 MyISAM 表）的修改不能回滚，所以这些修改将被复制到从节点
- 对非事务表的更新在执行后立即存储在二进制日志中
- 当处理事务的线程启动时，它会分配一个大小为 binlog_cache_size 的缓冲区来缓存语句 如果语句大小大于缓冲区的值，线程则打开一个临时文件来存储事务，临时文件在线程结束时删除
- Binlog_cache_use 状态变量显示使用该缓冲区（可能还有临时文件）存储事务的数量 Binlog_cache_disk_use 状态变量显示有多少事务实际上使用了临时文件 结合这两个变量可以把 binlog_cache_size 调优到一个足够大的值，从而避免使用临时文件
- 系统变量 max_binlog_cache_size（默认值和最大值都是 4GB，最小值为 4096）用于限制缓存区大小，如果事务语句大于这个值指定的字节数，事务将会失败并回滚
- 如果使用基于行的日志记录方式，为了保证日志的准确性，CREATE ... SELECT 或 INSERT ... SELECT 语句的并发插入将转换为普通插入 如果使用基于语句的日志记录方式，则将原始语句写入日志
- 由于服务器崩溃或其他原因，导致对二进制日志文件无法进行写入、刷新或者同步到磁盘 那么主从节点上的日志就会出现不一致 当遇到这种问题时，可以通过系统变量 binlog_error_action 控制处理方式：
- 默认值 ABORT_SERVER，服务器停止二进制日志记录并关闭，排查完问题并重启后，服务器按意外停止执行恢复操作
- IGNORE_ERROR 表示，服务器继续进行当前的事务并记录错误，然后停止日志记录，排查问题后，需要确认启用 log_bin，然后再次启动服务器，对日志要求不高的场景可以设置此值，不推荐在集群环境使用
- 默认情况下，sync_binlog=1，表示每个事务在写入缓存后立即同步到磁盘，也可以设置为其他值，比如 sync_binlog=N 表示 N 次事务提交到缓存之后再同步到磁盘，如果 sync_binlog=0 则 MySQL 不控制同步磁盘的频率，完全由操作系统控制 需要注意：如果当 sync_binlog 的值设置为 0 或 N，那么当服务器崩溃时，缓存中的有些日志不能同步到磁盘，可能造成一些更改丢失，所以 sync_binlog=1 是最安全的，但同时效率也是最低的
- 可以使用 RESET MASTER 语句删除所有二进制日志文件，或者使用 PURGE BINARY LOGS 删除一部分二进制日志文件
# 重置⼆进⾏⽇志⽂件和索引⽂件为初始状态
mysql> RESET MASTER;
# 删除指定⽇志⽂件之前的所有⽇志⽂件并更新索引
mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
# 删除指定时间之前的所有⽇志⽂件并更新索引
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'
6.3 查看二进制日志
使用客户端工具mysqlbinlog查看：
mysqlbinlog binlog.000001 > binlog.000001
通过SQL语句查看：
mysql> show binlog events in 'binlog.000001' from N limit S;6.4 二进制日志格式
记录二进制日志时使用的格式有以下几种：
- 基于语句的日志格式，最初 MySQL 是基于 SQL 语句复制实现主从节点同步，通过指定选项 --binlog-format=STATEMENT 使用此格式。
- 基于行的日志格式（默认）中，主节点将事件写入二进制日志，表示各个表的行受到的影响，可以通过指定选项 --binlog-format=ROW 使用此格式。
- 混合日志记录格式，默认情况下使用基于语句的日志记录，如果 MySQL 认为基于语句的格式不能保证主从复制过程中的数据安全时，会自动切换到基于行的日志格式，比如主节点在语句中用了 UUID() 函数，那么日志文件中记录的是 UUID 生成的真实值而不是直接使用原始的 SQL 语句，使用混合日志格式可以通过指定选项 --binlog-format=MIXED。
基于语句与基于行的区别：
# 基于语句，记录执⾏的SQL语句
update student set age = 18 where id between 10 and 20;
# 基于⾏，记录每⼀⾏的更改
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;
七、Redo Log 和 Undo Log
- Redo Log： 重做日志，用于恢复数据；
- Undo 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 支持可选的修饰符以启用个别日志的选择性刷新：
FLUSH BINARY LOGS # 刷新二进制日志
FLUSH ERROR LOGS # 刷新错误日志
FLUSH GENERAL LOGS # 刷新一般查询日志
FLUSH RELAY LOGS # 刷新中继日志
FLUSH SLOW LOGS # 刷新慢查询日志- 刷新一般查询日志、慢查询日志或错误日志只是关闭并重新打开日志文件，如果要备份可以先重命名再执行刷新操作，比如一般查询日志、慢查询日志或错误日志文件名分别为：mysql.log、mysql-slow.log 和 err.log，可以在命令行中使用如下一系列命令：
cd mysql-data-directory # 进入日志目录
mv mysql.log mysql.log.old # 重命名一般查询日志
mv mysql-slow.log mysql-slow.log.old # 重命名慢查询日志
mv err.log err.log.old # 重命名错误日志
mysqladmin flush-logs # 刷新日志- 要在运行时重命名一般查询日志或慢查询日志
1. 首先连接到服务器并禁用日志：
SET GLOBAL general_log = 'OFF'; SET GLOBAL slow_query_log = 'OFF';
2. 在禁用日志的情况下，重命名日志文件，例如用 mv 命令从命令行执行重命名操作。
3. 再次启用日志：SET GLOBAL general_log = 'ON'; SET GLOBAL slow_query_log = 'ON';
这种方法适用于任何平台且不需要重启服务器。
配置日志输出位置：
# 服务器节点
[mysqld]
# ⼀般查询⽇志和慢查询⽇志记录⽅式为⽂件
log-output=FILE
# 开启⼀般查询⽇志
general-log=1
# ⼀般查询⽇志路径和⽂件名
general_log_file=/var/log/mysql/general.log
# 开启慢查询⽇志
slow-query-log=1
# 慢查询⽇志路径和⽂件名
slow_query_log_file=/var/log/mysql/slow-query.log
# 慢查询⽇志时间限制
long_query_time=10
# 错误⽇志路径和⽂件名
log-error=/var/log/mysql/error.err
# ⼆进制⽇志路径和基本名
log-bin=/var/log/mysql/binlog
# 服务器编号
server-id=1