MySQL数据库备份工具：binlog详细操作与实战指南

MySQL的binlog（二进制日志）是MySQL数据库中非常重要的日志文件，它记录了所有对数据库的修改操作（如INSERT、UPDATE、DELETE等）。通过 binlog，我们可以实现数据恢复、主从复制、数据审计等功能。因此，定期备份binlog是数据库运维中的关键任务之一。

1 binlog基本概念

1.1 binlog的作用

binlog是MySQL的二进制日志文件，记录了所有对数据库的修改操作。具有以下特点：

记录内容：SQL语句或行数据的变化（取决于binlog_format配置)

作用：

• 数据恢复：当数据库发生误操作或数据丢失时，可以通过binlog恢复到指定时间点

• 主从复制：binlog是实现MySQL主从复制的核心，从库通过读取主库的binlog来同步数据
• 数据审计：binlog记录了所有对数据库的修改操作，可以用于数据审计和问题排查

1.2 binlog的三种格式

# 查看当前的binlog格式
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

2 启用binlog

在MySQL中，binlog默认是关闭的，要启用binlog，需要修改MySQL配置文件.

2.1 启用binlog操作步骤

1.编辑MySQL配置文件（通常是/etc/my.cnf）
vim /etc/my.cnf
# [mysqld] 部分添加以下配置：
[mysqld]
log_bin = /data/mysql/log/mysql-bin.log  # 启用binlog，并指定文件路径
server_id = 1                        # 设置服务器唯一 ID（主从复制时需要）
binlog_format = STATEMENT                  # 设置binlog格式为STATEMENT
expire_logs_days = 7                 # binlog文件保留7天

2.重启MySQL服务
systemctl restart mysqld

2.2 验证是否启用

# 命令
SHOW VARIABLES LIKE 'log_bin';

# 输出示例
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 

3 查看binlog文件

3.1 查看binlog文件列表

# 命令
show binary logs;

# 输出示例
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       398 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> 

3.2 查看当前正在使用的binlog文件

# 命令
show master status;

# 输出示例
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | binlog_Do_DB | binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      398 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

mysql> 

3.3 查看binlog文件内容

# 命令
mysqlbinlog /data/mysql/log/mysql-bin.000001

# 输出示例
[root@node3 log]# mysqlbinlog /data/mysql/log/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#250315 19:54:50 server id 100  end_log_pos 123 CRC32 0xfca8eab0        Start: binlog v 4, server v 5.7.43-log created 250315 19:54:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
binlog '
imrVZw9kAAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKatVnEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbDqqPw=
'/*!*/;
# at 123
#250315 19:54:50 server id 100  end_log_pos 154 CRC32 0x0ad1d83d        Previous-GTIDs
# [empty]
# at 154
#250315 19:55:50 server id 100  end_log_pos 219 CRC32 0x54470a2a        Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#250315 19:55:50 server id 100  end_log_pos 398 CRC32 0x2e75e0e5        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1742039750/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*957813CB0CF9EE59CA0B5D537F62E14D825D3C96'
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node3 log]# 

 4 手动刷新binlog

# 命令
flush binary logs;

# 输出示例
mysql> flush binary logs;
Query OK, 0 rows affected (0.02 sec)

mysql> 

说明：执行手动刷新命令后，MySQL会关闭当前的binlog文件并创建一个新的binlog文件，便于备份
mysql> flush binary logs;
Query OK, 0 rows affected (0.01 sec)

mysql>  show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       445 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> 

5 binlog备份数据

5.1 导出指定时间范围的binlog

mysqlbinlog --start-datetime="2025-03-15 00:00:00" --stop-datetime="2025-03-15 19:00:00" /data/mysql/log/mysql-bin.000002 > /data/mysql/backup/binlog_20250315.sql

参数说明：
--start-datetime：指定开始时间
--stop-datetime：指定结束时间
/data/mysql/log/mysql-bin.000005：要导出的binlog文件
/data/mysql/tmp/backup/binlog_20250314.sql：导出的SQL文件路径

5.2 导出指定位置范围的binlog

mysqlbinlog --start-position=123 --stop-position=456 /data/mysql/log/mysql-bin.000002 > /data/mysql/backup/binlog_position.sql

5.3 解码ROW格式的binlog

# 如果binlog格式为 ROW，需要使用--base64-output=DECODE-ROWS选项解码
mysqlbinlog --base64-output=DECODE-ROWS --verbose /data/mysql/log/mysql-bin.000002 > /backup/binlog_decoded.sql

6 实战案例：基于binlog恢复数据

6.1 准备工作

# 在mydb数据库中创建一张临时表，并在临时表插入10条数据
mysql> use mydb;
Database changed
mysql> CREATE TABLE tmp_user (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL,
    ->     age INT
    -> ) ENGINE=InnoDB;
INSERT INTO tmp_user (name, age) VALUES
('A', 25),
('B', 30),
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO tmp_user (name, age) VALUES
    -> ('A', 25),
    -> ('B', 30),
    -> ('C', 22),
    -> ('D', 28),
    -> ('E', 26),
    -> ('F', 35),
    -> ('G', 29),
    -> ('H', 31),
    -> ('I', 27),
    -> ('J', 24);
commit;Query OK, 10 rows affected (0.07 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select *from tmp_user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | A    |   25 |
|  2 | B    |   30 |
|  3 | C    |   22 |
|  4 | D    |   28 |
|  5 | E    |   26 |
|  6 | F    |   35 |
|  7 | G    |   29 |
|  8 | H    |   31 |
|  9 | I    |   27 |
| 10 | J    |   24 |
+----+------+------+
10 rows in set (0.00 sec)

mysql> 

6.2 模拟删除表数据

# 模拟误操作将tmp_user表数据删除
mysql> delete from tmp_user;
Query OK, 10 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> 

6.3 基于binlog实现数据恢复

6.3.1 查询误操作时间节点

# 这里我们是通过general_log进行查找误操作时间点，使用general_log的前提是开启了general_log
1. 查询是否开启general_log，如果是On则开启
mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> 

2. 查看slow_query_log文件路径
mysql> SHOW VARIABLES LIKE 'general_log_file';
+------------------+-----------------------------+
| Variable_name    | Value                       |
+------------------+-----------------------------+
| general_log_file | /data/mysql/log/general.log |
+------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

3. 分析general_log的文件/data/mysql/log/general.log
[root@node3 log]# grep -i "insert into" /data/mysql/log/general.log
2025-03-15T23:27:00.712243+08:00            2 Query     INSERT INTO tmp_user (name, age) VALUES
[root@node3 log]# 

 6.3.2 导出binlog

mysqlbinlog --start-datetime="2025-03-15 23:27:00" --stop-datetime="2025-03-15 23:27:05" /data/mysql/log/mysql-bin.000005 > /data/mysql/backup/binlog_recovery.sql

6.3.3 恢复数据

mysql -u root -p -D mydb < /data/mysql/backup/binlog_recovery.sql
 
[root@node1 log]# mysql -u root -p -D mydb < /data/mysql/backup/binlog_recovery.sql
Enter password: 
[root@node1 log]# 

6.3.4 验证数据是否恢复

mysql> select * from tmp_user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | A    |   25 |
|  2 | B    |   30 |
|  3 | C    |   22 |
|  4 | D    |   28 |
|  5 | E    |   26 |
|  6 | F    |   35 |
|  7 | G    |   29 |
|  8 | H    |   31 |
|  9 | I    |   27 |
| 10 | J    |   24 |
+----+------+------+
10 rows in set (0.00 sec)

mysql>