MySQL 8 教程

MySQL 8.0中的主要日志类型

日志类型	描述	主要用途
二进制日志（Binary Log）	记录所有更改数据的语句	数据恢复、主从复制、审计
错误日志（Error Log）	记录MySQL服务器启动、运行和停止过程中的错误和警告信息	排查服务器问题、监控服务器状态
通用查询日志（General Query Log）	记录所有客户端连接和发送给服务器的SQL语句	审计、调试、监控用户活动
慢查询日志（Slow Query Log）	记录执行时间超过指定阈值的SQL语句	性能优化、识别慢查询
中继日志（Relay Log）	在复制架构中，从服务器用来存储从主服务器接收到的二进制日志	主从复制
事务日志（Transaction Log）	对于InnoDB存储引擎，记录事务的变更	崩溃恢复、事务回滚

在配置文件中设置二进制日志

# 在Linux系统中的配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf
# 在Windows系统中的配置文件通常是C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]
# 启用二进制日志
log_bin = mysql-bin

# 设置二进制日志文件的存储路径（可选，默认为数据目录）
# log_bin_basename = /var/lib/mysql/mysql-bin

# 设置二进制日志格式（STATEMENT、ROW或MIXED）
binlog_format = ROW

# 设置二进制日志过期时间（单位：天）
expire_logs_days = 10

# 设置二进制日志文件的最大大小（单位：字节）
max_binlog_size = 100M

# 控制自动删除过期二进制日志的行为
binlog_expire_logs_seconds = 864000  # 10天（以秒为单位）

# 设置sync_binlog参数（控制二进制日志刷新到磁盘的频率）
sync_binlog = 1  # 每次事务提交都刷新到磁盘

二进制日志格式说明

MySQL支持三种二进制日志格式：

• STATEMENT：记录SQL语句本身。优点是日志文件较小，缺点是某些语句可能导致主从复制不一致。
• ROW：记录行的更改。优点是可以保证主从复制的一致性，缺点是日志文件较大。
• MIXED：混合使用STATEMENT和ROW格式。MySQL会根据语句的类型自动选择合适的格式。

在MySQL 8.0中，默认的二进制日志格式是ROW。

使用SET命令动态设置二进制日志参数

-- 查看当前二进制日志格式
SHOW VARIABLES LIKE 'binlog_format';

-- 动态设置二进制日志格式（需要SUPER权限）
SET GLOBAL binlog_format = 'ROW';

-- 查看当前二进制日志过期时间
SHOW VARIABLES LIKE 'expire_logs_days';

-- 动态设置二进制日志过期时间
SET GLOBAL expire_logs_days = 10;

-- 查看当前sync_binlog设置
SHOW VARIABLES LIKE 'sync_binlog';

-- 动态设置sync_binlog（需要重启MySQL服务器才能生效）
SET GLOBAL sync_binlog = 1;

查看所有二进制日志文件列表

-- 查看所有二进制日志文件列表
SHOW BINARY LOGS;

-- 示例输出：
/*
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
| mysql-bin.000002 |       154 |
| mysql-bin.000003 |      2456 |
+------------------+-----------+
*/

查看当前正在使用的二进制日志文件

-- 查看当前正在使用的二进制日志文件
SHOW MASTER STATUS;

-- 示例输出：
/*
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     2456 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
*/

使用mysqlbinlog工具查看二进制日志内容

-- 使用mysqlbinlog工具查看二进制日志内容
mysqlbinlog mysql-bin.000003

-- 查看指定时间段内的二进制日志
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-02 00:00:00" mysql-bin.000003

-- 查看指定位置范围内的二进制日志
mysqlbinlog --start-position=107 --stop-position=1000 mysql-bin.000003

-- 将二进制日志内容输出到文件
mysqlbinlog mysql-bin.000003 > binlog_content.txt

-- 查看ROW格式的二进制日志内容（使用-v参数）
mysqlbinlog -v mysql-bin.000003

-- 查看ROW格式的二进制日志内容（使用-vv参数显示更详细的信息）
mysqlbinlog -vv mysql-bin.000003

使用PURGE BINARY LOGS命令删除二进制日志

