印刻数据库

外观

MySQL 日志格式 ​

二进制日志（Binary Log） ​

二进制日志是 MySQL 中最重要的日志之一，它记录了所有数据更改事件，用于复制、恢复和审计。

二进制日志格式类型 ​

1. STATEMENT 格式 ​

基于语句的日志格式，记录执行的 SQL 语句。

特点：

  • 日志文件较小
  • 记录 SQL 语句，易于理解
  • 可能存在主从数据不一致的问题
  • 某些函数（如 NOW()、UUID()）可能导致主从数据不一致

配置：

ini
# 设置二进制日志格式为 STATEMENT
binlog_format = STATEMENT

示例：

# 二进制日志内容示例
# at 1234
#210101 10:00:00 server id 1  end_log_pos 5678 CRC32 0x12345678	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1609459200/*!*/;
INSERT INTO test_table (name) VALUES ('test')/*!*/;

2. ROW 格式 ​

基于行的日志格式，记录数据行的更改。

特点：

  • 日志文件较大
  • 记录行级更改，主从数据一致性好
  • 不依赖于 SQL 语句的执行环境
  • 支持更复杂的复制场景

配置：

ini
# 设置二进制日志格式为 ROW
binlog_format = ROW

示例：

# 二进制日志内容示例
# at 1234
#210101 10:00:00 server id 1  end_log_pos 5678 CRC32 0x12345678	Table_map: `test`.`test_table` mapped to number 123
# at 5678
#210101 10:00:00 server id 1  end_log_pos 9012 CRC32 0x87654321	Write_rows: table id 123 flags: STMT_END_F

BINLOG 'abcdefghijklmnopqrstuvwxyz' /*!*/;

3. MIXED 格式 ​

混合日志格式，根据语句自动选择 STATEMENT 或 ROW 格式。

特点：

  • 结合了 STATEMENT 和 ROW 格式的优点
  • 对于大多数语句使用 STATEMENT 格式
  • 对于可能导致主从不一致的语句使用 ROW 格式

配置：

ini
# 设置二进制日志格式为 MIXED
binlog_format = MIXED

二进制日志文件结构 ​

二进制日志由多个文件组成：

  1. 索引文件：以 .index 结尾，记录所有二进制日志文件的名称
  2. 日志文件：以 -bin.xxxxxx 结尾，记录实际的二进制日志内容

二进制日志事件格式 ​

每个二进制日志事件包含以下部分：

  • 事件头：包含事件类型、服务器 ID、时间戳等
  • 事件数据：包含实际的日志内容
  • 事件尾：包含事件长度、CRC32 校验等

查看二进制日志内容 ​

使用 mysqlbinlog 命令查看二进制日志内容：

bash
# 查看二进制日志内容
mysqlbinlog mysql-bin.000001

# 以 ROW 格式查看二进制日志
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001

# 查看特定时间范围的二进制日志
mysqlbinlog --start-datetime="2021-01-01 10:00:00" --stop-datetime="2021-01-01 11:00:00" mysql-bin.000001

错误日志（Error Log） ​

错误日志记录 MySQL 服务器的启动、关闭和错误信息，是诊断 MySQL 问题的重要工具。

错误日志格式 ​

错误日志的格式通常包括：

  • 时间戳：日志记录的时间
  • 错误级别：日志的严重程度（Note, Warning, Error, Critical）
  • 线程 ID：产生日志的线程 ID
  • 错误信息：详细的错误描述

错误日志示例 ​

2021-01-01T10:00:00.123456Z 0 [Note] [MY-010908] [Server] /usr/sbin/mysqld: ready for connections.
Version: '8.0.23'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2021-01-01T10:01:00.234567Z 1 [Warning] [MY-010055] [Server] IP address '192.168.1.1' could not be resolved: Name or service not known
2021-01-01T10:02:00.345678Z 2 [Error] [MY-010811] [Server] Can't create/write to file '/var/lib/mysql/ibdata1' (OS errno 28 - No space left on device)

错误日志配置 ​

配置参数：

ini
# 启用错误日志
log_error = /var/log/mysql/error.log

# 设置错误日志级别
log_error_verbosity = 3

错误日志级别：

  • 1：仅记录错误信息
  • 2：记录错误和警告信息
  • 3：记录错误、警告和注意信息

查看错误日志 ​

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

# 实时查看错误日志
tail -f /var/log/mysql/error.log

查询日志（General Query Log） ​

查询日志记录所有 MySQL 服务器接收到的查询语句，包括成功和失败的查询。

查询日志格式 ​

