MySQL教程FG174-MySQL二进制日志管理

内容简介：MySQL二进制日志是数据库中最重要的日志之一，记录了所有数据修改操作，用于复制、恢复和审计。本文风哥教程参考MySQL官方文档MySQL Server Administration部分，详细介绍MySQL二进制日志的配置、管理、备份和恢复方法，帮助DBA高效管理MySQL二进制日志系统。学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

1.1 MySQL二进制日志概述

MySQL二进制日志（Binary Log）记录了所有对数据库进行修改的操作，包括：

数据修改语句（INSERT、UPDATE、DELETE）
表结构修改语句（CREATE、ALTER、DROP）
用户权限修改语句（GRANT、REVOKE）
事务信息和状态

二进制日志的主要用途：

数据恢复：用于基于时间点或位置的恢复
主从复制：从服务器通过读取主服务器的二进制日志进行数据同步
审计：记录所有数据修改操作，用于安全审计

1.2 二进制日志格式

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

STATEMENT格式：记录执行的SQL语句，日志体积小，但可能存在主从不一致的问题
ROW格式：记录每行数据的变化，保证主从一致性，但日志体积大
MIXED格式：自动选择STATEMENT或ROW格式，平衡日志体积和一致性

Part02-生产环境规划与建议

2.1 二进制日志存储规划

在生产环境中，二进制日志的存储需要精心规划：

独立磁盘：将二进制日志存储在与数据文件不同的磁盘上，避免I/O竞争
高性能磁盘：使用高速磁盘（如SSD）存储二进制日志，提高写入性能
足够空间：为二进制日志预留足够的存储空间，根据业务量和保留策略确定
安全存储：将二进制日志备份到安全的位置，防止数据丢失

2.2 二进制日志性能影响

二进制日志会对数据库性能产生一定影响，需要合理配置：

日志格式：ROW格式比STATEMENT格式性能开销大，但保证一致性
同步策略：sync_binlog参数控制二进制日志的同步方式，值为1时最安全但性能最低
日志大小：合理设置max_binlog_size参数，避免单个日志文件过大
过期时间：配置expire_logs_days参数，自动清理旧的二进制日志

风哥提示：在生产环境中，建议使用ROW格式的二进制日志，确保主从复制的一致性。

Part03-生产环境项目实施方案

3.1 二进制日志配置

MySQL二进制日志配置主要通过my.cnf配置文件实现。

# 配置二进制日志
# vi /etc/my.cnf
[mysqld]
# 开启二进制日志
log_bin = /mysql/binlog/binlog
# 二进制日志索引文件
log_bin_index = /mysql/binlog/binlog.index
# 二进制日志格式（ROW, STATEMENT, MIXED）
binlog_format = ROW
# 二进制日志最大大小（字节）
max_binlog_size = 1G
# 二进制日志过期时间（天）
expire_logs_days = 14
# 同步二进制日志到磁盘（1为最安全，0为最高性能）
sync_binlog = 1
# 二进制日志行图像（FULL, MINIMAL, NOBLOB）
binlog_row_image = FULL
# 二进制日志校验和（CRC32, NONE）
binlog_checksum = CRC32
# 主服务器ID（复制环境必需）
server_id = 1

3.2 二进制日志管理

二进制日志的日常管理包括查看、删除和刷新等操作。

# 查看二进制日志文件列表
mysql> SHOW BINARY LOGS;
+—————+———–+
| Log_name | File_size |
+—————+———–+
| binlog.000001 | 1073741824|
| binlog.000002 | 1073741824|
| binlog.000003 | 567890123|
| binlog.000004 | 1234567|
+—————+———–+

# 刷新二进制日志（生成新的日志文件）
mysql> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.01 sec)

# 查看刷新后的二进制日志
mysql> SHOW BINARY LOGS;
+—————+———–+
| Log_name | File_size |
+—————+———–+
| binlog.000001 | 1073741824|
| binlog.000002 | 1073741824|
| binlog.000003 | 567890123|
| binlog.000004 | 1234654|
| binlog.000005 | 154 |
+—————+———–+

