技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

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

 收录于 Mysql
   约 2148 字   预计阅读 5 分钟 
https://bing.ee123.net/img/rand?artid=146287717

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的三种格式

  • STATEMENT：记录SQL语句
  • ROW：记录每一行数据的变化
  • MIXED：混合模式，默认记录SQL语句，但在某些情况下记录行数据

查看当前的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>

更新于 2025-03-16
 数据库, 备份恢复, Mysql, Binlog
返回 | 主页