记一次通过binlog日志恢复MySQL数据库的经历

news/2024/11/24 5:23:58/

通过binlog日志恢复MySQL数据库的数据

一、起因

起因是我在自己服务器上搭建的博客被黑客攻击，黑客删除了我的数据库并且要求支付比特币才给我恢复。

博客所有的表和数据都被清空，只留下了勒索金额和地址。如图

image-20230517111100245

穷鬼如我当然是：

iamge-20230517001

二、恢复数据

2-1 查看binlog日志

先连接MySQL看看binlog日志是否开启。

# 查看MySQL是否开始binlog日志，结果为ON即已开启。
mysql> show variables like 'log_bin';

幸好，我的MySQL默认开启了binlog日志，也就是接下来用于恢复数据的核心。

# 查看binlog日志列表
mysql> show master logs;
# 查看指定binlog日志
mysql> show binlog events in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000001 |    4 | Format_desc    |         1 |         126 | Server ver: 8.0.33, Binlog ver: 4    |
| binlog.000001 |  126 | Previous_gtids |         1 |         157 |                                      |
| binlog.000001 |  157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 |  236 | Query          |         1 |         321 | BEGIN                                |
| binlog.000001 |  321 | Table_map      |         1 |         519 | table_id: 294 (mysql.user)           |
| binlog.000001 |  519 | Update_rows    |         1 |         911 | table_id: 294 flags: STMT_END_F      |
| binlog.000001 |  911 | Xid            |         1 |         942 | COMMIT /* xid=5284 */                |
| binlog.000001 |  942 | Anonymous_Gtid |         1 |        1019 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1019 | Query          |         1 |        1109 | FLUSH PRIVILEGES                     |
| binlog.000001 | 1109 | Stop           |         1 |        1132 |                                      |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
10 rows in set (0.00 sec)

显然这样查看的结果并不便于我们阅读，也没办法清晰地判断该回滚哪一部分。

那么现在有两个方案：

  • 方案一：直接开始恢复。适用于明确知道恢复时间节点或者位置节点的情况，就无需麻烦地解析binlog日志了。详见2-2

  • 方案二：解析binlog日志。适用于不确定该从什么节点开始恢复的情况，需要将binlog日志解析成便于人类阅读的sql文件，根据sql的内容判断该何时执行。详见2-3

2-2 使用mysqlbinlog命令按照时间或位置节点恢复

分两种情况：

1.MySQL服务运行在Linux服务器中

# 进入Linux服务器
mysqlbinlog --no-defaults  日志的绝对路径\binlog.000001 --start-datetime="2023-2-25 20:17:00" --stop-datetime="2023-05-11 23:59:00"  | mysql -uroot -p123456 blog
# 执行binlog命令
## 按时间节点恢复
mysqlbinlog --no-defaults  /home/zhangheng/software/docker-mysql/3306/data/binlog.000001 --start-datetime="2023-2-25 20:17:00" --stop-datetime="2023-05-17 09:00:00"  | mysql -uroot -p123456 blog
## 按位置节点恢复
mysqlbinlog --no-defaults  /home/zhangheng/software/docker-mysql/3306/data/binlog.000001 --start-position=321 --stop-position=1019  | mysql -uroot -p123456 blog

2.MySQL服务运行在docker部署的容器中

# 1.进入Linux服务器
# 2.进入docker容器
docker exec -it mysql-3306 /bin/bash
# 3.执行binlog命令
mysqlbinlog --no-defaults  /home/zhangheng/software/docker-mysql/3306/data/binlog.000001 --start-datetime="2023-2-25 20:17:00" --stop-datetime="2023-05-17 09:00:00"  | mysql -uroot -p123456 blog
# 注意：如果提示bash: mysqlbinlog: command not found，说明容器中找不到mysqlbinlog服务。那就只有通过另一种方案，即将binlog解析成可阅读的sql后执行了来恢复数据了。详见2-3

2-3 将binlog解析成可阅读的sql后通过sql文件恢复

方案一：离线解析。

适合无需原生sql，使用mysqlbinlog命令按节点恢复的情况。

这种方案是将binlog文件，解析成可阅读的sql后通过阅读sql获取位置节点或者时间节点，然后按照2-2的方法恢复数据。好处是除了MySQL无需安装其它工具。

（1）首先需要确保Linux上安装了MySQL服务

安装步骤详见附：一、Linux安装MySQL服务，已安装请跳过。（这里主要针对此前MySQL是docker容器部署的情况下，服务器需要安装一个MySQL）