查询日志的格式通常包括：

  • 时间戳：查询执行的时间
  • 线程 ID：执行查询的线程 ID
  • 用户：执行查询的用户名和主机
  • 查询语句：完整的 SQL 语句

查询日志示例 ​

2021-01-01T10:00:00.123456Z	1	Query	SELECT * FROM test_table
2021-01-01T10:01:00.234567Z	1	Query	INSERT INTO test_table (name) VALUES ('test')
2021-01-01T10:02:00.345678Z	2	Query	UPDATE test_table SET name = 'updated' WHERE id = 1

查询日志配置 ​

配置参数：

ini
# 启用查询日志
general_log = ON

# 设置查询日志文件路径
general_log_file = /var/log/mysql/query.log

查看查询日志 ​

bash
# 查看查询日志
tail -n 100 /var/log/mysql/query.log

# 实时查看查询日志
tail -f /var/log/mysql/query.log

慢查询日志（Slow Query Log） ​

慢查询日志记录执行时间超过指定阈值的查询语句，用于性能分析和优化。

慢查询日志格式 ​

慢查询日志的格式通常包括：

  • 时间戳：查询开始执行的时间
  • 用户：执行查询的用户名和主机
  • 查询时间：查询执行的时间（秒）
  • 锁定时间：查询锁定表的时间（秒）
  • 返回行数：查询返回的行数
  • 扫描行数：查询扫描的行数
  • 使用的数据库：查询使用的数据库
  • 查询语句：完整的 SQL 语句

慢查询日志示例 ​

# 慢查询日志示例
# Time: 2021-01-01T10:00:00.123456Z
# User@Host: root[root] @ localhost []  Id:     1
# Query_time: 1.234567  Lock_time: 0.123456 Rows_sent: 100  Rows_examined: 10000
use test_db;
SET timestamp=1609459200;
SELECT * FROM large_table WHERE name LIKE '%test%';

慢查询日志配置 ​

配置参数：

ini
# 启用慢查询日志
slow_query_log = ON

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log

# 设置慢查询阈值（秒）
long_query_time = 2

# 记录没有使用索引的查询
log_queries_not_using_indexes = ON

# 记录管理语句
log_slow_admin_statements = ON

# 记录慢查询的最小扫描行数
min_examined_row_limit = 1000

查看慢查询日志 ​

bash
# 查看慢查询日志
tail -n 100 /var/log/mysql/slow.log

# 使用 mysqldumpslow 工具分析慢查询日志
mysqldumpslow /var/log/mysql/slow.log

# 查看最慢的 10 个查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

中继日志（Relay Log） ​

中继日志是从库上存储主库二进制日志的日志文件，用于复制过程。

中继日志格式 ​

中继日志的格式与二进制日志格式相同，包括 STATEMENT、ROW 和 MIXED 三种格式。

中继日志配置 ​

配置参数：

ini
# 设置中继日志文件前缀
relay_log = /var/lib/mysql/relay-bin

# 设置中继日志索引文件
relay_log_index = /var/lib/mysql/relay-bin.index

# 设置中继日志大小限制
max_relay_log_size = 1G

# 自动清理中继日志
relay_log_purge = ON

查看中继日志 ​

使用 mysqlbinlog 命令查看中继日志内容：

bash
# 查看中继日志内容
mysqlbinlog /var/lib/mysql/relay-bin.000001

事务日志（InnoDB Redo Log） ​

事务日志是 InnoDB 存储引擎用于保证事务持久性的日志，记录了数据页的更改。

事务日志格式 ​

事务日志以固定大小的块（block）形式存储，每个块包含多个日志记录。

事务日志配置 ​

配置参数：

ini
# 设置事务日志文件路径
innodb_log_group_home_dir = /var/lib/mysql/

# 设置单个事务日志文件大小
innodb_log_file_size = 512M

# 设置事务日志文件数量
innodb_log_files_in_group = 2

# 设置事务日志缓冲区大小
innodb_log_buffer_size = 16M

查看事务日志 ​

事务日志是二进制格式，不能直接查看。可以通过 InnoDB 监控工具查看事务日志的状态：

sql
-- 查看事务日志状态
SHOW ENGINE INNODB STATUS\G

二进制日志与事务日志的区别 ​

特性二进制日志事务日志
存储引擎所有存储引擎仅 InnoDB
记录内容数据更改事件数据页更改
用途复制、恢复、审计事务持久性、崩溃恢复
格式STATEMENT、ROW、MIXED固定二进制格式
大小限制可配置可配置

日志格式的选择建议 ​

