mysql的日志管理
错误日志
MySQL错误日志功能默认是开启状态，默认的存放位置在数据目录("datadir")下，默认的文件名称为:"主机名.err"。
指定MySQL错误日志的文件名称及存放位置:
cat /etc/my.cnf
[mysqld]
log_error=/usr/local/mysql/logs/mysql_error.log
systemctl restart mysqld
# 需要保证/usr/local/mysql/logs/mysql_error.log文件已存在，并且mysql有写入权限
MySQL通用日志
通用日志的作用: 用于临时调试,可以做简单的日志审计功能
常用参数: 在配置文件的[mysqld]下配置
# 如果general_log_file的父目录不存在或无权限访问时,尽管将改参数设置为ON也无效。
general_log=ON
general_log_file: 指定通用日志的存储路径
# 通用日志功能，生产环境建议关闭，可能会影响性能，如果调试可以临时打开,不建议将它审计
MySQL慢日志
MySQL慢日志概述
MySQL慢日志记录内容: 记录mysql在运行过程中执行较慢的语句，通过一个文本文件记录下来。
MySQL慢日志作用: 主要帮助DBA进行语句优化的工具日志。
慢日志相关参数
# 查看是否开启慢日志功能。默认情况下，MySQL是没有开启慢日志查询功能的
mysql> SELECT @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.01 sec)
# 查看慢日志文件存储位置
mysql> SELECT @@slow_query_log_file;
+--------------------------------------------------------+
| @@slow_query_log_file |
+--------------------------------------------------------+
| /usr/local/mysql/data/iZuf60i96pjsch5l0rvqi7Z-slow.log |
+--------------------------------------------------------+
1 row in set (0.00 sec)
# 查看慢语句的阈值，即超出该阈值则默认为慢SQL语句,默认情况下，超过10秒的SQL均被视为慢语句查询。生产环境中我们可以循序渐进设置阈值，比如0.1~3秒均可。
mysql> SELECT @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
# 查看是否记录不走索引的语句。默认情况下并不记录。
mysql> SELECT @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
启动慢日志功能
cat /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/usr/local/mysql/logs/mysql_slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
systemctl restart mysqld
# 需要保证/usr/local/mysql/logs/mysql_slow.log文件已存在，并且mysql有写入权限
使用mysqldumpslow工具来分析慢日志
直接查看慢日志尽管也可以，但还是适用于日志量较少的情况下，日志较多后，可读性较差了，因此MySQL官方给咱们提供了"mysqldumpslow"工具来进行慢日志分析，常用的参数说明如下:
mysqldumpslow -s c -t 3 /usr/local/mysql/logs/mysql_slow.log
-s c：指定按照查询次数（count）对结果进行排序。`c` 表示按查询出现的次数从高到低排序。
-t 3: 这个选项限制输出的结果数量，这里指定了只显示前3个最频繁的慢查询。
MySQL二进制日志
二进制日志概述
- MySQL二进制日志记录内容: 记录mysql数据变化性质的日志(比如会记录"DDL","DCL","DML"等操作，并不会记录DQL，因为"SELECT"操作并不会改变数据)，属于逻辑层性质日志，并不会记录数据页的变化。
- MySQL二进制日志作用: 主要用于数据恢复和主从复制中的应用。
配置二进制日志
启动二进制日志可能会用到以下几个常用几个参数:
- server_id: 主机编号，主从复制中必须开启此参数。
- log_bin: 指定二进制日志的前缀名称，通常需要写一个绝对路径。
- sync_binlog: binlog日志刷写策略，我们通常设置为"1"，表示每次事物提交理都刷写"binlog buffer"到磁盘，其目的是为了保证数据的持久性与安全性。如果我们将该值设置为"0"，则每次提交事务并不会立即刷写已提交的事务到磁盘，而是交由操作系统来完成事务的刷写，尽管性能上是有所提升的，但这同样也带来了高风险，即当操作意外断电崩溃，将意味着数据MySQL部分已提交的事务数据将丢失。
- binlog_format: binlog的记录格式，我们通常设置为"row"模式。
- expire_logs_days: 若不设置，默认值为0，表示永不删除二进制日志(binlog)。生产环境建议设置最少2个全量备份周期(如果你是每周做一次全量备份，那么你的周期就是7天)+1，目的是最近一个全量备份若损坏则可以考虑使用上一个的备份。
启用MySQL二进制日志功能:
cat /etc/my.cnf
server_id=7
log_bin=/usr/local/mysql/logs/binlog.log
sync_binlog=1
binlog_format=row
systemctl restart mysqld
# 需要保证/usr/local/mysql/logs/binlog.log存在且有读写权限
二进制日志(binlog)记录内容详解
记录SQL语句种类
- DDL: 原封不动的记录当前DDL的statement语句方式。
- DCL:原封不动的记录当前DCL的statement语句方式。
- DML:只记录已提交的DML事务。
DML三种记录方式
- statement: 这是MySQL 5.6默认的记录方式，即Statement Based Replication(简称"SBR")，这种记录方式会原封不动的记录当前的DML。
- 优点: 可读性较高，日志量较少，因为它是原封不动的记录用户执行的SQL语句。
- 缺点: 这种记录方式并不严谨('SELECT NOW()')，这也是为什么MySQL 5.7开始就不在使用该模式了。
- ROW: 这是MySQL 5.7默认的记录方式，即Row Based Replication(简称"RBR")，这种记录方式会记录数据行的变化，这种方式可读性极差，需要借助工具进行分析，但记录的方式也是最严谨的，生产环境中推荐大家使用这种记录方式。
- 优点：弥补了statement模式不够严谨的缺点。
- 缺点: 可读性极差，日志量较大，因为他会记录每一行的数据变化。
- mixed: 这是混合模式，即Mixed Based Replication(简称"MBR")，这种记录方式会将上面的statement和ROW这两种模式混合使用，生产环境中很少使用。
综上所述，生产环境中我们使用的"ROW"更为准确，其性能可能相比statement较差，但为了数据准确性，我们通常愿意牺牲一些性能。
二进制日志最小记录单元-事件(event)
事件(event)简介： 众所周知，事件(event)是二进制最小记录单元。对于DDL和DCL而言，一个语句就是一个事件(event)，而对于DML语句而言，只记录已提交的事务。
事件(event)的组成:
- 事件的开始标识(at position，例如: at 219);
- 事件内容(记录的DDL,DCL和已提交的DML语句);
- 事件的结束标识(end_log_pos position，例如: End_log_pos 343);
位置编号(position)是记录某个事件在binlog中的相对位置编号，为了方便我们截取事件。
二进制日志相关信息查看
# 查看二进制日志(binlog)功能是否启用
mysql> SELECT @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
# 二进制日志(binlog)文件的前缀名称。
mysql> SELECT @@log_bin_basename;
+------------------------------+
| @@log_bin_basename |
+------------------------------+
| /usr/local/mysql/logs/binlog |
+------------------------------+
1 row in set (0.00 sec)
# 查看目前有几个日志文件:
mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 156 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
# 查看当前在用的二进制日志(binlog)文件:
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查看二进制日志(binlog)事件:
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'binlog.000001' ;
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000001 | 4 | Format_desc | 7 | 125 | Server ver: 8.0.25, Binlog ver: 4 |
| binlog.000001 | 125 | Previous_gtids | 7 | 156 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
二进制日志(binlog)滚动机制
- MySQL内部基于"max_binlog_size"指定的1GB文件大小来自动触发日志滚动，也就是说当日志的文件大小达到"1GB"时，会自动触发日志滚动，当然生产环境中，我们也可以修改配置文件来自定义日志滚动的文件大小;
- 可以通过mysql工具提供的字符交互式终端执行"FLUSH LOGS;"命令会手动触发日志滚动，我们也可以借助mysqldump，mysqladmin等工具来触发日志滚动;
- 重启MySQL数据库实例时，也会自动触发日志滚动;
二进制日志(binlog)删除
- MySQL内部基于"expire_logs_days"参数来删除二进制日志(binlog)文件，若不设置，默认值为0，表示永不删除二进制日志(binlog)。生产环境建议设置最少2个全量备份周期(如果你是每周做一次全量备份，那么你的周期就是7天)+1，目的是最近一个全量备份若损坏则可以考虑使用上一个的备份。
- 我们可以借助"PURGE BINARY LOGS"命令工具来手动删除二进制日志(binlog)文件，也可以借助"reset master"命令来手动清空全部的二进制日志文件(新的日志文件名称编号将从"000001"开始命名，此命令比较危险，如果在主从架构中的主库执行该命令，那么主从复制会立即失效);
- 注意，千万不要手动使用"rm"命令来删除二进制日志(binlog)文件。
手动删除
# 删除二进制文件：删除所有编号小于binlog.000008的二进制日志文件，如果binlog.000008是当前正在使用的日志文件，则不会删除任何文件
mysql> PURGE BINARY LOGS TO 'binlog.000008';
Query OK, 0 rows affected (0.00 sec)
# 删除指定时间点之前创建的所有二进制日志文件，可以根据时间精确控制删除范围，适合定期清理
PURGE BINARY LOGS BEFORE '2025-02-04 16:44:17';
# 删除 15 天前的所有 binlog（以当前时间计算）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 15 DAY);
# 手动清空全部的二进制日志文件(新的日志文件名称编号将从"000001"开始命名，此命令比较危险）
RESET MASTER;
定时自动删除
临时生效（当前会话有效，重启后失效）：
# 设置 binlog 保留 7 天（适用于 MySQL 5.7 及更早版本）
SET GLOBAL expire_logs_days = 7;
# MySQL 8.0+ 推荐使用（设置保留 7 天，7*24*3600=604800 秒）
SET GLOBAL binlog_expire_logs_seconds = 604800;
永久生效（需修改配置文件）：
vim /etc/my.cnf
[mysqld]
# 对于 MySQL 5.7 及更早版本
expire_logs_days = 7 # 保留 7 天
# 对于 MySQL 8.0+ 推荐使用
binlog_expire_logs_seconds = 604800 # 保留 7 天（604800 秒）
# 重启
systemctl restart mysqld
# 验证
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
中继日志
在 MySQL 主从复制架构中，Relay Log（中继日志） 是从服务器（Slave）用于存储主服务器（Master）二进制日志（Binary Log）副本的临时日志文件。
自动删除
通过配置 relay_log_purge = 1（默认开启），从服务器会在中继日志中的事件被 SQL 线程执行完毕且不再需要时自动删除旧日志（依赖主服务器的二进制日志是否已被清理）。
浙公网安备 33010602011771号