This topic describes how to restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database.

Note

Prerequisites

  1. The RDS instance meets the following requirements:
    • The RDS instance runs MySQL 8.0, MySQL 5.7, MySQL 5.6, or MySQL 5.5.
    • The RDS instance runs RDS High-availability Edition.
    • The RDS instance uses local SSDs.
    Note
    • You can go to the Basic Information page of the RDS instance to view the preceding information about the RDS instance.
    • Physical backup files can be downloaded only when the RDS instances run RDS High-availability Edition. For more information about how to restore the data of an RDS instance that runs RDS Basic Edition, see FAQ.
  2. Tables in the RDS instance are not encrypted by using Transparent Data Encryption (TDE). For more information, see Configure TDE for an ApsaraDB RDS for MySQL instance.
    Important
    • If tables in the RDS instance are encrypted by using TDE, errors occur during the restoration process. We recommend that you decrypt the encrypted tables before you download a backup set. For more information, see Decrypt a table.
    • You can go to the TDE tab of the Data Security page in the ApsaraDB RDS console to view the status of the TDE feature.
  3. The RAM user that you want to use to log on to your RDS instance is granted the permissions to download backup files. For more information about how to grant permissions to a RAM user, see Grant backup file download permissions to a RAM user with read-only permissions.
  4. The self-managed MySQL database and the RDS instance run the same MySQL version, such as MySQL 5.7.
    Note Make sure that no other services are running on top of the MySQL service.
  5. Percona XtraBackup is installed on the server on which the self-managed MySQL database resides.
    • If the RDS instance runs MySQL 5.7, MySQL 5.6, or MySQL 5.5, you must install Percona XtraBackup 2.4. For more information, see Percona XtraBackup 2.4.
    • If the RDS instance runs MySQL 8.0, you must install Percona XtraBackup 8.0. For more information, see the Percona XtraBackup 8.0.
  6. The qpress tool is installed on the server on which the self-managed MySQL database resides. The qpress tool is used for decompression. You can run the following commands to install qpress:
    ## Download the TAR package of the executable file.
wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
## Decompress the downloaded TAR package to obtain the executable file.
tar xvf qpress-11-linux-x64.tar
## Grant the execute permissions on the file to qpress.
chmod 775 qpress
## Copy qpress to the /usr/bin directory.
cp qpress /usr/bin

Step 1: Download the physical backup file that you want to use

  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click Backup and Restoration.
  3. On the page that appears, click the Base Backups tab and then the Data Backup tab.
  4. Find the backup set that you want to download and click Download Instance Backup in the Actions column.
    Note
    • By default, the ApsaraDB RDS console displays the backup sets that were generated over the most recent eight days. If you want to view the backup sets that were generated eight days ago, you must change the default time range.
    • If Download Instance Backup is not displayed, check whether the major engine version or region of the RDS instance meets the requirements that are described in Prerequisites.
  5. In the Download Instance Backup dialog box, you can copy the internal or public URL or click Download to download the backup set.
    Important
    • If you use the internal URL to download the backup set, make sure that the server to which you log on and the RDS instance reside in the same VPC. If the server and the RDS instance reside in VPCs of different regions or if the server resides in the classic network whereas the RDS instance resides in a VPC, you cannot download the backup set by using the internal URL on the server.
    • I have learnt the billing rules for backup file download. is selected by default. If you use the public URL to download the backup set, you are charged for the excess Internet traffic that you consume. For more information,see Billing.
    • The download URLs are valid for only one hour. If the download URLs expire, you can refresh the page to obtain the latest download URLs.
    • We recommend that you do not modify or delete the content of the physical backup file. If you modify or delete the content of the physical backup file, the file may be damaged and cannot be restored. If you must modify the content of the physical backup file, we recommend that you restore the data of the RDS instance from the physical backup file to a self-managed database before you modify the content.
  6. On the Linux server, run the following command to download the physical backup package: 
    wget -c 'http://...' -O test1_qp.xb
    Note
    • You must replace http://... with the URL that you can use to download the physical backup package.
    • test1_qp.xb is the name that is used for the physical backup package after the physical backup package is downloaded. You can change the package name based on your business requirements. However, you must make sure that the extension of the package name remains unchanged. Download screenshot

