MySQL BinLog 数据回滚｜数据恢复

在使用MySQL数据库的过程中，有时我们需要回滚到某一特定时间节点的数据状态。这是非常有用的，特别是在出现数据错误或者误操作时。MySQL提供了丰富的功能和语法，可以帮助我们实现这一目的。本文将介绍如何使用MySQL实现回滚到某一时间节点的操作，并提供相应的代码示例。

1. 准备工作

在开始之前，我们需要先明确几个概念和准备一些必要的工作。

1.1 MySQL的binlog

MySQL的binlog是一种二进制日志文件，用于记录数据库的修改操作。它包含了所有的插入、更新和删除操作的详细信息。我们可以使用它来回滚数据库到某一时间点的状态。

1.2 配置MySQL的binlog

为了能够使用binlog进行回滚操作，我们需要确保MySQL已经启用了binlog功能，并且配置正确。

-- 编辑MySQL配置文件（如：my.cnf）
[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
expire_logs_days=10
max_binlog_size=100M
binlog_format=row

上述配置中，log-bin指定了binlog文件的路径和名称，expire_logs_days设置了binlog文件的保留天数，max_binlog_size指定了每个binlog文件的最大大小，binlog_format设置了binlog的格式为row；重启MySQL使配置生效！

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]			# # 定义客户端的默认参数
default-character-set=utf8mb4   # # 设置客户端的默认字符集为utf8mb4

[mysql]				# # 定义MySQL命令行工具的默认参数
default-character-set=utf8mb4	# # 设置MySQL命令行工具的默认字符集为utf8mb4

[mysqld]			# # 定义MySQL服务器的默认参数
pid-file        = /var/run/mysqld/mysqld.pid		# 指定MySQL服务器的进程ID文件位置
socket          = /var/run/mysqld/mysqld.sock		# 指定MySQL服务器的Unix域套接字文件位置
datadir         = /var/lib/mysql			# 指定MySQL服务器的数据目录位置
secure-file-priv= NULL					# 指定MySQL服务器的安全文件目录位置（这里设置为NULL表示没有限制）

wait_timeout=246400			# 指定服务器在关闭闲置连接之前等待的时间（以秒为单位）
innodb_log_file_size=2G			# 设置InnoDB存储引擎的日志文件大小为2GB
lower_case_table_names=1		# 设置表名大小写敏感性，1表示不区分大小写
#character_set_results=utf8		 # 设置查询结果字符集
character_set_server=utf8mb4		# 设置服务器的默认字符集为utf8mb4
default-storage-engine=INNODB		# 设置默认的存储引擎为InnoDB
innodb_default_row_format=DYNAMIC	# 设置默认的InnoDB行格式为动态。
max_connections=5000			# 设置mysql的连接数，默认是151个连接数

# 配置定义慢查询日志的开关、慢查询的时间、日志文件的存放路径;默认是禁用，当需要debug时可以启用后查看日志文件。
slow_query_log=0			# 启用(1)/禁用(0)慢查询日志功能
long_query_time=2			# 配置慢查询的阈值时间,以秒为单位;任何执行时间超过该阈值的查询语句将被记录到慢查询日志中;
					# 设置为2秒，意味着任何执行时间超过2秒的查询将被记录到慢查询日志中。
slow_query_log_file =/var/lib/mysql/SlowSQL.log 	# 配置慢查询日志文件的路径和名称;此配置中，慢查询日志将被记录到/var/lib/mysql/SlowSQL.log文件中
# slow_query_log_file =/var/lib/mysql/localhost-slow.log

## binlog configuration	BinLog相关选项配置
#expire_logs_days = 30			# 配置binlog失效时间
#max_binlog_size = 500M			# 配置binlog文件大小
#binlog_expire_logs_seconds=259200	# 设定binlog保留时间

# 设置SQL模式，包括严格的事务表、禁止零日期、错误除零、禁用引擎替换等
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# Custom config should go here
!includedir /etc/mysql/conf.d/

2. 检查binlog文件

在进行回滚操作之前，我们首先需要查看可用的binlog文件，并确定回滚的时间节点。

使用以下命令查看MySQL的binlog文件列表；我们可以看到所有可用的binlog文件及其大小。

SHOW BINARY LOGS;

3. 回滚到某一时间节点

有了binlog文件列表后，我们可以选择一个合适的时间节点进行回滚。下面是一个回滚到某一时间节点的示例。

3.1 查看当前时间节点

在回滚之前，我们需要了解当前的binlog名称和位置，以便在回滚操作后更新到正确的时间节点。可以使用以下命令查看当前的binlog名称和位置；其中，File列显示了当前的binlog文件名称，Position列显示了当前的binlog位置。结果类似如下：

SHOW MASTER STATUS;

3.2 回滚到特定时间节点

要回滚到某一时间节点，我们需要使用MySQL的mysqlbinlog命令解析binlog文件，并生成相应的SQL语句。

首先，我们需要将目标binlog文件复制到本地（如果文件在远程服务器上）并且本地必须有mysqlbinlog命令，如果没有的话，那建议还是在服务器上操作。，然后使用以下命令解析binlog文件并生成SQL语句：

mysqlbinlog --start-datetime='YYYY-MM-DD HH:MM:SS' --stop-datetime='YYYY-MM-DD HH:MM:SS' mysql-bin.XXXXXX > rollback.sql

# 例如：
mysqlbinlog --start-datetime='2023-10-24 11:00:00' --stop-datetime='2023-10-24 13:40:00' /var/lib/mysql/binlog.000053 > rollback.sql

# 或者整个binlog文件恢复
mysqlbinlog --set-charset=utf-8 /var/lib/mysql/binlog.000051>51.sql