The process of restoring a backup of one of the databases in RDS

Note: Our company's RDS uses the xtrabackup tool for physical full backups.

Process:

1. First log in to Management Center》Business Management》Cloud Database MYSQL》click Manage to enter, click Backup, select the required backup file, and download it locally or to a cloud server (the following tutorial uses a cloud server).

2. Install mysql and xtrabackup on the cloud server. The versions must be consistent with RDS. (Our company's xtrabackup version is 8.0.22)

3. Upload the downloaded backup file to the cloud server, and restore the entire database through xtrabackup.

4. As needed, export single or multiple database files, format .sql

5. Then use phpmyadmin or other tools to import the exported .sql file back into RDS for successful recovery.

The following are the specific operation steps for restoring a single database:

Version: rds database mysql 8.0 , xtrabackup 8.0.22.

1. First activate a linux system cloud server, install mysql8.0 through BaoTa Panel, and upload the downloaded rds physical backup auto_269_xxx.tar.gz to the server.

2. Install xtrabackup 8.0.22

Visit https://www.percona.com/downloads to download xtrabackup 8.0.22 .

[root@ebs-149559 ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.22-15/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm

# Install xtrabackup 8.0.22

[root@ebs-149559 ~]# yum install -y percona-xtrabackup-80-8.0.22-15.1.el7.x86_64.rpm

# Extract the physical backup file

3. Restore the backup file through xtrabackup

[root@ebs-149559 ~]# tar zxvf auto_269_xxx.tar.gz

# Stop the mysql database

[root@ebs-149559 ~]# service mysqld stop

# Modify my.cnf and replace lower_case_table_names=0

[root@ebs-149559 ~]# sed -i '/lower_case_table_names/c lower_case_table_names=0' /etc/my.cnf

# Delete all files under the mysql database directory

[root@ebs-149559 ~]# rm -rf /www/server/data/*

# Preparation before recovery 

[root@ebs-149559 ~]# xtrabackup --prepare --target-dir=auto_269_xxx

# Restore data 

[root@ebs-149559 ~]# xtrabackup  --copy-back --target-dir=auto_269_xxx

# Reset database directory permissions

[root@ebs-149559 ~]# chown mysql:mysql /www/server/data/ -Rf

# Start the database. After startup, the database root password is the previous rds root password.

[root@ebs-149559 ~]# service mysqld start

4. Export a single database, refer to: https://www.west.cn/faq/list.asp?unid=566

5. Import and restore through Navicat, refer to: https://www.west.cn/faq/list.asp?unid=430

The above is a reference process; actual operation shall prevail.

If you need our company to perform the backup restoration on your behalf, please submit a ticket for paid processing (300 yuan/time).