Step 2: Decompress the physical backup file that you downloaded and restore data from the file that is generated from the decompression

  1. Create a directory to store the files that are obtained from the package on the Linux server. In this example, a directory named /home/mysql/data is created. 
    mkdir /home/mysql/data
  2. Decompress the physical backup package. The command that is used to decompress the physical backup package varies based on the extension of the package name.
    Important
    • You must install Percona XtraBackup and qpress on the server on which the self-managed MySQL database resides before you run the decompression commands. For more information, see Prerequisites. If you do not install Percona XtraBackup and qpress, decompression commands fail to be run.
    • You can replace test1 and /home/mysql/data in the decompression commands with the actual names and save paths of your physical backup package.
    Table 1. Decompression commands
    File name extensionDecompression command
    .tar.gz
    tar -izxvf test1.tar.gz -C /home/mysql/data
    .xb.gz
    gzip -d -c test1.xb.gz | xbstream -x -v -C /home/mysql/data
    _qp.xb
    ## Step 1: Decompress the package.
cat test1_qp.xb | xbstream -x -v -C /home/mysql/data

## Step 2: Decompress the package again.
### If the RDS instance runs MySQL 5.6 or MySQL 5.7, run the following command:
innobackupex --decompress --remove-original /home/mysql/data
### If the RDS instance runs MySQL 8.0, run the following command:
xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
    Note
    • If the can't change to dir to xx( errorcode:no such file or directory) error message is displayed when you run the preceding cat command, check whether the directory in which you want to store the decompressed file exists or is correct.
    • If an error message indicating that innobackupex is not found is displayed when you run the preceding decompression command, check whether Percona XtraBackup is installed on the server on which the self-managed MySQL database resides. For more information, see Prerequisites.
    • If the sh: qpress: command not found error message is displayed when you run the preceding decompression command, check whether qpress is installed on the server on which the self-managed MySQL database resides. For more information, see Prerequisites.
    _xb.qp
    qpress -do  test1_xb.qp  | xbstream -x -v -C /home/mysql/data
  3. Run the following command to query the files that are obtained from the physical backup package: 
    ls -l /home/mysql/data
    The system returns the following information. The information in blue indicates the databases whose data is contained in the physical backup package. View the files that are obtained from the package
  4. Restore the data of the files that are obtained from the physical backup package to the self-managed MySQL database. 
    ## MySQL 5.6/5.7
innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data

