ApsaraDB RDS:Restore the data of an ApsaraDB RDS for MySQL instance to a self-managed MySQL database from a physical backup file

• Why is an error reported when I download a data file?

  If you run the wget -c 'https://****.bak.rds.aliyuncs.com/****_xb.qp?****' -O test_xb.qp command to download a data file, you must use a pair of single quotation marks (') to enclose the download URL. This way, the application can identify the download URL.

When you run the following command, replace test_xb.qp with the name of the backup file that you saved and replace /var/mysql_bkdata/ with the backup decompression directory that you created.

### MySQL 8.0
qpress -do  test_xb.qp | /u01/xtrabackup80/bin/xbstream -x -v -C /var/mysql_bkdata/

### MySQL 5.5/5.6/5.7
qpress -do  test_xb.qp | xbstream -x -v -C /var/mysql_bkdata/

When you run the following commands, replace test_qp.xb with the name of the backup file that you saved and replace /var/mysql_bkdata/ with the backup decompression directory that you created.

## Step 1: Parse the file.
cat test_qp.xb | xbstream -x -v -C /var/mysql_bkdata/

## Step 2: Decompress the file.
### MySQL 5.5/5.6/5.7
innobackupex --decompress --remove-original /var/mysql_bkdata/

### MySQL 8.0
/u01/xtrabackup80/bin/xtrabackup --decompress --remove-original --target-dir=/var/mysql_bkdata/

When you run the following command, replace test.tar.gz with the name of the backup file that you saved and replace /var/mysql_bkdata/ with the backup decompression directory that you created.

tar -izxvf test.tar.gz -C /var/mysql_bkdata/

When you run the following command, replace test.xb.gz with the name of the backup file that you saved and replace /var/mysql_bkdata/ with the backup decompression directory that you created.

### MySQL 8.0
gzip -d -c test.xb.gz | /u01/xtrabackup80/bin/xbstream -x -v -C /var/mysql_bkdata/

### MySQL 5.5/5.6/5.7
gzip -d -c test.xb.gz | xbstream -x -v -C /var/mysql_bkdata/

• What do I do if the system reports an error when I decompress the backup file that I downloaded?

  Perform the following operations to identify the causes of the error and resolve the error:

  Check whether the backup file is a physical backup file.

  Check whether the backup file has one of the following valid file name extensions: xb.qp, .tar.gz, .xb.gz, and _qp.xb.

  The decompression commands vary based on the file name extension of the backup file.

  The following error messages may be displayed during the decompression:

  • The sh: qpress: command not found error message is displayed.

    Solution: Check whether qpress is installed on the server on which your self-managed MySQL database resides. For more information, see Prerequisites.

  • An error message that indicates that innobackupex is not found is displayed. This error is reported if the file name extension of the backup file is _qp.xb.

    Solution: Check whether Percona XtraBackup is installed on the server on which your self-managed MySQL database resides. For more information, see Prerequisites.

  • 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. This error is reported if the file name extension of the backup file is _qp.xb.

    Solution: Check whether the directory in which you want to store the decompressed file or the file name is correct.

• What do I do if the xtrabackup: Unknown error 3613 error message is displayed?

  Update Percona XtraBackup to the latest version and try again.

• What do I do if the Original data directory /var/mysql_newdata is not empty! error message is displayed?

  Run the sudo rm -rf /var/mysql_newdata/* command to clear the files in the folder. Then, perform the restoration operations again.

• What do I do if the InnodDB: Encryption information in datafile: ./xxx.ibd can't be decrypted, please check if a keyring plugin is loaded and initialized successfully. error message is displayed?

  Check whether TDE is enabled for your RDS instance.

  • If TDE is enabled, check whether a TDE-encrypted table exists. If a TDE-encrypted table exists, decrypt the table and then restore the data based on the instructions provided in this topic. For more information, see Prerequisites and Configure TDE.

  • If TDE is disabled, make sure that the required version of Percona XtraBackup is installed. For more information, see Preparations.

• What do I do if a downloaded backup file contains TDE-encrypted data and an error occurs when I restore the data?

  You cannot use the downloaded backup file to restore data. If an encrypted table exists in an RDS instance, an error occurs when you restore data. To resolve the error, decrypt the table and then restore the data based on the instructions provided in this topic. For more information, see Prerequisites and Configure TDE.

• What do I do if the innobackupex: File 'undo001' not found (Errcode: 2 - No Such file or directory) error message is displayed during data restoration?

  The undo file is a system file. The file varies based on the database version. To resolve the issue, check whether the self-managed MySQL database runs the same database version as the RDS instance.

• Why is the mysqld: [ERROR] Failed to open required defaults file: /etc/my.cnf error message displayed in an Ubuntu operating system? What do I do?

  The error occurs due to the security program AppArmor is provided in an Ubuntu operating system. If you use an Ubuntu operating system, the error message is displayed. In this case, you must run the apt install -y apparmor-utils and aa-complain /usr/sbin/mysqld commands to modify the settings of AppArmor.

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

  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;

  Note

  If the ERROR 1067 (42000): Invalid default value for 'modified' error message is displayed when you execute the preceding statements, execute the SET SQL_MODE='ALLOW_INVALID_DATES'; statement first.

• What is the password of the root user after data is restored?

  You can use one of the following methods to obtain the password:

  • 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 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.

• What do I do if the InnodDB: Assertion failure in thread 140xxx in file page0zip.icne xxx error message is displayed during startup?

  The error may occur due to insufficient disk space. You can resize the disk of the server on which the self-managed MySQL database resides and try again.

• What do I do if the [ERROR] Failed to open the relay log xxx, [ERROR] Slave: Failed to initialize the master info xxx, and [ERROR] Failed to create or recover replication info repositories. error messages are displayed during startup?

  The RDS instance works in high availability (HA) mode, but the self-managed MySQL database does not involve the primary and secondary nodes. This does not affect the database startup. You can ignore the error messages.

• After I restore the data of my RDS instance from a physical backup file to a self-managed MySQL database, 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 resides 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 ensure consistency 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.

• What do I do if the Access denied for user 'XXX' error message is displayed when I connect to my self-managed MySQL database?

  Check whether the username and password of the account that is used to connect to the RDS instance is correct. The account must be created on the RDS instance.

• What do I do if I forget the password when I connect to a self-managed MySQL database?

  If you forget the username or password of your account, configure the --skip-grant-tables parameter when you run the command to start the MySQL process. After the process starts, the permission check is ignored, and you can log on to the database in password-free mode. After you log on to the database, you can change the username and password of the account.

• What do I do if I can view only system databases or some databases after I connect to my self-managed database?

  Restart the self-managed database. Then, use the root account or the privileged account of the original RDS instance to connect to the self-managed database.