This topic describes how to restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance. The restoration requires the mysqldump utility of MySQL.
Prerequisites
- Your RDS instance runs one of the following MySQL versions and RDS editions:
- MySQL 8.0 on RDS High-availability Edition (with local SSDs)
- MySQL 5.7 on RDS High-availability Edition (with local SSDs)
- MySQL 5.6
- MySQL 5.5
- A logical backup is complete. For more information, see Back up an RDS instance.
- For more information about how to restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL instance, see Restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database.
- For more information about how to back up an ApsaraDB RDS for MySQL instance, see Back up the data of an RDS instance.
Runtime environment
The self-managed MySQL instance runs in a 64-bit Linux operating system. In addition, it runs the same MySQL version as your RDS instance. In this topic, Linux 7 and MySQL 5.7 are used as examples.
Procedure
FAQ
- Why does my RDS instance not have logical backup files?
By default, ApsaraDB RDS creates physical backups. You must manually create logical backups if required. For more information, see Back up an RDS instance.
- When I download a logical backup file, why is the value in the Backup Set Restore Point column displayed as 0 for the file?
ApsaraDB RDS for MySQL allows you to restore data to a specific point in time by using a physical backup file and a log backup file. Therefore, the ApsaraDB RDS console provides the Backup Set Restore Point column, where you can view the timestamp of each physical backup file. Logical backup files are not used to restore data to a specific point in time. Therefore, the value in the Backup Set Restore Point column is 0 for each logical backup file.
- What do I do if "
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
" is reported?This issue is caused by the GTID feature. You can consider the following solutions:
- Enable the GTID feature. Then, perform the restoration again.
- Do not enable the GTID feature. Comment out the GTID_PURGED parameter in the
.sql
file that you want to import. Then, perform the restoration again. - Check that the synchronous replication mode is disabled. Log on to the self-managed
MySQL instance, run the
reset master
command, and then perform the restoration again.
- What do I do if "
ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED
" is reported?The imported
.sql
file contains some GTIDs that can be found on the self-managed MySQL instance. In this case, log on to the self-managed MySQL instance, run thereset master
command, and then perform the restoration again. - After the data is restored to the self-managed MySQL instance, why is the data not
automatically synchronized to the secondary instance of the self-managed MySQL instance?
Check whether you can find the "
SESSION.SQL_LOG_BIN= 0
" setting at the end of the imported.sql
file. If the setting is specified, the data that is restored to the self-managed MySQL instance cannot be automatically synchronized to the secondary instance.