MySQL—使用binlog日记恢复数据

梦应逍遥 · 2025-3-14 03:14:21

一、binlog日记恢复数据简介

在 MySQL 中，使用二进制日记（binlog）恢复数据是一种常见的用于故障恢复或数据找回的方法。以下是具体的使用步骤：

确认 binlog 已启用：首先必要确认 MySQL 服务器已经启用了二进制日记功能。可以通过查看 MySQL 的配置文件（通常是 my.cnf 或 my.ini），查抄是否存在 log-bin 配置项。如果配置文件中存在雷同 log-bin=mysql-bin 的配置，就表示已经启用了二进制日记。也可以在 MySQL 命令行中执行 SHOW VARIABLES LIKE 'log_bin'; 命令，若 Value 为 ON，则说明已启用。
找到必要的 binlog 文件：二进制日记文件默认会以 mysql-bin.xxxxxx 的情势命名，xxxxxx 是一个数字编号。可以通过 SHOW BINARY LOGS; 命令查看全部的二进制日记文件列表，确定必要用于恢复数据的日记文件范围。如果知道数据丢失或误操纵的大抵时间点，可以使用 SHOW BINLOG EVENTS IN '日记文件名'; 命令查看指定日记文件中的事件，找到对应的操纵记载。
预备恢复情况：为了恢复数据，最好在一个与原生产情况相同或相似的测试情况中举行操纵。可以使用备份的数据文件先恢复到一个时间点，然后再通过 binlog 来补充后续的操纵。
使用 mysqlbinlog 工具分析 binlog：mysqlbinlog 是 MySQL 提供的用于分析二进制日记的工具。可以使用以下命令来分析指定的二进制日记文件：

mysqlbinlog [选项] 二进制日志文件名

复制代码

例如，mysqlbinlog --no-defaults mysql-bin.000001 可以分析 mysql-bin.000001 这个日记文件。常用的选项包罗 --start-datetime 和 --stop-datetime 来指定时间范围，--start-position 和 --stop-position 来指定日记位置范围。例如，只恢复某个时间段内的操纵，可以使用 mysqlbinlog --start-datetime='2024-01-01 00:00:00' --stop-datetime='2024-01-02 00:00:00' mysql-bin.000001 。
5. 将分析后的内容应用到数据库：将 mysqlbinlog 分析后的 SQL 语句应用到目标数据库中，可以将分析结果通过管道直接输入到 mysql 客户端来执行。例如：

mysqlbinlog [选项] 二进制日志文件名
| mysql -u用户名 -p密码

复制代码

假设用户名是 root，密码是 123456，要恢复 mysql-bin.000001 这个日记文件中的数据，可以执行 mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123456 。
在使用 binlog 恢复数据时，要特别小心，由于错误的操纵大概会导致数据进一步丢失或损坏。在正式恢复生产情况数据之前，务必在测试情况中举行充分的测试。

二、使用binlog日记恢复数据的步骤

1、前提

在数据库的配置文件中一定要开启binlog日记，否则不会有binlog日记产生。

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1

复制代码

2、可选择的binlog日记配置项

添加配置项：在[mysqld]部门添加或修改以下配置内容。
- server-id=1：每个 MySQL 服务器必须有一个唯一的 ID，一般设置为正整数。
- log_bin=mysql-bin：指定开启 binlog 日记，并设置日记文件的基础名，默认存储在 MySQL 的数据目录下，也可指定绝对路径，如log_bin=/data/mysql/mysql-bin。
- binlog_format=ROW：设置 binlog 的格式，可选项有ROW（记载每一行数据的修改细节）、STATEMENT（记载 SQL 语句本身）、MIXED（混合模式），推荐使用ROW格式。
- expire_logs_days=7：设置 binlog 日记主动过期的天数，到期后会主动删除。

[mysqld]
binlog_format = ROW

复制代码

STATEMENT格式记载了语句的原文，RO格式记载了每行数据的变化，MIXED格式在某些情况下会记载为STATEMENT，在其他情况下会记载为ROW。
确保配置后重启MySQL服务以使更改收效。
留意：在生产情况中更改这些配置必要谨慎，由于它大概会影响数据库的性能和复制

3、使用命令行在体系中举行操纵

