MySQL—使用binlog日志恢复数据

一、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 数据库的一致性备份。

--single-transactionCreates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.--single-transaction选项在执行mysqldump命令时，会将隔离级别设置为
REPEATABLE READ，并开启一个事务。这样，在备份过程中读取的数据是一个逻辑一致的快照，即使在备份过程中有其他会话对数据进行修改，
也不会影响到备份的数据。这种方式避免了在备份大型数据库时出现长时间的锁定或阻塞现象，对生产环境的业务操作影响较小‌。--master-data=2
该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，
位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。
如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。

步骤三：在向数据库中插入数据模拟备份到误删除中间的时间段还有其他数据入库

步骤四：假设不小心删除了数据

步骤五：使用mysqlbinlog命令查看binlog日志明文确定删除前的POS的点好截取相关的日志文件

步骤六：查看误删时间段的日志信息

/opt/sumscope/mysql/bin/mysqlbinlog binlog.000002  --start-position=备份数据的POS --stop-position=删除数据的POS -vv > redo.biglog

步骤七：数据恢复

 --先导入备份的数据source /opt/sumscope/mysql/logs/stc.sql--再导入binlog中的日志source /opt/sumscope/mysql/logs/redo.biglog