（2）使用mysqlbinlog命令将binlog文件解析成可阅读的sql

mysqlbinlog --base64-output=decode-rows -vv --database=blog --stop-datetime="2023-05-11 23:59:00" /home/zhangheng/binlog/binlog.000008 > /home/zhangheng/binlog/sql08.sql

命令参数说明：

  • –base64-output=decode-rows -vv：binlog的模式是ROW模式，默认情况下只能看到一些经过base-64编码的信息，加上这条配置才能解析成可阅读的sql。注意：如果这里只加一个v，解析后的sql文件中只会有被注释的伪SQL，文件并不能执行。加两个v，解析后的SQL文件才会既有编码后的sql（可执行，又有解码后的sql语句（可供阅读）
  • –database=blog：用于指定要恢复数据的数据库
  • –stop-datetime=“2023-05-11 23:59:00”：用于指定要恢复数据的截止时间
  • /home/zhangheng/binlog/binlog.000008：binlog日志所在绝对路径
  • /home/zhangheng/binlog/sql08.sql：输出解析后的的sql文件绝对路径

这一步我遇到了两个坑，详见坑：坑1、坑2。

解析完成后获得sql文件。

（3）通过sql文件恢复数据

  • 情况1：从可阅读的sql文件中找到需要恢复的节点，按2-2中的按节点恢复执行mysqlbinlog命令恢复。

  • 情况2：通过mysql的source方法导入sql文件恢复。登录mysql，执行命令：

# MySQL导入执行sql文件
source xxx.sql

注：我通过情况2导入了sql文件，但发现数据有部分缺失，所以跳转到方案二继续。

方案二：通过工具在线解析。

适合需要生成原生sql，手动执行sql恢复数据的情况。

这种方案需要安装一些工具，麻烦一些，但好处是解析后的sql就是可直接执行的原生SQL，可以灵活执行。

同类型工具有很多， binlog2sql、my2sql等等，这里我选择的是my2sql工具。

（1）Linux安装go语言环境

# 因为my2sql由go语言编写，所以要编译它需要先安装go
# 下载go语言安装包
wget https://golang.google.cn/dl/go1.19.linux-amd64.tar.gz
# 解压安装包
cd /usr/local/src
tar -xzf go1.19.linux-amd64.tar.gz
# 配置环境
vim /etc/profile
# 在最底下添加一行
# GO PATH
export PATH=$PATH:/usr/local/src/go/bin
# 使profile配置立即生效
source /etc/profile
# 查看Go版本
go version
# 若结果显示“go1.19 linux/amd64”，则说明go安装成功

（2）Linux下载并编译my2sql

# 下载my2sql
git clone https://github.com/liuhr/my2sql.git
cd my2sql/
# 编译my2sql
go build .

（3）通过my2sql工具将binlog解析为sql

  • 情况1：MySQL部署在Linux中
# 给编译后的my2sql授权
chmod u+x my2sql# my2sql解析binlog文件
./my2sql  -user root -password 123456 -host 127.0.0.1 -port 3306 -databases blog -work-type 2sql -start-file binlog.000008 -output-dir /home/zhangheng/tmp
  • 情况2：MySQL部署在Docker中
#把编译后的my2sql 二进制文件复制到docker的MySQL容器的根目录（mysql-3306是我的容器名，此处也可以用容器id）
docker cp /usr/local/bin/my2sql  mysql-3306:/#进入MySQL容器
docker exec -it mysql-3306  bash
# 给编译后的my2sql授权
chmod u+x my2sql# my2sql解析binlog文件
./my2sql  -user root -password 123456 -host 127.0.0.1 -port 3306 -databases blog -work-type 2sql -start-file binlog.000008 -output-dir /home/zhangheng/tmp

命令参数说明：

  • -databases blog：用于指定要恢复数据的数据库blog
  • -work-type 2sql：2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息
  • -start-file binlog.000008：起始binlog日志
  • output-dir /home/zhangheng/tmp：输出解析后的的sql文件绝对路径