登录 MySQL：使用命令mysql -u root -p，输入密码登录到 MySQL 数据库3。
执行命令启用 binlog3
- SET GLOBAL binlog_format=ROW;：设置 binlog 格式为ROW，也可根据需求设置为STATEMENT或MIXED。
- SET GLOBAL binlog-do-db=<要记载更改的数据库>;：指定要记载更改的数据库，如果要记载多个数据库，数据库之间用逗号分隔。
- SET GLOBAL binlog-ignore-db=<要忽略的数据库>;：指定要忽略的数据库，多个数据库之间用逗号分隔。
生存设置：执行COMMIT;生存设置3。

配置完成后，可以使用show variables like 'log_bin%';命令查看 binlog 是否已启用。如果Value为ON，则表示 binlog 已经乐成开启。

4、确认binlog日记是否开启

确认binlog已启用：
SHOW VARIABLES LIKE 'log_bin';
查看当前的日志文件：
SHOW BINARY LOGS;
查看binlog的格式（可选）：
SHOW VARIABLES LIKE 'binlog_format';

复制代码

5、使用mysqlbinlog工具查看binlog二进制日记文件

三、数据备份和恢复步骤

步骤一：在sql中插入数据

步骤二：备份数据(正确定位到必要恢复数据的时间点)

模仿生产每天数据备份的的数据

mysqldump -ustc -pppp --master-data=2 --single-transaction -S /opt/sumscope/mysql/mysql.sock test stc > stc.sql

复制代码

备份命令要带上 --master-data=2 --single-transaction
在 MySQL 中，--master-data=2 和 --single-transaction 是 mysqldump 命令常用的参数，它们各自有不同的作用，以下为你具体介绍：

--master-data=2 参数详解：

作用：该参数用于在执行 mysqldump 备份时，记载主服务器的二进制日记文件名（File）和位置（Position）信息到备份文件中。这对于后续搭建主从复制情况非常重要，由于从服务器必要知道从主服务器的哪个二进制日记位置开始复制数据。当 --master-data 设置为 2 时，会在备份文件中添加一个 CHANGE MASTER TO 语句，其中包含了主服务器的二进制日记文件名和位置信息。
示例：假设执行 mysqldump --master-data=2 -u root -p mydatabase > backup.sql 命令来备份名为 mydatabase 的数据库。备份完成后，在 backup.sql 文件中会看到雷同以下的内容（部门示例）：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=459;
--
-- Current Database: `mydatabase`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mydatabase`;
--
-- Table structure for table `users`
--
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `users`
--
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `name`) VALUES (1,'John');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

复制代码

与 --master-data=1 的区别：--master-data=1 也会记载主服务器的二进制日记信息，但它会在执行 mysqldump 时，对主服务器加全局读锁（FLUSH TABLES WITH READ LOCK），直到备份完成，这期间主服务器无法举行写入操纵，会影响数据库的可用性。而 --master-data=2 不会加全局读锁，它是通过在事务中获取二进制日记位置信息来实现的，对数据库的影响较小。

--single-transaction 参数详解：

作用：该参数重要用于在 InnoDB 存储引擎的数据库上举行一致性备份。它会在备份开始时开启一个事务，然后在这个事务中执行 SELECT 语句来获取数据，由于 InnoDB 的 MVCC（多版本并发控制）机制，在事务执行期间，其他事务对数据的修改不会影响到本次备份的数据读取，从而保证了备份数据的一致性。在备份过程中，不会对表加锁（除了在获取二进制日记位置时大概会有短暂的锁），所以可以在数据库正常运行时举行备份，不影响业务的写入操纵。
实用场景：实用于必要在不影响数据库正常运行的情况下举行在线备份的场景，特别是对于写入频繁的 InnoDB 数据库。例如，在一个电商网站的数据库中，使用 --single-transaction 参数可以在不停止订单处置处罚等写入操纵的同时，获取到一个一致的数据库备份。
留意事项：--single-transaction 只对 InnoDB 存储引擎有效，对于其他存储引擎（如 MyISAM）不起作用。由于 MyISAM 表不支持事务，所以在备份 MyISAM 表时，大概会出现数据不一致的情况。

--master-data=2 重要用于记载主服务器的二进制日记信息以便后续搭建主从复制，--single-transaction 则用于在不影响数据库正常写入的情况下实现 InnoDB 数据库的一致性备份。