环境介绍：
CentOS 7.5
Mysql 5.7
percona-xtrabackup-24
注意事项
操作系统中已安装数据恢复工具Percona XtraBackup，您可以从Percona XtraBackup官网下载安装。
MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3，安装指导请参见官方文档Percona XtraBackup 2.3。MySQL 5.7版本需要安装 Percona XtraBackup 2.4，安装指导请参见官方文档Percona XtraBackup 2.4。MySQL 8.0版本需要安装 Percona XtraBackup 8.0，安装指导请参见官方文档Percona XtraBackup 8.0。
自建MySQL数据库安装在64位的Linux系统中，且与云数据库MySQL版的版本相同。
备份恢复操作步骤
1.登录RDS管理控制台。
2.在页面左上角，选择实例所在地域。
3.找到目标实例，单击实例ID。
4.在左侧导航栏中单击备份恢复。
5.选择数据备份标签页。
6.选择查询的时间范围，然后单击查询。
7.在数据备份列表中，找到要下载的数据备份，并单击其右侧的下载。
8.在实例备份文件下载窗口，单击复制外网地址旁的复制图标，获取数据备份文件外网下载地址。
9.登录服务器。
10.执行如下命令，下载数据备份文件。
wget -c '<数据备份文件外网下载地址>' -O <自定义文件名>
-c：启用断点续传模式。
-O：将下载的结果保存为指定的文件（使用URL中包含的文件名后缀 .tar.gz 、.xb.gz 或 _qp.xb）。
11.innobackupex解压命令需要安装qpress，下载qpress工具，然后使用如下命令安装：
下载qpress
[root@test2 ~]# mkdir crmdb[root@test2 ~]# cd crmdb[root@test2 ~]# ls crmdb/hins8537769_data_132950.tar qpress-11-linux-x64.tar[root@test2 ~]# ll crmdb/总用量 403196-rw-r--r-- 1 root root 412790614 7月 9 10:53 hins8537769_data_132950.tar //RDS数据库全备文件-rw-r--r-- 1 root root81920 7月 9 14:00 qpress-11-linux-x64.tar[root@test2 ~]# tar -xvf crmdb/qpress-11-linux-x64.tarqpress[root@test2 ~]# chmod 755 qpress[root@test2 ~]# cp qpress /usr/bin/
12.执行如下命令，解压已下载的数据备份文件。
目前物理备份集文件有3种格式：
tar 压缩包 （.tar.gz 后缀）xbstream 压缩包 （.xb.gz 后缀）xbstream 文件包（_qp.xb 后缀）
1.对于tar 压缩包 （.tar.gz 后缀），使用命令：
tar -izxvf <数据备份文件名>.tar.gz -C <数据库恢复到这个目录>
2.对于xbstream 压缩包 （.xb.gz 后缀），使用命令：
gzip -d -c <数据备份文件名>.xb.gz | xbstream -x -v -C <数据库恢复到这个目录>
3.对于xbstream 文件包（_qp.xb 后缀），使用命令：
--解包cat <数据备份文件名>_qp.xb | xbstream -x -v -C <数据库恢复到这个目录>--MySQL 5.6/5.7解压innobackupex --decompress --remove-original <数据库恢复到这个目录>--MySQL 8.0解压xtrabackup --decompress --remove-original --target-dir=<数据库恢复到这个目录>
注意：
由于阿里云RDS数据库备份的是物理全备文件，所以最好是恢复数据至新安装的Mysql数据库。
-C：指定文件要解压到的目录。可选参数，若不指定就解压到当前目录。
[root@test2 ~]# tar -izxvf crmdb/hins8537769_data_132950.tar -C /var/lib/mysqlcrmdb/weixin_textmsg.ibdcrmdb/sys_dept.ibdcrmdb/ybc_recovery.ibdcrmdb/sys_user_qx.ibdcrmdb/imptable.ibdcrmdb/ybc_refund.ibdcrmdb/ybt_type.ibdcrmdb/work_count.ibdcrmdb/ybc_branch.ibdcrmdb/ybc_follow.ibdcrmdb/weixin_command.ibdcrmdb/sys_user.ibdcrmdb/version_message.ibdcrmdb/sys_menu.ibdcrmdb/sys_app_user.ibdcrmdb/vip_visit.ibdcrmdb/ybc_house.ibdcrmdb/ybc_message.ibdcrmdb/sys_dictionaries.ibd...[root@test2 ~]# ls -l /var/lib/mysql//查询解压后生成的文件总用量 324852-rw-r----- 1 mysql mysql 56 7月 10 16:41 f-rw-rw---- 1 root root 536 7月 8 13:31 backup-f-rw------- 1 mysql mysql1680 7月 10 16:41 ca-key.pem-rw-r--r-- 1 mysql mysql1112 7月 10 16:41 ca.pem-rw-r--r-- 1 mysql mysql1112 7月 10 16:41 client-cert.pem-rw------- 1 mysql mysql1680 7月 10 16:41 client-key.pemdrwxr-xr-x 2 root root 4096 7月 10 16:48 crmdbdrwxr-xr-x 2 root root 4096 7月 10 16:48 crmdb_test-rw-r----- 1 mysql mysql 425 7月 10 16:41 ib_buffer_pool-rw-rw---- 1 root root 209715200 7月 8 12:31 ibdata1-rw-r----- 1 mysql mysql 50331648 7月 10 16:41 ib_logfile0-rw-r----- 1 mysql mysql 50331648 7月 10 16:41 ib_logfile1-rw-r----- 1 mysql mysql 12582912 7月 10 16:41 ibtmp1drwxr-x--- 2 mysql mysql4096 7月 10 16:48 mysqlsrwxrwxrwx 1 mysql mysql 0 7月 10 16:41 mysql.sock-rw------- 1 mysql mysql 5 7月 10 16:41 mysql.sock.lockdrwxr-x--- 2 mysql mysql8192 7月 10 16:48 performance_schema-rw------- 1 mysql mysql1680 7月 10 16:41 private_key.pem-rw-r--r-- 1 mysql mysql 452 7月 10 16:41 public_key.pem-rw-r--r-- 1 mysql mysql1112 7月 10 16:41 server-cert.pem-rw------- 1 mysql mysql1680 7月 10 16:41 server-key.pemdrwxr-x--- 2 mysql mysql8192 7月 10 16:48 sys-rw-rw---- 1 root root 119 7月 8 13:31 xtrabackup_binlog_info-rw-rw---- 1 root root 147 7月 8 13:31 xtrabackup_checkpoints-rw-rw---- 1 root root 845 7月 8 13:31 xtrabackup_info-rw-rw---- 1 root root 9566720 7月 8 13:31 xtrabackup_logfile-rw-rw---- 1 root root 74 7月 8 13:31 xtrabackup_slave_filename_info-rw-rw---- 1 root root 162 7月 8 13:31 xtrabackup_slave_info
13.安装Percona XtraBackup 2.4（Mysql 5.7需要），恢复备份数据
[root@test2 ~]# yum -y install /yum/percona-release-latest.noarch.rpm...已安装:percona-release.noarch 0:1.0-21完毕！[root@test2 ~]# yum list | grep percona...percona-release.noarch 1.0-21 installedpercona-xtrabackup-24.x86_64 2.4.5-1.el7 installedPercona-Server-55-debuginfo.x86_64 5.5.62-rel38.14.el7 percona-release-x86_64Percona-Server-56-debuginfo.x86_64 5.6.48-rel88.0.1.el7percona-release-x86_64Percona-Server-57-debuginfo.x86_64 5.7.30-33.1.el7percona-release-x86_64Percona-Server-80-info.x86_64 8.0-1.el7 percona-release-x86_64Percona-Server-MongoDB.x86_64 3.0.15-1.10.el7percona-release-x86_64Percona-Server-MongoDB-32.x86_64 3.2.22-3.13.el7percona-release-x86_643.2.22-3.13.el7percona-release-x86_64Percona-Server-MongoDB-32-mongos.x86_64 3.2.22-3.13.el7percona-release-x86_64Percona-Server-MongoDB-32-server.x86_64 3.2.22-3.13.el7percona-release-x86_64Percona-Server-MongoDB-32-shell.x86_64 3.2.22-3.13.el7percona-release-x86_64Percona-Server-MongoDB-32-tools.x86_64 3.2.22-3.13.el7percona-release-x86_64Percona-Server-MongoDB-34.x86_64 3.4.24-3.0.el7percona-release-x86_643.4.24-3.0.el7percona-release-x86_64Percona-Server-MongoDB-34-mongos.x86_64 3.4.24-3.0.el7percona-release-x86_64...[root@test2 ~]# yum -y install percona-xtrabackup-24.x86_64...已安装:percona-xtrabackup-24.x86_64 0:2.4.20-1.el7完毕！
14.为避免版本问题，需修改backup-f参数
[root@test2 ~]# vim /var/lib/mysql/backup-f[mysqld]innodb_checksum_algorithm=crc32#innodb_log_checksum_algorithm=strict_crc32 //注释掉innodb_data_file_path=ibdata1:200M:autoextendinnodb_log_files_in_group=2innodb_log_file_size=1048576000#innodb_fast_checksum=false //注释掉innodb_page_size=16384#innodb_log_block_size=512 //注释掉innodb_undo_directory=./innodb_undo_tablespaces=0server_id=2931342899#redo_log_version=1 //注释掉#server_uuid=cf7b6786-a55c-11e9-94b7-506b4b231e40 //注释掉#master_key_id=0 //注释掉#innodb_encrypt_algorithm=AES_256_CBC//注释掉
15.执行如下命令，恢复解压好的备份文件并修改文件属主，并确定文件所属为MySQL用户。
Mysql5.6/5.7/8.0恢复数据时，backup-f配置文件为上面RDS备份文件解压后的目录中（即，要恢复的文件夹中），本文中数据恢复至/var/lib/mysql/中，Mysql 5.7为刚安装的新库，无任何数据。
–MySQL 5.6/5.7–
innobackupex --defaults-file=<数据库恢复到的目录>/backup-f --apply-log <数据库恢复到的目录>
–MySQL 8.0–
xtrabackup --prepare --target-dir=<数据库恢复到的目录>
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=<数据库恢复到的目录>
[root@test2 ~]# chown -R mysql:mysql /var/lib/mysql //修改文件属主属组[root@test2 ~]# ll /var/lib/mysql总用量 2278460-rw-r----- 1 mysql mysql 56 7月 10 16:41 f-rw-rw---- 1 mysql mysql 543 7月 10 16:50 backup-f-rw------- 1 mysql mysql 1680 7月 10 16:41 ca-key.pem-rw-r--r-- 1 mysql mysql 1112 7月 10 16:41 ca.pem-rw-r--r-- 1 mysql mysql 1112 7月 10 16:41 client-cert.pem-rw------- 1 mysql mysql 1680 7月 10 16:41 client-key.pemdrwxr-xr-x 2 mysql mysql 4096 7月 10 17:26 crmdbdrwxr-xr-x 2 mysql mysql 4096 7月 10 16:48 crmdb_test-rw-r----- 1 mysql mysql 425 7月 10 16:41 ib_buffer_pool-rw-rw---- 1 mysql mysql 209715200 7月 10 17:39 ibdata1-rw-r----- 1 mysql mysql 1048576000 7月 10 17:39 ib_logfile0-rw-r----- 1 mysql mysql 1048576000 7月 10 17:39 ib_logfile1-rw-r----- 1 mysql mysql 12582912 7月 10 17:39 ibtmp1drwxr-x--- 2 mysql mysql 4096 7月 10 16:48 mysqlsrwxrwxrwx 1 mysql mysql0 7月 10 16:41 mysql.sock-rw------- 1 mysql mysql5 7月 10 16:41 mysql.sock.lockdrwxr-x--- 2 mysql mysql 8192 7月 10 16:48 performance_schema-rw------- 1 mysql mysql 1680 7月 10 16:41 private_key.pem-rw-r--r-- 1 mysql mysql 452 7月 10 16:41 public_key.pem-rw-r--r-- 1 mysql mysql 1112 7月 10 16:41 server-cert.pem-rw------- 1 mysql mysql 1680 7月 10 16:41 server-key.pemdrwxr-x--- 2 mysql mysql 8192 7月 10 16:48 sys-rw-rw---- 1 mysql mysql 119 7月 8 13:31 xtrabackup_binlog_info-rw-r--r-- 1 mysql mysql 24 7月 10 17:38 xtrabackup_binlog_pos_innodb-rw-rw---- 1 mysql mysql 147 7月 10 17:38 xtrabackup_checkpoints-rw-rw---- 1 mysql mysql 845 7月 8 13:31 xtrabackup_info-rw-rw---- 1 mysql mysql 13565952 7月 10 17:38 xtrabackup_logfile-rw-r--r-- 1 mysql mysql1 7月 10 17:38 xtrabackup_master_key_id-rw-rw---- 1 mysql mysql 74 7月 8 13:31 xtrabackup_slave_filename_info-rw-rw---- 1 mysql mysql 162 7月 8 13:31 xtrabackup_slave_info[root@test2 ~]# innobackupex --defaults-file=/var/lib/mysql/backup-f --apply-log /var/lib/mysqlxtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_data_file_path=ibdata1:200M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=1048576000 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=2931342899xtrabackup: recognized client arguments:10 17:38:03 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully.At the end of a successful apply-log run innobackupexprints "completed OK!".innobackupex version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)xtrabackup: cd to /var/lib/mysql/xtrabackup: This target seems to be not prepared yet.InnoDB: Number of pools: 1xtrabackup: xtrabackup_logfile detected: size=13565952, start_lsn=(10489267059)xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:200M:autoextendxtrabackup: innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 13565952xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:200M:autoextendxtrabackup: innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 1xtrabackup: innodb_log_file_size = 13565952xtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)InnoDB: PUNCH HOLE support availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin __atomic_thread_fence() is used for memory barrierInnoDB: Compressed tables use zlib 1.2.7InnoDB: Number of pools: 1InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100MInnoDB: Completed initialization of buffer poolInnoDB: page_cleaner coordinator priority: -20InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 10489267059InnoDB: Doing recovery: scanned up to log sequence number 10494509568 (43%)InnoDB: Doing recovery: scanned up to log sequence number 10498831278 (79%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99InnoDB: Apply batch completedInnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448InnoDB: Removed temporary tablespace data file: "ibtmp1"InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...InnoDB: File './ibtmp1' size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: Waiting for purge to startInnoDB: 5.7.26 started; log sequence number 10498831278InnoDB: page_cleaner: 1000ms intended loop took 46390ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)InnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 10498832727InnoDB: Number of pools: 1xtrabackup: using the following InnoDB configuration for recovery:xtrabackup: innodb_data_home_dir = .xtrabackup: innodb_data_file_path = ibdata1:200M:autoextendxtrabackup: innodb_log_group_home_dir = .xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 1048576000InnoDB: PUNCH HOLE support availableInnoDB: Mutexes and rw_locks use GCC atomic builtinsInnoDB: Uses event mutexesInnoDB: GCC builtin __atomic_thread_fence() is used for memory barrierInnoDB: Compressed tables use zlib 1.2.7InnoDB: Number of pools: 1InnoDB: Using CPU crc32 instructionsInnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100MInnoDB: Completed initialization of buffer poolInnoDB: page_cleaner coordinator priority: -20InnoDB: Setting log file ./ib_logfile101 size to 1000 MBInnoDB: Progress in MB:100 200 300 400 500 600 700 800 900 1000InnoDB: Setting log file ./ib_logfile1 size to 1000 MBInnoDB: Progress in MB:100 200 300 400 500 600 700 800 900 1000InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0InnoDB: New log files created, LSN=10498832727InnoDB: Highest supported file format is Barracuda.InnoDB: Log scan progressed past the checkpoint lsn 10498832908InnoDB: Doing recovery: scanned up to log sequence number 10498832917 (0%)InnoDB: Database was not shutdown normally!InnoDB: Starting crash recovery.InnoDB: xtrabackup: Last MySQL binlog file position 593541, file name mysql-bin.001448InnoDB: Removed temporary tablespace data file: "ibtmp1"InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...InnoDB: File './ibtmp1' size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: page_cleaner: 1000ms intended loop took 35376ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)InnoDB: 5.7.26 started; log sequence number 10498832917xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1049883293610 17:39:28 completed OK!
可以看到执行 innobackupex 恢复数据成功了。
注意：
数据恢复成功后，由于我们是恢复的RDS全备文件，所以我们自建的Mysql数据库之前设置的密码会被覆盖成RDS的备份数据库的密码，如果忘记密码，可以临时在Mysql配置文件中加入skip-grant-tables配置，无密码进入数据库，验证数据恢复情况，后面再重置root密码。
[root@test2 ~]# vim /etc/f[mysqld]...skip-grant-tables
16.执行如下命令，启动MySQL进程。
注意：
在启动Mysql进程的时候，由于我是直接将备份数据恢复到Mysql默认的数据库目录/var/lib/mysql中，所以可以直接systemctl start mysqld启动Mysql进程。如果最开始是将RDS数据库的备份文件恢复到了其他目录中，那么在启动Mysql数据库进程的时候就需要使用mysqld_safe指定数据库目录启动进程，否则默认方式启动数据库后是看不到恢复的数据的。
命令：
mysqld_safe --defaults-file=<数据库恢复到的目录>/backup-f --user=mysql --datadir=<数据库恢复到的目录> &
[root@test2 ~]# systemctl start mysqld [root@test2 ~]# mysql -urootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.30 MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use crmdb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+------------------------+| Tables_in_crmdb |+------------------------+| 产品表_rsow_product|| calllist|| cus_manage_set || dictionary || importtel || imptable|| imptable2 || log|| rsow_product || rxpbls || sys_app_user || sys_dept|| sys_dictionaries || sys_gl_qx || sys_menu|| sys_role|| sys_user|| sys_user_qx || tb_pictures || test || tmp_ybc|| user |mysql> select * from user;+--------+----------+----------+------+------+------------+| userID | userName | password | name | sex | department |+--------+----------+----------+------+------+------------+|1 | admin | admin | NULL | NULL | NULL ||2 | 111111 | 111111 | NULL | NULL | NULL ||3 | 222222 | 222222 | NULL | NULL | NULL ||4 | 333333 | 333333 | NULL | NULL | NULL ||5 | 444444 | 444444 | NULL | NULL | NULL ||6 | 555555 | 555555 | NULL | NULL | NULL |+--------+----------+----------+------+------+------------+6 rows in set (0.00 sec)
可以看到数据成功展示，数据恢复成功！！！
重置连接Mysql数据库的Root密码
密码重置报错解决：
关于将阿里云RDS数据库或其他主机数据库全备文件恢复至自建数据库后密码被覆盖，并在重置密码时出现各种报错的问题解决
1.修改配置文件，添加跳过授权表配置启动MySQL服务
[root@test2 ~]# vim /etc/f[mysqld]...skip-grant-tables
2.重启Mysql服务进程后登陆数据库修改root密码
[root@test2 ~]# systemctl restart mysqld [root@test2 ~]# mysql -urootmysql> use mysql;mysql> update user set authentication_string=password('123qqq...A') where user='root' and host='%';mysql> flush privileges;mysql> exitBye[root@test2 ~]# sed -i s/skip-grant-tables//g /etc/f[root@test2 ~]# systemctl restart mysqld[root@test2 ~]# mysql -uroot -p123qqq...Amysql> select user();+--------+| user() |+--------+| root@ |+--------+1 row in set (0.00 sec)
重置数据库密码成功！
其他相关报错解决：
1.安装Mysql报错：错误：软件包：mysql-community-server-5.7.30-1.el7.x86_64 (mysql57-community) 需要：mysql-community解决
2.安装percona-xtrabackup-24报错： file /etc/f from install of Percona-Server-shared-56-5.6.48-rel88.解决
3.阿里云数据库RDS MySQL 物理全备文件恢复到自建数据库Mysql报错：InnoDB: Log file ./ib_logfile1 is of different size…xtrabacku
↓↓↓↓↓↓
最近刚申请了个微信公众号，上面也会分享一些运维知识，大家点点发财手关注一波，感谢大家。【原创公众号】：非著名运维 【福利】：公众号回复 “资料” 送运维自学资料大礼包哦！
