| 知乎专栏 ｜ 多维度架构 |
从 RDS 上下载的物理备份
[root@master backup]# ls hins19648946_data_20220913035432_qp.xb
安装依赖库
[root@master backup]# dnf install -y qpress libaio
安装恢复软件
[root@master backup]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/tarball/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz [root@master backup]# tar zxvf percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12.tar.gz [root@master backup]# mv percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12 /srv/ [root@master backup]# rm -f /srv/percona-xtrabackup [root@master backup]# ln -s /srv/percona-xtrabackup-2.4.24-Linux-x86_64.glibc2.12 /srv/percona-xtrabackup
cat >> /etc/profile.d/percona.sh <<EOF # Percona Xtrabackup export PATH=$PATH:/srv/percona-xtrabackup/bin EOF source /etc/profile.d/percona.sh
恢复数据
[root@master backup]# xbstream -v -x -C ./rds [root@master backup]# xbstream -x -C rds/ < hins19648946_data_20220913035432_qp.xb
解压
innobackupex --decompress --remove-original rds/
恢复日志
innobackupex --defaults-file=/etc/my.cnf --apply-log rds/
启动 mysql 增加 --skip-grant-tables 选项，然后运行 mysql_upgrade
mysql_upgrade
去掉 --skip-grant-tables 选项，重启 MySQL，恢复完成。
安装 percona-xtrabackup 恢复软件
[root@master backup]# tar zxvf percona-xtrabackup-8.0.29-22-Linux-x86_64.glibc2.17.tar.gz [root@master backup]# mv percona-xtrabackup-8.0.29-22-Linux-x86_64.glibc2.17 /srv/ [root@master backup]# rm -f /srv/percona-xtrabackup [root@master backup]# ln -s /srv/percona-xtrabackup-8.0.29-22-Linux-x86_64.glibc2.17/ /srv/percona-xtrabackup
cat >> /etc/profile.d/percona.sh <<EOF # Percona Xtrabackup export PATH=$PATH:/srv/percona-xtrabackup/bin EOF source /etc/profile.d/percona.sh
当前表改名，然后再基于旧表创建新的空表
ALTER TABLE old_table_name RENAME TO new_table_name; CREATE TABLE current_table_name LIKE new_table_name;
ALTER TABLE old_table_name RENAME TO new_table_name; CREATE TABLE old_table_name SELECT * FROM new_table_name WHERE ...;
RDS 目前没有找到导出 csv 的方案，客服让使用 DMS 中的数据导出功能
mysql> show grants for 'root'@'%'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@% | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*F5200743F6F42815CB2DC833C98D6E7730F8E2E2' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
查询未找到 FILE 权限，官网权限列表中也没有 https://help.aliyun.com/document_detail/146395.html
mysql> GRANT FILE ON *.* to neo@'%' identified by 'chen'; Query OK, 0 rows affected (0.026 sec)
secure_file_priv 只要不是 null 即可，阿里云配置是 secure_file_priv = ''。
mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | secure_file_priv | | +------------------+-------+ 1 row in set (0.01 sec)