# 删除指定日期之前的二进制日志
mysql> PURGE BINARY LOGS BEFORE ‘2026-03-15’;
Query OK, 0 rows affected (0.01 sec)

# 删除指定文件之前的二进制日志
mysql> PURGE BINARY LOGS TO ‘binlog.000003’;
Query OK, 0 rows affected (0.01 sec)

# 重置二进制日志（删除所有日志并重新开始）
mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

3.3 二进制日志备份

二进制日志备份是数据恢复的重要保障，需要定期备份。

#!/bin/bash
# binlog_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

BINLOG_DIR=”/mysql/binlog”
BACKUP_DIR=”/backup/mysql/binlog”
DATE=$(date +%Y%m%d)

# 创建备份目录
mkdir -p $BACKUP_DIR/$DATE

# 刷新二进制日志，生成新的日志文件
mysql -u root -p”password” -e “FLUSH BINARY LOGS;”

# 备份二进制日志文件
# 只备份今天之前创建的日志文件
today_start=$(date +%Y-%m-%d” “00:00:00)
find $BINLOG_DIR -name “binlog.*” -type f -mtime -1 ! -newer “$today_start” | xargs cp -t $BACKUP_DIR/$DATE/

# 压缩备份文件
gzip -r $BACKUP_DIR/$DATE/

# 删除超过30天的备份文件
find $BACKUP_DIR -name “*.gz” -mtime +30 -delete

echo “二进制日志备份完成: $(date)”
echo “备份文件存储在: $BACKUP_DIR/$DATE/”

# 运行二进制日志备份脚本
# chmod +x /mysql/scripts/binlog_backup.sh
# /mysql/scripts/binlog_backup.sh
二进制日志备份完成: Tue Apr 2 11:30:00 2026
备份文件存储在: /backup/mysql/binlog/20260402/

# 查看备份文件
# ls -la /backup/mysql/binlog/20260402/
-rw-r–r– 1 root root 567890123 Apr 2 11:30 binlog.000001.gz
-rw-r–r– 1 root root 1073741824 Apr 2 11:30 binlog.000002.gz
-rw-r–r– 1 root root 890123456 Apr 2 11:30 binlog.000003.gz

Part04-生产案例与实战讲解

4.1 基于二进制日志的恢复

使用二进制日志进行基于时间点或位置的恢复。

案例：误删除数据恢复
问题描述：用户误删除了fgedu_student表中的数据，需要恢复到删除前的状态
解决方法：使用全量备份和二进制日志进行恢复

# 1. 查找误删除操作的时间和位置
# mysqlbinlog /mysql/binlog/binlog.000004 | grep -A 5 -B 5 “DELETE FROM fgedu_student”
# at 12345
#260402 11:35:00 server id 1 end_log_pos 12456 CRC32 0x12345678 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= ‘12345678-1234-1234-1234-1234567890ab:123’/*!*/;
# at 12456
#260402 11:35:00 server id 1 end_log_pos 12567 CRC32 0x87654321 Query thread_id=1234 exec_time=0 error_code=0
SET TIMESTAMP=1712025300/*!*/;
DELETE FROM fgedu_student WHERE id > 1000/*!*/;

# 2. 恢复全量备份
# mysql -u root -p”password” fgedudb < /backup/mysql/full_backup_20260401.sql # 3. 应用二进制日志到误删除操作之前 # mysqlbinlog --stop-datetime="2026-04-02 11:34:59" /mysql/binlog/binlog.000004 | mysql -u root -p"password" fgedudb # 或者使用位置恢复 # mysqlbinlog --stop-position=12345 /mysql/binlog/binlog.000004 | mysql -u root -p"password" fgedudb # 4. 验证数据恢复 mysql> SELECT COUNT(*) FROM fgedu_student;
+———-+
| COUNT(*) |
+———-+
| 1500 |
+———-+
1 row in set (0.01 sec)