-- 删除指定日期之前的所有二进制日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 删除指定文件名之前的所有二进制日志
PURGE BINARY LOGS TO 'mysql-bin.000003';

-- 注意：不能删除当前正在使用的二进制日志文件

使用RESET MASTER命令删除所有二进制日志

-- 删除所有二进制日志文件，并重新开始编号
RESET MASTER;

-- 注意：在主从复制架构中，主服务器执行RESET MASTER命令会导致从服务器无法继续复制

设置二进制日志自动过期

-- 在配置文件中设置二进制日志自动过期时间（单位：天）
expire_logs_days = 10

-- 或者使用SET命令动态设置
SET GLOBAL expire_logs_days = 10;

-- 在MySQL 8.0中，还可以使用binlog_expire_logs_seconds参数（单位：秒）
SET GLOBAL binlog_expire_logs_seconds = 864000;  -- 10天

使用二进制日志恢复数据库的基本步骤

1. 确定恢复的起点：如果有全量备份，需要先使用全量备份恢复数据库，然后从全量备份之后的二进制日志开始恢复
2. 应用二进制日志：使用mysqlbinlog工具将二进制日志内容转换为SQL语句，然后执行这些SQL语句
3. 验证恢复结果：恢复完成后，验证数据库是否恢复到了预期的状态

使用二进制日志恢复数据库的示例

-- 假设我们有一个全量备份文件db_backup.sql，以及二进制日志文件mysql-bin.000001和mysql-bin.000002

-- 1. 先使用全量备份恢复数据库
mysql -u root -p database_name < db_backup.sql

-- 2. 应用全量备份之后的二进制日志
-- 方法1：使用mysqlbinlog工具和管道
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 方法2：将二进制日志内容导出到文件，然后执行
mysqlbinlog mysql-bin.000001 mysql-bin.000002 > binlog_to_apply.sql
mysql -u root -p database_name < binlog_to_apply.sql

-- 3. 恢复到指定的时间点
mysqlbinlog --stop-datetime="2023-01-01 12:00:00" mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 4. 恢复到指定的位置
mysqlbinlog --stop-position=1000 mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 5. 从指定的时间点开始恢复
mysqlbinlog --start-datetime="2023-01-01 10:00:00" mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 6. 从指定的位置开始恢复
mysqlbinlog --start-position=500 mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 7. 恢复到指定的时间范围
mysqlbinlog --start-datetime="2023-01-01 10:00:00" --stop-datetime="2023-01-01 12:00:00" mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

-- 8. 恢复到指定的位置范围
mysqlbinlog --start-position=500 --stop-position=1000 mysql-bin.000001 mysql-bin.000002 | mysql -u root -p database_name

暂时停止二进制日志功能的方法

-- 在当前会话中暂时停止二进制日志记录（需要SUPER权限）
SET SQL_LOG_BIN = 0;

-- 执行不需要记录到二进制日志的操作
-- ...

-- 恢复二进制日志记录
SET SQL_LOG_BIN = 1;

-- 注意：这个设置只对当前会话有效，不会影响其他会话的二进制日志记录

在配置文件中设置错误日志

# 在Linux系统中的配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf
# 在Windows系统中的配置文件通常是C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]
# 设置错误日志文件的路径和名称
log_error = /var/log/mysql/error.log  # Linux系统
# log_error = "C:/ProgramData/MySQL/MySQL Server 8.0/Logs/error.log"  # Windows系统

# 设置错误日志的级别（可选，默认为警告级别）
# 可用的级别：DEBUG, INFO, NOTICE, WARNING, ERROR, CRITICAL, ALERT, EMERGENCY
# log_error_verbosity = 3  # 默认为3，表示记录错误、警告和通知信息

# 设置是否将错误日志输出到控制台（可选，默认为OFF）
# log_error_console = ON

# 设置是否将PID文件写入错误日志目录（可选，默认为OFF）
# log_error_services = log_filter_internal; log_sink_internal

# 设置错误日志的格式（可选，默认为FILE）
# log_error_sink = FILE

使用SHOW命令查看错误日志的设置

-- 查看错误日志文件的路径
SHOW VARIABLES LIKE 'log_error';

-- 查看错误日志的级别
SHOW VARIABLES LIKE 'log_error_verbosity';

