Restore an RDS MySQL logical backup to a self-managed database - ApsaraDB RDS

This topic describes how to use the mysqldump tool, which is included with MySQL, to restore a logical backup file from an ApsaraDB RDS for MySQL instance to a self-managed database.

Note

For more information about how to select a data restoration method, see Overview of data restoration methods.
To restore data from a physical backup file to a self-managed database, see Restore an ApsaraDB RDS for MySQL physical backup to a self-managed database.
For more information about how to back up data for an ApsaraDB RDS for MySQL instance, see Back up an ApsaraDB RDS instance.
You can also use mysqldump to migrate data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance. For more information, see Use mysqldump to migrate MySQL data. For more information about the mysqldump tool options, see RDS MySQL mysqldump options.

Prerequisites

The ApsaraDB RDS for MySQL instance must meet the following requirements:
- Major version: 8.0, 5.7, 5.6, or 5.5
- Edition: High-availability Edition
- Storage type: Local SSD
Note
You can view this information on the Basic Information page of the instance.
A logical backup file is available. For more information, see Automatic backup.

Demo environment

The local MySQL database is installed on a 64-bit Linux system and runs the same MySQL version as the ApsaraDB RDS for MySQL instance. This topic uses CentOS 7 and MySQL 5.7 as an example.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, click Backup and Restoration.
On the Base Backups > Data Backup tab. Find the logical backup file that you want to download and click Download Instance Backup File in the Actions column.
Note
- You must create logical backups manually. For more information, see Manual backup.
- If the Download Instance Backup File button is unavailable, check whether your instance edition supports backup downloads.
In the Download Instance Backup File dialog box, click Copy next to the external URL to obtain the external download URL of the data backup file.
Important
- A free quota is provided for backup downloads over the internet. If the traffic that is generated by backup downloads exceeds the free quota, you are charged for the excess traffic. For more information, see Billing.
- If your ECS instance and RDS instance are in the same VPC, you can use the internal network address for a faster and more stable download.
Log on to the Linux system where the self-managed database is located. Run the following command to download the logical backup file.
```
wget -c '<external_download_URL_of_the_data_backup_file>' -O <custom_file_name>.tar
```
Note
- -c: Enables resumable downloads.
- -O: Saves the downloaded file with the specified file name.
Decompress the logical backup file. The file contains compressed files for default system databases and for the databases that you created.
1. Run the following command to decompress the backup file.
```
tar xvf <custom_file_name>.tar -C /tmp
```
  Note
  If an error message such as This does not look like a tar archive is returned, confirm that you have downloaded an RDS logical backup file.
  If an error message such as Wrote only 512 of 10240 bytes. Exiting with failure status due to previous errors is returned, check whether your disk space is full. You can change the instance configuration to increase the disk space and then try again.
2. Run the following command to view the directory structure after the decompression.
```
tree /tmp/backup_root/  # Replace with the actual root directory after decompression.
```
  The following example shows a multi-level backup file structure:
```
/tmp/backup_root/
├── database1/   # Directory of target database 1
│ ├── schema.sql # Database schema file
│ └── data.sql   # Data file
├── database2/   # Directory of target database 2
│ ├── schema.sql
│ └── data.sql
└── config.txt    # Backup metadata (optional)
```

Run the following command to navigate to the target database directory.

cd /tmp/backup_root/database_name  # Replace with the actual database name.

Decompress the compressed files of the target database that you want to restore. The filenames end with .sql.gz. The command is as follows:
```
gzip -d schema.sql.gz  # Decompress the schema file.
gzip -d data.sql.gz    # Decompress the data file.
```
Note
The decompressed .sql files are used for the import in Step 10.
Log on to the database and create a corresponding empty database. The commands are as follows:
Note
The user in the following commands must have the permissions to execute all SQL statements in the .sql files.
```
mysql -u user -p<database_password>
create database <empty_database_name>;
exit
```
Use the following commands to import the .sql files into the corresponding database.
```
# Import the table schema.
mysql -u user -p <empty_database_name> < schema.sql

# Import the data.
mysql -u user -p <empty_database_name> < data.sql
```
Note
- After you run the command, you are prompted to enter a password. Enter the password and press the Enter key.
- If the "Can't find master key from keyring" error is reported, check whether the prerequisites in this topic are met.

Verify the restoration. If data is displayed in the database, the migration is successful.

mysql -u user -p
mysql> USE <empty_database_name>;
mysql> SHOW TABLES;       # Check if the tables exist.
mysql> SELECT COUNT(*) FROM <table_name>;  # Verify the data volume.

FAQ

Why does my instance not have any logical backups?
By default, the system creates physical backups. If you need logical backups, you must create them manually. For more information, see Automatic backup.
When I download a logical backup, why is the value for Restore Point in Time 0?
ApsaraDB RDS for MySQL can restore data to a specific point in time using a physical backup file and a log backup file. Therefore, a specific Restore Point in Time (UNIX timestamp) is displayed for physical backups. Logical backups are not used for point-in-time restore. As a result, the value is 0.
How do I resolve the ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. error?
This error is caused by the global transaction identifier (GTID). Use one of the following solutions:
- Enable GTID and then run the restoration operation again.
- Do not enable GTID. Comment out all GTID_PURGED content in the import file, which ends with .sql, and then run the restoration operation again.
- If primary/secondary replication is not used, log on to the database, run the RESET MASTER command, and then run the restoration operation again.
How do I resolve the ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED error?
An import file that ends with .sql and contains GTID information requires that the current database does not contain other GTID information. Log on to the database, run the RESET MASTER command to reset the database, and then run the restoration operation again.
Why is the restored data only in the primary database and not automatically synchronized to the secondary database?
Check the import file, which ends with .sql, for the SESSION.SQL_LOG_BIN= 0 setting. This setting prevents operations on the primary database from being synchronized to the secondary database.