rds下载备份集在ECS上恢复(解压目录与mysql data目录不同)

环境:
OS:Centos 7
db:mysql5.6

说明:mysql已经部署安装好

 

1.下载备份集
从阿里云下载备份集并确定下载文件的格式,我这里是tar.gz格式的
文件名如下:
hins19550477_data_20220627164157.tar.gz

 

2.解压压缩文件
解压大概需要8个小时,cpu 4核8G内存的机器
[root@hxl 20220628]# cd /home/hxl/backup/rds/20220628
[root@hxl 20220628]# tar -zxvf hins19550477_data_20220627164157.tar.gz

 

文件很大可以将如上命令做成脚本后台执行
vi tar.sh,内容如下:
#!/bin/bash
cd /home/hxl/backup/rds/20220628
tar -zxvf hins19550477_data_20220627164157.tar.gz

 

3.修改目录权限
将刚才解压的目录权限赋予mysql
[root@localhost opt]# cd /home/hxl/backup/rdsuqu7t
[root@localhost opt]# chown -R mysql:mysql ./20220628

 

4.解压完成后将解压包转移到其他的目录
[root@hxl 20220628]# mv hins19550477_data_20220627164157.tar.gz ../

 

5.停掉之前的mysql并将data目录备份后创建新的data目录
停掉
[root@hxl]# /opt/mysql-5.6.40/bin/mysqladmin -h localhost -pmypasswd -uroot -P13306 shutdown

删除之前数据库的data目录下的文件,我的做法是重命名data目录,重新创建,在my.cnf配置文件中找到data目录
[root@localhost mysql]# cd /databak02/mysqldata
[root@localhost mysql]# mv data bakdata
[root@localhost mysql]# mkdir data
[root@localhost mysql]# chown -R mysql:mysql ./data

 

6.编辑配置文件(看情况需要)

 

7.执行恢复

/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/opt/mysql-5.6.40/conf/my.cnf --user=root --apply-log /home/hxl/backup/rdsuqu7t/20220628

InnoDB: Doing recovery: scanned up to log sequence number 75525692112896 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525697355776 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525702598656 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525707841536 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525713084416 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525718327296 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525723570176 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525728813056 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525734055936 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 75525739298816 (73%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 75517516496896 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517521739776 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517526982656 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517532225536 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517537468416 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517542711296 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517547954176 (0%)
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 75517589897216 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517595140096 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517600382976 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517605625856 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517610868736 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517616111616 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75517621354496 (0%)
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 99

InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 75519451119616 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519456362496 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519461605376 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519466848256 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519472091136 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519477334016 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 75519482576896 (17%)

InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 517952869, file name mysql-bin.011047
InnoDB: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 100403138857, 1 rows to undo
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: Rollback of trx with id 100403138857 completed
InnoDB: Rollback of non-prepared transactions completed
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 17537632ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.13 started; log sequence number 75528679183489
InnoDB: xtrabackup: Last MySQL binlog file position 517952869, file name mysql-bin.011047
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=75528679184239
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 75528679184396
InnoDB: Doing recovery: scanned up to log sequence number 75528679184405 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 75528679184405 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 517952869, file name mysql-bin.011047
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: 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 225860ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.13 started; log sequence number 75528679184405
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 75528679184877
220701 13:43:14 completed OK!

 

接着执行,该语句是将上面恢复后的所有文件拷贝到my.cnf指定的data目录
/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/opt/mysql-5.6.40/conf/my.cnf --user=root --copy-back --rsync /home/hxl/backup/rdsuqu7t/20220628

 

/opt/xtrabackup-2.4.7/bin/innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
innobackupex: Can't create/write to file '/opt/mysql-5.6.40/redolog/ib_logfile0' (Errcode: 17 - File exists)
[01] error: cannot open the destination stream for ib_logfile0
[01] Error: copy_file() failed.
这里报错，因为redolog文件存在，删除即可
[root@hxl conf]# cd /opt/mysql-5.6.40/redolog
[root@hxl redolog]# ls
ib_logfile0  ib_logfile1  ib_logfile2
[root@hxlZ redolog]# rm *
rm: remove regular file `ib_logfile0'? y
rm: remove regular file `ib_logfile1'? y
rm: remove regular file `ib_logfile2'? y

 

8.在配置文件中添加如下项目,跳过密码检查
[mysqld]
skip-grant-tables

 

9.修改数据目录权限
[root@hxl mysqldata]# cd /databak02/mysqldata
[root@hxl mysqldata]# chown -R mysql:mysql ./data

 

10.启动数据库
/opt/mysql-5.6.40/bin/mysqld_safe --defaults-file=/opt/mysql-5.6.40/conf/my.cnf --user=mysql &

报错:
2022-07-02 00:17:51 18761 [ERROR] InnoDB: /opt/mysql-5.6.40/redolog/ib_logfile0 can't be opened in read-write mode
2022-07-02 00:17:51 18761 [ERROR] Plugin 'InnoDB' init function returned error.
2022-07-02 00:17:51 18761 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-07-02 00:17:51 18761 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-07-02 00:17:51 18761 [ERROR] Aborting

解决办法,将之前的redolog删除掉
[root@hxl redolog]# mv ib_logfile0 bak_ib_logfile0
[root@hxl redolog]# mv ib_logfile1 bak_ib_logfile1

 

 

11.修改密码
/opt/mysql-5.6.40/bin/mysql -h localhost -uroot -P13306
use mysql;
update user set password=password("mypasswd") where user="root";
flush privileges;

 

12.停止数据库去掉跳过密码的项目启动数据库
这个时候就需要密码登陆了

/opt/mysql-5.6.40/bin/mysqladmin -h 127.0.0.1 -uroot -p -P13306 shutdown
/opt/mysql-5.6.40/bin/mysqld_safe --defaults-file=/opt/mysql-5.6.40/conf/my.cnf --user=mysql &

13.密码登陆
/opt/mysql-5.6.40/bin/mysql -h 127.0.0.1 -uroot -p -P13306