更多可选择参数详见 link:[https://github.com/liuhr/my2sql]

运行成功后如下图：

image-20230517153605013

（4）恢复数据

解析后指定目录内容如下：

image-20230517153630477

biglong_trx.txt # 事务的统计信息
binlog_status.txt # 事务的统计信息
forward.1.sql # binlog解析之后的sql
...
forward.n.sql # binlog解析之后的sql

然后可以自由选择执行sql

三、提高安全性

被黑客删库后痛定思痛，觉得有必要提高一下我数据库的安全性，毕竟虽然只是用来写写个人博客，但逛我家数据库跟他家逛菜园子似的，也太没面子了。

于是有以下几个方案：

3-1 修改密码

整个复杂的，但记得找个小本本记下来。

ALTER USER 'root'@'localhost'  IDENTIFIED BY 'dnqi3ehQ2783YE$3@&@￥';

3-2 禁止root用户外网访问

host='%'是任意IP访问，host = 'localhost’是仅限本地访问。

update user set host = 'localhost' where user = 'root';

3-3 使用ssl加密连接

# 创建一个新的账户，用于远程访问
create user 'ssl_zh'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 查看用户拥有权限
SHOW GRANTS FOR ssl_zh;
# 给用户分配权限为查询，范围为blog数据库下的所有表
GRANT SELECT ON blog.* TO 'ssl_zh'@'%';
# 收回用户所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'ssl_zh'@'%';
# 指定用户只能通过ssl连接
alter user 'ssl_zh'@'%' require ssl;
#刷新权限
flush privileges;

直接通过用户ssl_zh远程登录数据库，发现提示以下内容

Access denied for user 'ssl_zh'@'xx.xx.xx.xx' (using password: YES)

3-3-1 Windows上的数据库客户端通过ssl登录

从MySQL服务器下载这三个客户端证书到本地，连接时指定证书路径

ca.pem, client-key.pen, client-cert.pem

image-20230517172242093

再次连接，连接成功。

image-20230517172440380

3-3-2 Linux上运行的springboot项目通过jdbc连接数据库

情况1：MySQL数据库和springboot项目直接部署在服务器上

情况2：MySQL数据库和springboot项目部署在docker容器里

3-4 新增数据库备份功能

1.任意目录新建脚本

vim backup_mysql.sh

# 设置 MySQL 登录信息
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_DATABASE="blog"
BACKUP_DIR="/home/zhangheng/job/mysqlbackup"
DATE=$(date +%Y-%m-%d)
TIME=$(date +%H-%M-%S)# 创建备份目录和文件名
mkdir -p $BACKUP_DIR/$DATE
FILENAME=$BACKUP_DIR/$DATE/$MYSQL_DATABASE-$TIME.sql.gz# 使用 mysqldump 命令备份数据库
mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD --databases $MYSQL_DATABASE | gzip > $FILENAME# 输出备份完成信息
echo "Backup completed on $(date +%Y-%m-%d %H:%M:%S) for database $MYSQL_DATABASE to $FILENAME."# 删除超过14天的备份文件
find $BACKUP_DIR/* -mtime +14 -exec rm {} \;

2、为脚本文件添加可执行权限：

chmod +x backup_mysql.sh

3、将脚本文件添加到 crontab 中，以每天凌晨 2 点执行备份任务：

crontab -e

4、在打开的编辑器中，新增以下行：

0 2 * * * /home/zhangheng/job/backup_mysql.sh >/dev/null 2>&1

保存并退出编辑器。现在，脚本将在每天凌晨 2 点执行，并输出一条备份计划信息。

上面的shell脚本做了以下事情：

  1. 获取当前日期和时间。
  2. 使用 mkdir 命令创建备份目录和文件名。
  3. 使用 mysqldump 命令备份数据库，并将结果压缩成 gzip 格式。
  4. 输出一条备份完成信息。
  5. 使用 find 命令查找超过14天的备份文件，并将其删除。

附

一、Linux安装MySQL服务

# 在 /soft 目录下创建一个空的文件夹 mysql
mkdir /software/mysql
# 进入这个新建的文件夹下
cd /software/mysql

上传下载好的 Linux 下 MySQL 的安装包（安装包下载链接：https://downloads.mysql.com/archives/community/）

image-20230517115450270

# 在当前目录下（mysql）下创建一个 mysql-8.0.23 文件夹
mkdir mysql-8.0.23
# 解压安装包到该目录下
tar -xvf mysql-8.0.23-1.el8.x86_64.rpm-bundle.tar -C mysql-8.0.23
# 解压完成之后切换到 mysql-8.0.26 目录
cd mysql-8.0.23

可以看到解压后的文件都是 rpm 文件，所以需要用到 rpm 包资源管理器相关的指令安装这些 rpm 的安装包

在安装执行 rpm 安装包之前先下载 openssl-devel 插件，因为 mysql 里面有些 rpm 的安装依赖于该插件。

yum install openssl-devel

安装完该插件之后，依次执行以下命令安装这些 rpm 包

rpm -ivh mysql-community-common-8.0.23-1.el8.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.23-1.el8.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.23-1.el8.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.23-1.el8.x86_64.rpm
rpm -ivh mysql-community-client-8.0.23-1.el8.x86_64.rpm
rpm -ivh mysql-community-server-8.0.23-1.el8.x86_64.rpm

在 Linux 中 MySQL 安装好了之后系统会自动的注册一个服务，服务名称叫做 mysqld，所以可以通过以下命令操作 MySQL

  • 启动 MySQL 服务：systemctl start mysqld
  • 重启 MySQL 服务：systemctl restart mysqld
  • 关闭 MySQL 服务：systemctl stop mysqld

rpm 安装 MySQL 会自动生成一个随机密码，可在 /var/log/mysqld.log 这个文件中查找该密码

cat /var/log/mysqld.log

image-20230517120302046

A temporay password is generated for root@localhost: ****密码**** ，账号是 root，有了账号和密码之后就可以连接 MySQL 了。

# 连接 MySQL 
mysql -u root -p
# 修改密码(默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。)
ALTER  USER  'root'@'localhost'  IDENTIFIED BY 'Qwert123?';
# 创建一个新的账户，用于远程访问(mysql 8.0)
create user 'zh'@'%' IDENTIFIED WITH mysql_native_password BY 'Qwert123?';
# 给用户分配权限
grant all on *.* to 'zh'@'%';

卸载

卸载 MySQL 前需要先停止 MySQL

命令：systemctl stop mysqld

停止 MySQL 之后查询 MySQL 的安装文件：rpm -qa | grep -i mysql

卸载上述查询出来的所有的 MySQL 安装包

rpm -e mysql-community-common-8.0.23-1.el8.x86_64.rpm --nodeps
rpm -e mysql-community-client-plugins-8.0.23-1.el8.x86_64.rpm --nodeps
rpm -e mysql-community-libs-8.0.23-1.el8.x86_64.rpm --nodeps
rpm -e mysql-community-devel-8.0.23-1.el8.x86_64.rpm --nodeps
rpm -e mysql-community-client-8.0.23-1.el8.x86_64.rpm --nodeps
rpm -e mysql-community-server-8.0.23-1.el8.x86_64.rpm --nodeps

删除MySQL的数据存放目录

rm -rf /var/lib/mysql/

删除MySQL的配置文件备份

rm -rf /etc/my.cnf.rpmsave

坑

坑1 mysqlbinlog解析后的sql中文变成乱码

问题

执行mysqlbinlog命令后，解析的sql数据部分中文变成乱码。如图所示

image-20230517143134196

原因和解决方案

原因一句话：我在Windows系统执行的mysqlbinlog命令。

为什么要在Windows系统执行呢，因为我Linux上没装MySQL，运行的MySQL是通过docker容器部署在Linux上的。

为了省事（真相是docker容器部署的MySQL里没有mysqlbinlog命令才是最坑爹的啊！明明都给我默认开启了binlog，也记录了binlog日志，但进容器之后发现没有mysqlbinlog命令…我拿着一手的binlog日志没办法下嘴QAQ）

我就把Linux上运行的MySQL服务的binlog日志拷贝到Windows电脑上，试图用Windows电脑上装的MySQL中的mysqlbinlog去将它解析成sql文件，再拿到Linux上近MySQL容器里去执行。

我是说在网上查了半天也没有看到同类问题，可能没有跟我一样傻的大兄弟吧。。

总而言之，要想避免这个问题，去Linux上执行mysqlbinlog命令。

要问MySQL是通过docker容器部署在Linux上的，Linux上没装MySQL怎么办，只能给装一个呗。

坑2 mysqlbinlog解析后的sql语句被注释了

问题

执行mysqlbinlog命令后，解析的sql语句都被注释掉了。如图所示

还是上面那张图，可以看到sql语句没有字段名，而是用每个字段的位置指代了字段，当然这不重要，重要的是sql语句前面都有三个#号，也就是被注释了。进入MySQL后通过source导入sql文件就会发现啥也导不进去。

image-20230517143134196

原因和解决方案

原因一句话：这是伪sql，并不能执行。

真正能执行的是哪一部分呢，是解析之前经过base-64编码的sql，如图所示：

image-20230517145202908

也就是下面这条配置将这些人类无法阅读的编码信息解析成这些带#号的伪sql，相当于对经过base-64编码的sql的注释，恢复数据时网上查到的一些资料告诉我应该这样写：

--base64-output=decode-rows -v

这样将编码后的sql解码成可阅读的sql，但会导致文件执行后没有影响任何数据，因为解码后的sql都被注释了，而解码前的sql并没有保存在sql文件中。

正确的配置应该是

--base64-output=decode-rows -vv

如果这里只加一个v，解析后的sql文件中只会有被注释的伪SQL，文件并不能执行。加两个v，解析后的SQL文件才会既有编码后的sql（可执行，又有解码后的sql语句（可供阅读）

坑3

问题

执行mys


http://www.ppmy.cn/news/147449.html

相关文章

v-bind和v-model的区别和联系

v-bind和v-model的区别和联系

1、这两个指令都可以完成数据绑定 2、v-bind是单向数据绑定&#xff0c;只有data里面的数据可以改变视图里的数据 data > html视图 3、v-model是双向数据绑定&#xff0c;data里面的数据和视图里的数据可以互相改变 data <> html视图 4、v-bind可以在任何html标签中…
阅读更多...
OCP-1Z0-051 第68题 ORDER BY的默认值

OCP-1Z0-051 第68题 ORDER BY的默认值

一、原题 Which statement is true regarding the default behavior of the ORDER BY clause? A. In a character sort, the values are case- sensitive. B. NULL values are not considered at all by the sort operation. C. Only those columns that are specified in th…
阅读更多...
显著性水平对应的临界值_统计学问题 常有的显著性水平a 所对应的Z值是哪些...

显著性水平对应的临界值_统计学问题 常有的显著性水平a 所对应的Z值是哪些...

展开全部 z(a/2)指的是标准正态分布的双侧临界值&#xff0c;z(a)当然就是单侧临界值。a(阿尔法)指62616964757a686964616fe4b893e5b19e31333431356638的是显著水平&#xff0c;一般是0.05、0.01等。而95%、99%指的是置信水平&#xff0c;不要搞混这两个概念&#xff0c;置信水…
阅读更多...
黑苹果显卡div和d-sub的选择

黑苹果显卡div和d-sub的选择

今天试了下&#xff0c;d-sub 和dvi 接口&#xff0c;发现真的事dvi接口很清楚&#xff0c;所以推荐大家还是插入div接口的好&#xff0c;现在驱动成功了9800gt 和5750 &#xff0c;还是挺开心的&#xff0c;10.9真的才顺了。
阅读更多...
统计学(三)：置信区间; Z 检验(样本平均数的假设检验), 均值分布, 附Python实现（大牌护肤品碧欧泉背后的秘密）

统计学(三)：置信区间; Z 检验(样本平均数的假设检验), 均值分布, 附Python实现（大牌护肤品碧欧泉背后的秘密）

引言 本篇博文开始前&#xff0c;请熟知如下链接中的概念&#xff1b;当然&#xff0c;如果直接开始&#xff0c;遇到遗忘的统计学名词再返回查找也没问题。 统计学(二)&#xff1a;假设检验导论 (深入浅出超详解&#xff0c;附Python 代码)&#xff1b;置信区间与 Z 检验先修…
阅读更多...
计算类氦离子基态能级z=1-103从氢到铹

计算类氦离子基态能级z=1-103从氢到铹

让原子核外有两个电子&#xff0c;让核电荷数z1-103从H到Lr。 两个1s电子波函数为 能级为 分成三部分&#xff0c;两个电子的动能和势能&#xff0c;加上两个电子之间的排斥能 将第一部分打开 第二部分与第一部分操作方法相同 打开第三部分&#xff0c;单中心双电子排斥能积分…
阅读更多...
matlab画二维颜色深浅,在matlab中如何在二维图中用颜色深浅来代表数值z大小，已知（x,y,z）一组向量。...

matlab画二维颜色深浅,在matlab中如何在二维图中用颜色深浅来代表数值z大小，已知（x,y,z）一组向量。...

在matlab中如何在二维图中用颜色深浅来代表数值z大小，就像ansys中的应力云图那样的，已知(x,y,z)一组向量，其中(x,y)为某一区域内的位置坐标，z为此处的物理量大小。 注：1.x,y形成的网格间距不完全相等，部分区域点比较集中 2.后面附有部分数据，与附件中数据相同 x …
阅读更多...
68pin SCSI接头针脚排序

68pin SCSI接头针脚排序

68pin SCSI接头针脚排序 1.问题 针脚排序&#xff0c;无法确定。 图1 68pin示意图 图2 实物图 2.解决办法 方法1&#xff1a; 咨询客服。直接得出答案。 方法2&#xff1a; 接入端子台&#xff0c;用万用表测得那个连通。 实验方法&#xff1a; 采用万用表蜂鸣档&…
阅读更多...
最新文章

Copyright @ 2022~2023