通过MySQL binlog日志恢复DataEase误删资源

Lukey 字数: 3335 阅读耗时: 8 分钟 2023/06/16 博客独享热度: 28 评论: 0

1. 场景介绍

binlog 是 MySQL 的二进制格式文件，会记录用户对数据库的更新信息，例如更改数据库表和更改内容的SQL语句都会记录到 binlog 里，但是对库表的查询不会记录。

DataEase 的 MySQL 默认情况下没有开启 binlog，开启 binlog 后，会记录用户对 DataEase 资源的新建、修改、删除等操作，如果用户希望恢复到某一时间点的状态，可通过数据库备份和 binlog 来完成。

2. 添加 binlog 配置

在 DataEase 的安装目录的 MySQL 配置文件增加配置，开启 binlog

binlog 文件将保存在 MySQL 容器中的 /var/lib/mysql 目录，该目录在安装 DataEase 时已持久化到宿主机的 /opt/dataease/data/mysql 目录。

sed -i '2i\

server_id=110\

log_bin=/var/lib/mysql/mysql-bin\

binlog-format=mixed\

sync-binlog=1\

expire_logs_days=7\

max_binlog_size=100m\

binlog_cache_size=10m\

max_binlog_cache_size=512m\

binlog_stmt_cache_size=100m\

max_binlog_stmt_cache_size=100m\n' /opt/dataease/conf/my.cnf

重启 mysql 容器

dectl restart mysql

3. 增加数据库备份

编写备份脚本，增加全量备份设置，并定期运行备份脚本

vi /opt/backup-de.sh

#!/bin/bash

TIME=`date "+%Y_%m_%d-%H_%M_%S"`

BAK_DIR=/opt/bak

[ ! -d $BAK_DIR ] && mkdir $BAK_DIR

cd $BAK_DIR

mysqldump -uroot -pPassword123@mysql dataease >> $BAK_DIR/mysql-dataease_$TIME.bak.sql

mysqldump -uroot -pPassword123@mysql -ntd -R dataease > $BAK_DIR/mysql-function_$TIME.bak.sql

# delete history backup file

find $BAK_DIR/ -mtime +7 -name "*.sql" -exec rm -rf {} \;

给脚本赋执行权限

[root@dataease opt]# chmod +x /opt/backup-de.sh

创建执行计划

每天凌晨 1 点执行备份任务

00 01  * /opt/backup-de.sh

4. binlog恢复

示例：将 DataEase 恢复到 2023年 5月 29日 15:00 的状态

# 将2023 年 5 月 29 日 凌晨 1 点的全量备份复制到 MySQL 容器目录中

# "/opt/dataease/data/mysql" 为 MySQL 容器持久化目录

cp /opt/bak/mysql-dataease_2023_05_29-01_00_00.bak.sql /opt/dataease/data/mysql

#登录 MySQL

docker exec -it mysql mysql -uroot -pPassword123@mysql

# 查看 binlog，确定最新的 binlog 文件

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |  49475562 |

| mysql-bin.000002 |      8783 |

| mysql-bin.000003 |    153284 |

+------------------+-----------+

3 rows in set (0.01 sec)

mysql> exit

# 先进行全量恢复，恢复 2023年 5月 29日 凌晨 1 点的备份

mysql -uroot -pPassword123@mysql < /var/lib/mysql/mysql-dataease_2023_05_29-01_00_00.bak.sql

# 通过 binlog 恢复 2023年 5月 29日 凌晨 1 点到 15 点的数据

mysqlbinlog /var/lib/mysql/bin-log/mysql-bin.000003 --start-datetime="2023-05-29 01:00:00" --stop-datetime="2023-05-29 15:00:00" --database=dataease --skip-gtids | mysql -uroot -pPassword123@mysql

---