-- 查看错误日志的服务设置
SHOW VARIABLES LIKE 'log_error_services';

使用文本编辑器查看错误日志

-- 在Linux系统中，使用cat、less、tail等命令查看错误日志
cat /var/log/mysql/error.log
less /var/log/mysql/error.log
tail -f /var/log/mysql/error.log  # 实时查看错误日志

tail -n 100 /var/log/mysql/error.log  # 查看最后100行错误日志

-- 在Windows系统中，可以使用记事本或其他文本编辑器查看错误日志
-- 路径通常为：C:/ProgramData/MySQL/MySQL Server 8.0/Logs/error.log

使用MySQL Workbench查看错误日志

MySQL Workbench提供了图形界面来查看错误日志：

1. 打开MySQL Workbench
2. 连接到MySQL服务器
3. 点击"Server" -> "Server Logs" -> "Error Log"

删除错误日志的方法

-- 方法1：手动删除错误日志文件，然后重启MySQL服务器
-- 在Linux系统中
rm /var/log/mysql/error.log
service mysql restart

-- 方法2：使用FLUSH ERROR LOGS命令轮换错误日志（MySQL 8.0.22及以上版本）
FLUSH ERROR LOGS;

-- 执行该命令后，MySQL会关闭当前的错误日志文件，并创建一个新的错误日志文件
-- 旧的错误日志文件会被重命名为error.log.old或类似的名称

-- 方法3：在MySQL配置文件中设置错误日志的滚动策略
-- 例如，设置错误日志每小时滚动一次
# log_error_services = log_filter_internal; log_sink_internal
# log_rotate_age = 1
# log_rotate_size = 0

在配置文件中启动通用查询日志

# 在Linux系统中的配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf
# 在Windows系统中的配置文件通常是C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]
# 启用通用查询日志
general_log = ON

# 设置通用查询日志文件的路径和名称
general_log_file = /var/log/mysql/general.log  # Linux系统
# general_log_file = "C:/ProgramData/MySQL/MySQL Server 8.0/Logs/general.log"  # Windows系统

# 设置通用查询日志的输出格式（可选，默认为FILE）
# log_output = FILE  # 输出到文件
# log_output = TABLE  # 输出到mysql.general_log表
# log_output = FILE,TABLE  # 同时输出到文件和表

使用SET命令动态启动通用查询日志

-- 查看当前通用查询日志的设置
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'log_output';

-- 动态启用通用查询日志（需要SUPER权限）
SET GLOBAL general_log = ON;

-- 动态设置通用查询日志文件的路径和名称
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- 动态设置通用查询日志的输出格式
SET GLOBAL log_output = 'FILE';
SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_output = 'FILE,TABLE';

查看文件格式的通用查询日志

-- 在Linux系统中，使用cat、less、tail等命令查看通用查询日志
cat /var/log/mysql/general.log
less /var/log/mysql/general.log
tail -f /var/log/mysql/general.log  # 实时查看通用查询日志

tail -n 100 /var/log/mysql/general.log  # 查看最后100行通用查询日志

-- 在Windows系统中，可以使用记事本或其他文本编辑器查看通用查询日志
-- 路径通常为：C:/ProgramData/MySQL/MySQL Server 8.0/Logs/general.log

查看表格式的通用查询日志

-- 当log_output设置为TABLE或FILE,TABLE时，可以查询mysql.general_log表查看通用查询日志
SELECT * FROM mysql.general_log;

-- 查询最近的100条通用查询日志记录
SELECT * FROM mysql.general_log ORDER BY event_time DESC LIMIT 100;

-- 过滤特定用户的查询日志
SELECT * FROM mysql.general_log WHERE user_host LIKE '%username%';

-- 过滤特定时间范围内的查询日志
SELECT * FROM mysql.general_log WHERE event_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-02 00:00:00';

-- 清空mysql.general_log表（需要TRUNCATE权限）
TRUNCATE TABLE mysql.general_log;

删除通用查询日志的方法

-- 方法1：禁用通用查询日志
SET GLOBAL general_log = OFF;

-- 方法2：清空文件格式的通用查询日志
-- 在Linux系统中
echo "" > /var/log/mysql/general.log

