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

embedded/2025/3/18 13:08:14/
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: 0mysql> 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>


http://www.ppmy.cn/embedded/173595.html

相关文章

【多线程】单例模式

【多线程】单例模式

文章目录 1. 单例模式1.1 什么是单例模式1.2 为什么使用单例模式1.3 实现单例模式1.3.1 饿汉模式1.3.1 懒汉模式 1. 单例模式 1.1 什么是单例模式 单例模式是一种创建型设计模式&#xff0c;它确保一个类只有一个实例&#xff0c;并提供一个全局访问点来访问该实例。 单例 单…
阅读更多...
vue2：el-table列中文字前面加icon图标的两种方式

vue2：el-table列中文字前面加icon图标的两种方式

1、文字前面加icon <el-table-column label="姓名" align="left" prop="nickName"><template #default="{ row }"><i v-if="row.sync" class="el-icon-lock"></i><span>{{ row.nic…
阅读更多...
网络安全与七层架构

网络安全与七层架构

网络安全与七层架构 随着互联网技术的迅猛发展&#xff0c;网络安全问题日益凸显。网络安全不仅影响到个人用户的信息安全&#xff0c;更是企业及国家安全的重要组成部分。而七层架构&#xff08;OSI模型&#xff09;为网络通信提供了理论支撑&#xff0c;能够有效地帮助我们理…
阅读更多...
ImGui 学习笔记（五） —— 字体文件加载问题

ImGui 学习笔记（五） —— 字体文件加载问题

ImGui 加载字体文件的函数似乎存在编码问题&#xff0c;这一点可能跟源文件的编码也有关系&#xff0c;我目前源文件编码是 UTF-16。 当参数中包含中文字符时&#xff0c;ImGui 内部将字符转换为宽字符字符集时候&#xff0c;采用的 MultiByteToWideChar API 参数不太对&#…
阅读更多...
处理变长的时间序列

处理变长的时间序列

pytorch中torch.nn.utils.rnn相关sequence的pad和pack操作 官网…torch.nn.utils.rnn.pack_padded_sequence 知乎pack_padded_sequence 和 pad_packed_sequence 结论 ✅ pack_padded_sequence 是最好的方法&#xff08;避免无效计算&#xff0c;提升性能&#xff09; &#x…
阅读更多...
java 动态赋值写入word模板

java 动态赋值写入word模板

最近工作中&#xff0c;客户给提供了word模板&#xff0c;要求动态赋值到word模板中&#xff0c;查阅相关资料&#xff0c;最终完成了需求&#xff0c;希望也可以帮助到大家&#xff01; 例如表格如下&#xff1a; 第一步&#xff1a;在word模板中&#xff0c;把需要动态赋值的…
阅读更多...
ThreadLocal

ThreadLocal

多线程和 ThreadLocal 是 Java 并发编程中的两个重要概念&#xff0c;它们在处理线程安全和资源隔离时扮演关键角色。 1. 多线程基础 1.1 什么是多线程&#xff1f; 线程&#xff1a;是操作系统能够调度的最小执行单元&#xff0c;一个进程可以包含多个线程。 多线程&#xf…
阅读更多...
高亮动态物体——前景提取与动态物体检测器（opencv实现）

高亮动态物体——前景提取与动态物体检测器（opencv实现）

目录 代码说明 1. 导入库 2. 创建背景建模对象 3. 打开视频源 4. 逐帧处理视频 5. 应用背景建模获得前景掩码 6. 形态学操作去除噪声 6.1 定义形态学核 6.2 开运算去除噪点 6.3 膨胀操作填补前景区域空洞 7. 轮廓检测识别动态物体 8. 绘制轮廓和边界框 9. 显示处理…
阅读更多...
最新文章

Copyright @ 2022~2023