阿里云官方参考文档
目录
- 背景
- 一、环境部署
- 二、目录规划
- 三、操作步骤
- FAQ
背景
今天把一张 5500 多万条记录的表进行按年度拆分，本来打算将表数据拆分为 2020 年、2021 年、2022 年三张新表，提升原表查询效率，仅保留 2023 年数据。表拆分完毕后，就陆续删除原表中 2020 年、2021 年、2022 年的数据。结果在执行 SQL 过程中误操作执行了删除 2023 年全年的 SQL 语句，具体如下：
delete from kys_convert_order where add_time between '2022-11-01 00:00:01' and '2023-12-01 00:00:00';
原本是需要执行这条 SQL 语句的：
delete from kys_convert_order where add_time between '2022-11-01 00:00:01' and '2022-12-01 00:00:00';
发现不对劲后，赶紧 kill 掉 SQL 进程。但庆幸数据没有被删除，我初步猜测是因为 '2022-11-01 00:00:01' and '2023-12-01 00:00:00' 这段时间的数据量很大，SQL 进程可能在做扫表的一个操作。
为了验证数据是否被删除，于是将备份数据 dump 下来（我们使用的是阿里云的 RDS MySQL），备份方式为物理备份，最新备份数据为每天早上 8:00（即备份 策略为一天一次全量备份，共保留 7 天数据）。因此新开了一台按量付费的 Linux 实例，安装好 MySQL 环境，并进行数据恢复，验证与生产环境数据的一致性。以下是恢复详细步骤。
一、环境部署
1、MySQL
与 RDS MySQL 版本保持一致（大版本）
# rds MySQL 版本：5.7
# 本次安装 MySQL 版本：5.7.34# 安装 MySQL 5.7 和 MySQL 官方的 Yum 源管理器
yum -y install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm# 安装 MySQL 5.7
yum -y install mysql-community-server# 启动 MySQL
systemctl start mysqld# 获取初始密码
grep password /var/log/mysqld.log# 修改初始密码
mysqladmin -u root -p'旧密码' password '新密码'# MySQL登录验证
mysql -u root -p'新密码'
2、qpress
解压工具
# 下载可执行文件的tar包
wget "https://static-aliyun-doc.oss-cn-hangzhou.aliyuncs.com/file-manage-files/zh-CN/20230406/flxd/qpress-11-linux-x64.tar"# 解压下载的tar包，取出可执行文件
tar -xvf qpress-11-linux-x64.tar# 设置qpress文件的执行权限
sudo chmod 775 qpress# 拷贝qpress到/usr/bin中
sudo cp qpress /usr/bin
3、Percona XtraBackup
备份/恢复工具
# wget如果下载很慢，你可以先下载到你Windows本地再上传
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm# 开始安装XtraBackup
sudo yum localinstall -y percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm
二、目录规划
1、解压目录
用于存放解压后的备份文件（压缩文件从 RDS MySQL 下载并上传到服务器）
mkdir /var/mysql_bkdata
chown -R $USER:$USER /var/mysql_bkdata
2、数据目录
用于将备份文件恢复到此目录，启动自建数据库时，自建数据库的数据目录使用此目录的数据。
mkdir /var/mysql_newdata
chown -R $USER:$USER /var/mysql_newdata
三、操作步骤
1、下载备份文件
通过阿里云 RDS 控制台下载即可，过程略。可通过 wget 下载，下载的文件后缀有一定要求，否则无法解压，具体看官方说明：
2、解压备份文件
qpress -do test_xb.qp | xbstream -x -v -C /var/mysql_bkdata/
实测了一下，我在自建 MySQL 主机上 wget 了备份文件，且后缀名为 ddz_xb.qp，符合后缀名命名要求，但是通过上面这条命令是执行失败的，但你可以将 ddz_xb.qp 重命名为 ddz_qp.xb，然后使用上图 xbstream 的方式来解压是完全没问题的。
mv ddz_xb.qp ddz_qp.xb
开始解压文件：
# 步骤一：解包
cat ddz_qp.xb | xbstream -x -v -C /var/mysql_bkdata/# 步骤二：解压（时间较长，需耐心等待）
# 适用于MySQL 5.5/5.6/5.7
innobackupex --decompress --remove-original /var/mysql_bkdata/
3、恢复数据
恢复数据库前，请先停止自建数据库服务。
-
停止自建 MySQL 服务
systemctl stop mysqld.service -
恢复前准备
innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/参数解释
参数 含义 –defaults-file 通过传入配置文件设置MySQL默认选项。RDS MySQL备份文件中，提供名为 backup-my.cnf的配置文件，该文件位于备份解压目录，即/var/mysql_bkdata/。–apply-log XtraBackup工具的准备命令。该命令后配置存放备份文件的目录，即备份解压目录 /var/mysql_bkdata/。 -
恢复数据
innobackupex --defaults-file=/etc/my.cnf --copy-back /var/mysql_bkdata/参数解释
参数 含义 –defaults-file 自建数据库的 my.cnf文件，根据此配置文件中设置的数据目录（datadir），获取恢复数据的目标路径。–copy-back XtraBackup工具的恢复命令。该命令后配置存放备份文件的目录，即备份解压目录 /var/mysql_bkdata/，XtraBackup工具将此目录数据恢复到自建数据库的数据目录中。 -
修改自建数据库配置文件
my.cnfvim /etc/my.cnf... # 指定新的数据目录 datadir = /var/mysql_newdata # 参数innodb_undo_tablespaces的取值需要与/var/mysql_bkdata/backup-my.cnf中的取值相同 # 可以使用cat /var/mysql_bkdata/backup-my.cnf | grep innodb_undo_tablespaces查询。 innodb_undo_tablespaces=2 innodb_undo_directory=/var/mysql_newdata# 在RDS MySQL管理控制台中查看实例参数lower_case_table_names的取值，如果取值为1，则需要修改自建数据库配置文件my.cnf。 lower_case_table_names=1 ...
4、启动自建 MySQL
-
为新的数据目录授权
chown -R mysql:mysql /var/mysql_newdata -
启动 MySQL 进程
systemctl stop mysqld.service systemctl status mysqld.service
5、连接数据库并验证
-
登录自建数据库
mysql -uroot -p<对应密码> -
验证数据库是否完整
show databases;
FAQ
在删除数据库表数据时，需要注意这几点：
（1）使用 DELETE 命令和 WHERE 子句来选择指定时间段内的数据。例如，假设需要删除时间范围为 start_time 和 end_time 之间的数据，则可以使用如下命令：
DELETE FROM kys_convert_order WHERE add_time BETWEEN '2022-01-01 00:00:00' AND '2022-03-01 00:00:00';
其中，table_name 是要删除数据的表名，time_column 是表中用于表示时间的列名，BETWEEN start_time AND end_time 是时间范围条件。
（2）使用 ORDER BY 子句按照时间升序（ASC）排序。例如，假设 time_column 是日期类型，则可以使用如下命令：
DELETE FROM kys_convert_order WHERE add_time BETWEEN '2022-01-01 00:00:00' AND '2022-03-01 00:00:00' ORDER BY add_time ASC;
这样就会按照时间顺序执行删除操作，从而确保数据是按照时间顺序删除的。
需要注意的是，如果表中数据比较多，一次性删除可能会导致性能问题，可以考虑使用 LIMIT 限制每次删除的行数。例如，可以使用如下命令：
DELETE FROM kys_convert_order WHERE add_time BETWEEN '2022-01-01 00:00:00' AND '2022-03-01 00:00:00' ORDER BY add_time ASC LIMIT 50000;
这里的 LIMIT 1000 表示每次最多删除 1000 行数据。需要多次执行命令，直到删除完所有指定时间段内的数据为止。
最后开发人员进行数据可用性校验！
—END