4.2 二进制日志与复制

二进制日志是MySQL主从复制的基础，从服务器通过读取主服务器的二进制日志进行数据同步。

# 从服务器配置复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.1′,
-> MASTER_USER=’repl_user’,
-> MASTER_PASSWORD=’repl_password’,
-> MASTER_LOG_FILE=’binlog.000004′,
-> MASTER_LOG_POS=12345;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

# 查看复制状态
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 12345
Relay_Log_File: fgedu.net.cn-relay-bin.000001
Relay_Log_Pos: 283
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12345
Relay_Log_Space: 490
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 12345678-1234-1234-1234-1234567890ab:1-100
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)

4.3 二进制日志分析

使用mysqlbinlog工具分析二进制日志内容。

# 查看二进制日志的基本信息
# mysqlbinlog –base64-output=decode-rows –verbose /mysql/binlog/binlog.000004 | head -n 50
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260402 11:40:00 server id 1 end_log_pos 123 CRC32 0x12345678 Start: binlog v 4, server v 8.4.0 created 260402 11:40:00
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#260402 11:40:05 server id 1 end_log_pos 234 CRC32 0x87654321 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= ‘12345678-1234-1234-1234-1234567890ab:101’/*!*/;
# at 234
#260402 11:40:05 server id 1 end_log_pos 345 CRC32 0xabcdef12 Query thread_id=5678 exec_time=0 error_code=0
SET TIMESTAMP=1712025605/*!*/;
SET @@session.pseudo_thread_id=5678/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1134118272/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*! utf8mb4 *//*!/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!/;
BEGIN
/*!*/;
# at 345
#260402 11:40:05 server id 1 end_log_pos 456 CRC32 0x12ab34cd Table_map: `fgedudb`.`fgedu_orders` mapped to number 123
# at 456
#260402 11:40:05 server id 1 end_log_pos 567 CRC32 0x56ef78ab Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `fgedudb`.`fgedu_orders`
### SET
### @1=1001 /* INT meta=0 nullable=0 is_null=0 */
### @2=100 /* INT meta=0 nullable=0 is_null=0 */
### @3=’2026-04-02′ /* DATE meta=0 nullable=0 is_null=0 */
### @4=199.99 /* DECIMAL(10,2) meta=2 nullable=0 is_null=0 */
### @5=’completed’ /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
# at 567
#260402 11:40:05 server id 1 end_log_pos 598 CRC32 0x90ab12cd Xid = 123456
COMMIT/*!*/;

Part05-风哥经验总结与分享

5.1 二进制日志管理最佳实践

开启二进制日志：生产环境必须开启二进制日志，用于数据恢复和复制
选择合适的日志格式：建议使用ROW格式，确保主从复制的一致性
合理设置日志大小：max_binlog_size建议设置为1GB，便于管理和备份
配置日志过期时间：根据存储空间和恢复需求，设置合理的expire_logs_days
定期备份日志：将二进制日志备份到安全的位置，避免数据丢失
监控日志增长：监控二进制日志的增长速度，及时发现异常
安全保护：设置适当的文件权限，防止未授权访问二进制日志

5.2 二进制日志故障排查

风哥提示：二进制日志故障可能导致数据恢复失败或复制中断，需要及时排查和处理。

日志文件损坏：使用mysqlbinlog工具检查日志文件完整性，从备份恢复
日志文件丢失：检查expire_logs_days设置，确保重要日志未被自动删除
复制中断：检查从服务器的错误日志，使用SHOW SLAVE STATUS查看复制状态
日志写入失败：检查磁盘空间和权限，确保MySQL用户有写入权限
性能问题：调整sync_binlog参数，平衡安全性和性能

生产环境建议：建立二进制日志管理的标准化流程，包括配置、监控、备份、恢复和故障处理等环节，确保数据库的高可用性和数据安全性。更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html