-- 方法3：轮换文件格式的通用查询日志
-- 先重命名当前的通用查询日志文件
mv /var/log/mysql/general.log /var/log/mysql/general.log.old
-- 然后刷新日志，创建新的通用查询日志文件
FLUSH GENERAL LOGS;

-- 方法4：清空表格式的通用查询日志
TRUNCATE TABLE mysql.general_log;

在配置文件中启动和设置慢查询日志

# 在Linux系统中的配置文件通常是/etc/my.cnf或/etc/mysql/my.cnf
# 在Windows系统中的配置文件通常是C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]
# 启用慢查询日志
slow_query_log = ON

# 设置慢查询日志文件的路径和名称
slow_query_log_file = /var/log/mysql/slow.log  # Linux系统
# slow_query_log_file = "C:/ProgramData/MySQL/MySQL Server 8.0/Logs/slow.log"  # Windows系统

# 设置慢查询的时间阈值（单位：秒）
long_query_time = 2  # 执行时间超过2秒的查询会被记录

# 设置是否记录没有使用索引的查询
log_queries_not_using_indexes = ON

# 设置是否记录管理语句（如ALTER TABLE、ANALYZE TABLE等）
log_slow_admin_statements = ON

# 设置是否记录慢查询的执行计划
log_slow_slave_statements = ON

# 设置慢查询日志的输出格式（可选，默认为FILE）
# log_output = FILE  # 输出到文件
# log_output = TABLE  # 输出到mysql.slow_log表
# log_output = FILE,TABLE  # 同时输出到文件和表

# 设置记录慢查询的最小行数
min_examined_row_limit = 1000  # 扫描行数超过1000的查询才会被记录

使用SET命令动态启动和设置慢查询日志

-- 查看当前慢查询日志的设置
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'log_output';

-- 动态启用慢查询日志（需要SUPER权限）
SET GLOBAL slow_query_log = ON;

-- 动态设置慢查询日志文件的路径和名称
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 动态设置慢查询的时间阈值
SET GLOBAL long_query_time = 2;

-- 动态设置是否记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 动态设置慢查询日志的输出格式
SET GLOBAL log_output = 'FILE';
SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_output = 'FILE,TABLE';

直接查看慢查询日志文件

-- 在Linux系统中，使用cat、less、tail等命令查看慢查询日志
cat /var/log/mysql/slow.log
less /var/log/mysql/slow.log
tail -f /var/log/mysql/slow.log  # 实时查看慢查询日志
tail -n 100 /var/log/mysql/slow.log  # 查看最后100行慢查询日志

-- 在Windows系统中，可以使用记事本或其他文本编辑器查看慢查询日志
-- 路径通常为：C:/ProgramData/MySQL/MySQL Server 8.0/Logs/slow.log

查看表格式的慢查询日志

-- 当log_output设置为TABLE或FILE,TABLE时，可以查询mysql.slow_log表查看慢查询日志
SELECT * FROM mysql.slow_log;

-- 查询最近的100条慢查询日志记录
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100;

-- 过滤特定用户的慢查询日志
SELECT * FROM mysql.slow_log WHERE user_host LIKE '%username%';

-- 过滤特定时间范围内的慢查询日志
SELECT * FROM mysql.slow_log WHERE start_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-02 00:00:00';

-- 清空mysql.slow_log表（需要TRUNCATE权限）
TRUNCATE TABLE mysql.slow_log;

使用mysqldumpslow工具分析慢查询日志

mysqldumpslow是MySQL提供的一个用于分析慢查询日志的工具，它可以汇总相似的慢查询，帮助识别系统中的性能瓶颈。

-- 查看mysqldumpslow工具的帮助信息
mysqldumpslow --help

-- 显示慢查询日志中的前10条最慢的查询
mysqldumpslow -t 10 /var/log/mysql/slow.log

-- 按查询执行次数排序显示慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

-- 按查询锁定时间排序显示慢查询
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

-- 按查询返回的行数排序显示慢查询
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

-- 按查询执行时间排序显示慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 显示包含特定字符串的慢查询
mysqldumpslow -g "SELECT" /var/log/mysql/slow.log