二进制日志格式选择 ​

  • 开发环境：可以使用 STATEMENT 或 MIXED 格式，便于调试
  • 生产环境：推荐使用 ROW 格式，确保主从数据一致性
  • 特殊场景：如需要减小日志文件大小，可以考虑使用 STATEMENT 格式

日志启用建议 ​

  • 二进制日志：生产环境必须启用，用于复制和恢复
  • 错误日志：生产环境必须启用，用于故障诊断
  • 查询日志：生产环境不建议启用，会影响性能
  • 慢查询日志：生产环境建议启用，用于性能优化
  • 中继日志：从库自动启用，无需手动配置

日志文件的管理 ​

日志文件的轮转 ​

  • 二进制日志：自动轮转，可通过 FLUSH LOGS 命令手动触发
  • 错误日志：需要手动轮转，或使用日志管理工具
  • 查询日志：自动轮转，可通过 FLUSH LOGS 命令手动触发
  • 慢查询日志：自动轮转，可通过 FLUSH LOGS 命令手动触发

日志文件的清理 ​

  • 二进制日志：使用 PURGE BINARY LOGS 命令清理
  • 错误日志：手动删除或使用日志管理工具
  • 查询日志：手动删除或使用日志管理工具
  • 慢查询日志：手动删除或使用日志管理工具

日志文件的备份 ​

  • 二进制日志：必须定期备份，用于恢复
  • 错误日志：建议定期备份，用于故障诊断
  • 慢查询日志：根据需要备份，用于性能分析

不同版本的日志格式差异 ​

MySQL 5.5 及之前 ​

  • 二进制日志默认格式为 STATEMENT
  • 不支持 ROW 格式的完整功能
  • 慢查询日志格式较为简单

MySQL 5.6 ​

  • 二进制日志默认格式为 STATEMENT
  • 增强了 ROW 格式的功能
  • 引入了更多的日志配置选项

MySQL 5.7 ​

  • 二进制日志默认格式为 ROW
  • 增强了日志的安全性（如 CRC32 校验）
  • 引入了更多的日志监控指标

MySQL 8.0 ​

  • 二进制日志默认格式为 ROW
  • 增强了日志的性能
  • 引入了更多的日志管理功能
  • 支持日志的加密

常见问题（FAQ） ​

Q1: 如何选择合适的二进制日志格式？ ​

A1: 选择二进制日志格式应考虑以下因素：

  • 数据一致性：如果要求主从数据严格一致，推荐使用 ROW 格式
  • 日志大小：如果存储空间有限，可以考虑使用 STATEMENT 格式
  • 复制场景：如果涉及复杂的复制场景（如跨版本复制），推荐使用 ROW 格式
  • 性能影响：ROW 格式可能对性能有一定影响，尤其是在大量更新操作时

Q2: 为什么生产环境不建议启用查询日志？ ​

A2: 生产环境不建议启用查询日志的原因包括：

  • 性能影响：查询日志会记录所有查询，对性能影响较大
  • 日志文件过大：查询日志文件会快速增长，占用大量磁盘空间
  • 安全风险：查询日志可能包含敏感信息（如密码）
  • 调试需求有限：生产环境一般通过慢查询日志和错误日志进行调试

Q3: 如何优化慢查询日志的性能影响？ ​

A3: 优化慢查询日志性能影响的方法包括：

  • 设置合理的慢查询阈值（如 2 秒）
  • 只记录需要的慢查询（如通过 min_examined_row_limit 过滤）
  • 定期清理慢查询日志
  • 考虑使用性能模式（Performance Schema）替代慢查询日志

Q4: 如何查看二进制日志的内容？ ​

A4: 可以使用 mysqlbinlog 命令查看二进制日志的内容。对于 ROW 格式的二进制日志，需要使用 --base64-output=decode-rows -v 参数来解码和格式化输出。

Q5: 如何管理大量的日志文件？ ​

A5: 管理大量日志文件的方法包括：

  • 设置合理的日志轮转策略
  • 定期清理过期的日志文件
  • 使用日志管理工具（如 logrotate）自动管理日志
  • 考虑使用集中式日志管理系统（如 ELK Stack）

Q6: 日志加密有什么好处？ ​

A6: 日志加密的好处包括：

  • 保护敏感数据（如用户密码、个人信息）
  • 符合合规要求（如 GDPR、PCI DSS）
  • 防止日志文件被未授权访问
  • 增强数据安全性

Q7: 如何监控日志文件的增长？ ​

A7: 监控日志文件增长的方法包括：

  • 使用监控工具（如 Prometheus + Grafana）监控日志文件大小
  • 设置日志文件大小告警
  • 定期检查日志文件大小
  • 配置合理的日志轮转策略