侧边栏壁纸
博主头像
★街角晚灯★博主等级

博观而约取 厚积而薄发

  • 累计撰写 450 篇文章
  • 累计创建 183 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

MySQL BinLog 数据回滚|数据恢复

WinJay
2023-11-03 / 0 评论 / 0 点赞 / 35 阅读 / 5468 字 / 正在检测是否收录...
温馨提示:
文章发布较早,内容可能过时,阅读注意甄别。。。。

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;

image-20231024145824076

3. 回滚到某一时间节点

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

3.1 查看当前时间节点

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

SHOW MASTER STATUS;

image-20231024150006391

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

其中,--start-datetime--stop-datetime参数指定了回滚的时间范围,mysql-bin.XXXXXX是要回滚的binlog文件名称。

解析完成后,会生成一个名为rollback.sql的文件,其中包含了回滚操作的SQL语句。

image-20231024151044882

3.3 执行回滚操作

在生成了回滚的SQL语句后,我们可以使用MySQL的客户端工具(如mysql命令行工具或可视化工具)连接到数据库,并执行生成的SQL语句,将数据库回滚到目标时间节点的状态。

image-20231024153100543

回滚完验证数据即可!

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin

评论区