-- 显示不使用索引的慢查询
mysqldumpslow -g "Full scan" /var/log/mysql/slow.log

-- 组合多个选项
mysqldumpslow -s t -t 10 -g "JOIN" /var/log/mysql/slow.log

使用pt-query-digest工具分析慢查询日志

pt-query-digest是Percona Toolkit中的一个工具，它比mysqldumpslow功能更强大，可以提供更详细的慢查询分析报告。

-- 安装Percona Toolkit（在Linux系统中）
# Ubuntu/Debian
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

-- 使用pt-query-digest分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

-- 将分析报告保存到文件
pt-query-digest /var/log/mysql/slow.log > slow_query_analysis.txt

-- 分析特定时间范围内的慢查询
pt-query-digest --since="2023-01-01 00:00:00" --until="2023-01-02 00:00:00" /var/log/mysql/slow.log

-- 分析特定数据库的慢查询
pt-query-digest --filter="\$event->{db} eq 'database_name'" /var/log/mysql/slow.log

-- 分析特定用户的慢查询
pt-query-digest --filter="\$event->{user} eq 'username'" /var/log/mysql/slow.log

-- 分析包含特定字符串的慢查询
pt-query-digest --filter="\$event->{query} =~ m/SELECT/" /var/log/mysql/slow.log

删除慢查询日志的方法

-- 方法1：禁用慢查询日志
SET GLOBAL slow_query_log = OFF;

-- 方法2：清空文件格式的慢查询日志
-- 在Linux系统中
echo "" > /var/log/mysql/slow.log

-- 方法3：轮换文件格式的慢查询日志
-- 先重命名当前的慢查询日志文件
mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old
-- 然后刷新日志，创建新的慢查询日志文件
FLUSH SLOW LOGS;

-- 方法4：清空表格式的慢查询日志
TRUNCATE TABLE mysql.slow_log;

MySQL 8.0日志系统的主要改进

• 日志服务架构：引入了新的日志服务架构，将日志的过滤和存储分离，提供了更灵活的日志配置选项
• 错误日志改进：增强了错误日志的功能，支持多个错误日志输出目标，提供了更详细的错误信息
• 日志过滤：可以根据日志级别和内容过滤日志，减少日志文件的大小
• 日志轮换：提供了更灵活的日志轮换策略，可以根据时间或大小自动轮换日志文件
• 日志加密：支持对二进制日志和重做日志进行加密，提高数据安全性

使用日志服务配置错误日志

-- 在MySQL 8.0中，可以使用log_error_services参数配置多个错误日志服务
-- 查看当前的日志服务配置
SHOW VARIABLES LIKE 'log_error_services';

-- 配置多个日志服务（例如，同时输出到文件和控制台）
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_syseventlog';

-- 添加新的日志服务
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';

-- 移除某个日志服务
SET GLOBAL log_error_services = 'log_filter_internal; log_sink_internal';

使用日志过滤

-- 在MySQL 8.0中，可以使用log_filter_internal服务过滤错误日志
-- 设置日志过滤级别
SET GLOBAL log_error_verbosity = 2;  -- 只记录错误和警告信息

-- 配置更复杂的日志过滤规则
-- 例如，只记录特定组件的错误
INSTALL COMPONENT 'file://component_log_filter_dragnet';
SET GLOBAL log_error_services = 'log_filter_dragnet; log_sink_internal';
SET GLOBAL dragnet.log_error_filter_rules = 'include_diagnostics events WHERE component = ''InnoDB''';

-- 卸载日志过滤组件
UNINSTALL COMPONENT 'file://component_log_filter_dragnet';

配置日志轮换

-- 在MySQL 8.0中，可以配置日志轮换策略
-- 设置按时间轮换日志（单位：天）
SET GLOBAL log_rotate_age = 7;  -- 7天后轮换日志

-- 设置按大小轮换日志（单位：字节）
SET GLOBAL log_rotate_size = 104857600;  -- 100MB后轮换日志

-- 同时设置时间和大小轮换策略（满足任一条件即轮换）
SET GLOBAL log_rotate_age = 7;
SET GLOBAL log_rotate_size = 104857600;

-- 手动轮换日志
FLUSH ERROR LOGS;