## MySQL 8.0
## Run the following command to prepare the Percona XtraBackup-based data backup.
xtrabackup --prepare --target-dir=/home/mysql/data
## Run the following command to restore the data of the files:
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data
    Table 2. Parameters
    ParameterDescription
    --defaults-fileSpecify the directory of a file to configure the default MySQL options.

    After the physical backup package is decompressed, a file named backup-my.cnf is obtained. We recommend that you set this parameter to the directory of the backup-my.cnf file.

    --apply-logApply the transaction log file named xtrabackup_logfile in the directory to the directory in which the physical backup package is decompressed.
    --preparePrepare the data backup before Percona XtraBackup is used to back up data. This parameter affects the data restoration.
    --datadirThe directory in which the data of the source database is stored. The value of this parameter varies based on the location of the MySQL service.
    Note The value of this parameter is the same as the value of the --datadir parameter in the command that is used to start the MySQL process in Step 3.
    --target-dirThe directory in which the physical backup package is decompressed.
    If the system displays the following or similar information, the data is restored to the self-managed MySQL database.Data restoration successful
    Common restoration errors:
    • If the system returns xtrabackup: Unknown error 3613, update Percona XtraBackup to the latest version and try again.
    • If the system returns the following error, run the rm -rf /var/lib/mysql/* command to delete all files from the directory. Then, run the chown -R mysql:mysql /var/lib/mysql command to modify the permissions on the directory. Message indicating that the directory is not empty
    • If the system returns the following error, check whether the tables in the RDS instance are encrypted by using TDE. For more information, see Prerequisites. Data restoration failed
    Note The Percona XtraBackup version that you use must match the MySQL version of the RDS instance:

Step 3: Run MySQL

  1. Modify the backup-my.cnf file to ensure version compatibility.
    1. Run the following command to open the backup-my.cnf file in text mode: 
      vi /home/mysql/data/backup-my.cnf
    2. Add the following parameter setting to the file:
      lower_case_table_names=1
    3. Comment out the following parameters that are not supported by the self-managed SQL database. If you do not comment out these parameters, an error is reported.
      #innodb_log_checksum_algorithm
#innodb_fast_checksum
#innodb_log_block_size
#innodb_doublewrite_file
#innodb_encrypt_algorithm
#rds_encrypt_data
#redo_log_version
#master_key_id
#server_uuid
      Note The MyISAM storage engine is incompatible with the InnoDB storage engine that is used by ApsaraDB RDS. If the self-managed MySQL database runs MyISAM, you must comment out the following parameters and add the skip-grant-tables parameter:
      #innodb_log_checksum_algorithm=strict_crc32
#redo_log_version=1
skip-grant-tables
    4. Press Esc, enter :wq, and then press Enter to save the file.
  2. Run the following command to change the owner of the file to a user who has the permissions to manage the self-managed MySQL database: 
    chown -R mysql:mysql /home/mysql/data
  3. Run the following command to run MySQL: 
    mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/var/lib/mysql &
    Note
    • The following issues related to the password of the root user may occur:
      • If the RDS instance runs MySQL 5.5 or MySQL 5.6, you must reset the password of the root user of the RDS instance. For more information, see open source MySQL documentation.
      • If the RDS instance runs MySQL 5.7 or MySQL 8.0, the password of the root user of the RDS instance is the same as the password of the root user of the self-managed MySQL database.
    • If an error is reported when you start MySQL, you can resolve the error by changing the storage engine. For more information, see FAQ.

    Common errors

    AppArmor is a built-in security program in an Ubuntu operating system. If you use an Ubuntu operating system, the error that is shown in the following figure may be returned. In this case, you must run the apt install -y apparmor-utils and aa-complain /usr/sbin/mysqld commands to modify the settings of the AppArmor security program.

    Error details
  4. Run the following command to log on to the self-managed MySQL database and verify that MySQL is running: 
    mysql -u<Username of the account that is used to connect to the RDS instance> -p<Password of the preceding account>
    Note
    • If the Access denied for user 'XXX' error message is returned when you run this command, you need to check the username or password of the account that is used to connect to the RDS instance.
    • This command is used to check whether the restoration succeeds. If you only want to query data in a table, you can use an account that has permissions on the table to run this command. You do not need to use a privileged account.
    • If you forget the username or password of the account, you can specify the --skip-grant-tables parameter when you run the start command in Step 3 to start the MySQL process. After the MySQL process is started, authentication is skipped, and you can log on to the database without the username and password. After you log on to the database, you can change the username and password of the account.
    • You can also use a third-party tool, client, or command line to connect to the database. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
  5. Optional. Execute the SHOW DATABASES statement to check whether the database is restored. Successfully started

FAQ

  • How do I restore the data of my RDS instance over a specific time range to a self-managed MySQL database?

    You can download the log backup file that is generated over the specified time range in the ApsaraDB RDS console. Then, you can use the log backup file to restore the data of your RDS instance to a self-managed MySQL database. For more information, see Download the backup files of an ApsaraDB RDS for MySQL instance.

  • After I restore the data of my RDS instance to a self-managed MySQL database by using a physical backup file, the time that is indicated by the time field of the data and the local time of the server on which the self-managed MySQL database is deployed belong to different time zones. Why? How do I make sure that the time that is indicated by the time field of the data and the local time belong to the same time zone?

    If the time zone of the self-managed MySQL database is different from the time zone of your RDS instance, you must modify the value of the time_zone parameter for the self-managed MySQL database to keep it consistent with the value of the time_zone parameter for your RDS instance. If the time_zone parameter of your RDS instance is set to system, you must query the region in which the RDS instance resides and set the time_zone parameter of the self-managed MySQL database to the time zone of the region.

  • After a restoration task is completed, what do I do if the self-managed MySQL database cannot be started or the error 1105 Unknown error error message is displayed when I use the self-managed MySQL database?

    Execute the following SQL statements to change the storage engine:

    use mysql;
alter table proc engine=myisam;
alter table event engine=myisam;
alter table func engine=myisam;
  • I can use the data backup files that I downloaded to restore the data of my RDS instance to a self-managed MySQL database. Which other methods are available?

    You can use Data Transmission Service (DTS) to migrate data from your RDS instance to the self-managed MySQL database. For more information, see Migrate data from an ApsaraDB RDS for MySQL instance to a self-managed MySQL database.

  • When I download data backup files, why does the system report errors?

    When you run the wget -c '<URL to download the data backup files over the Internet>' -O <Custom file name>.tar.gz command, you must enclose the URL in a pair of single quotation marks ('). This way, the system can identify the URL, and related errors are prevented.

  • What do I do if the backup file that I downloaded contains TDE-encrypted data?

    You cannot use the downloaded backup file to restore data. If an encrypted table exists in an RDS instance, an error occurs during data restoration. In this case, you can decrypt the encrypted table on the RDS instance based on Prerequisites. For more information about how to decrypt an encrypted table, see Decrypt a table.

  • What do I do if the system reports an error when I decompress the data backup file that I downloaded?
    Perform the following operations to identify the causes of the error:
    1. Check whether the data backup file is a physical backup file.
    2. Check whether the data backup file is saved based on a valid file name extension. Valid file name extensions are .tar.gz, .xb.gz, and _qp.xb.
    3. Check whether the command that you use is supported for the format of the data backup file. For more information, see Step 2 in Step 2: Decompress the physical backup file that you downloaded and restore data from the file that is generated from the decompression. You must replace test1 and /home/mysql/data in the commands with the actual file name and directory.
    The following section describes the common errors and solutions when you decompress a package:
    • Error: The file name extension is _qp.xb. When you run the cat command, the can't change to dir to xx( errorcode:no such file or directory) error message is displayed.

      Solution: The possible cause is that the file name or directory in the command is invalid. In this case, replace test1 and /home/mysql/data in the command with the actual file name and directory.

    • Error: The file name extension is _qp.xb. When you run the decompression command, an error message indicating that innobackupex is not found is displayed.

      Solution: Make sure that Percona XtraBackup is installed on the server on which the self-managed database resides. For more information, see Prerequisites.

    • Error: When you run the decompression command, the sh: qpress: command not found error message is displayed.

      Solution: Make sure that qpress is installed on the server on which the self-managed database resides. For more information, see Prerequisites.

  • How do I restore or migrate the data of my RDS instance that runs RDS Basic Edition?

    RDS instances that run RDS Basic Edition support only snapshot backups. If your RDS instance runs RDS Basic Edition, use one of the following two methods to restore or migrate the data:

  • Can I restore the data of my RDS instance to another RDS instance by using a data backup file that I downloaded?

    No, you cannot restore the data of your RDS instance to another RDS instance by using a data backup file that you downloaded. We recommend that you use DTS to migrate data from an RDS instance to another RDS instance. For more information, see Migrate data between ApsaraDB RDS instances.

References