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

• Q: Why does an error occur when I download a data file?

  A: When you use the wget -c 'https://****.bak.rds.aliyuncs.com/****_xb.qp?****' -O test_xb.qp command, enclose the download address in single quotation marks (') to help the program correctly identify the address and prevent errors.

When you run the following decompression command, replace test_xb.qp with your backup file name and /var/mysql_bkdata/ with the backup decompression path 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 execute the following decompression command, replace test_qp.xb with the name of your backup file and /var/mysql_bkdata/ with the decompression path that you created.

## Step 1: Unpack 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 decompression command, replace test.tar.gz with your backup filename and /var/mysql_bkdata/ with the backup decompression path that you created.

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

When you run the following decompression command, replace test.xb.gz with the name of your backup file and /var/mysql_bkdata/ with the decompression path 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/

• Q: What do I do if an error occurs when I decompress a downloaded backup file?

  A: Check the following items to troubleshoot the error:

  Confirm that the downloaded file is a physical backup file.

  Confirm that the saved compressed file has the correct file name suffix (the suffix must be the one from the URL, such as xb.qp, .tar.gz, .xb.gz, or _qp.xb).

  Use the correct decompression command for the file format.

  Common errors:

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

    Solution: See Prerequisites to make sure that the qpress tool is installed on your self-managed database server.

  • When decompressing an xbstream package with the _qp.xb extension, the innobackupex not found error occurs.

    Solution: See Prerequisites to make sure that Percona XtraBackup is installed on your self-managed database server.

  • The error can't change to dir to xx( errorcode:no such file or directory) is reported when you use the cat command to unpack an xbstream package file that has a _qp.xb suffix.

    Solution: This error indicates an incorrect file name or path. Verify the file name and path in your command.

• Q: The system returns the error xtrabackup: Unknown error 3613. How can I resolve this?

  A: Update Percona XtraBackup to the latest version and try again.

• Q: The system returns the error Original data directory /var/mysql_newdata is not empty!. What should I do?

  A: Use the sudo rm -rf /var/mysql_newdata/* command to clear the files in the folder, and then retry the restore operation.

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

  Check whether TDE is enabled for your instance:

  • If TDE is enabled, check for encrypted tables. As described in Prerequisites, decrypt the encrypted tables by following the instructions in Configure Transparent Data Encryption (TDE), and then follow the instructions in this topic to restore the data.

  • If TDE is not enabled, make sure you have the correct version of Percona XtraBackup installed as described in Prerequisites.

• Q: A downloaded backup file contains TDE-encrypted data, which causes the data restoration to fail. What is the solution?

  A: You cannot restore data from a downloaded encrypted backup file. Encrypted tables in an RDS instance cause restoration errors. As described in Prerequisites, decrypt the tables by following the instructions in Configure Transparent Data Encryption (TDE), create a new backup, and then restore the data again.

• Q: How do I resolve the innobackupex: File 'undo001' not found (Errcode: 2 - No Such file or directory) error that occurs during a restore?

  A: The undo file is a system file that varies across database versions. Check whether your self-managed database has the same version as the ApsaraDB RDS for MySQL database.

• Q: What causes the error mysqld: [ERROR] Failed to open required defaults file: /etc/my.cnf on an Ubuntu operating system, and how can I resolve it?

  A: This error is caused by the Ubuntu security program AppArmor. Use the apt install -y apparmor-utils and aa-complain /usr/sbin/mysqld commands to modify the AppArmor settings.

• Q: After a restore is complete, what should you do if a self-managed database fails to start or you encounter the error 1105 Unknown error during use?

  A: Run the following SQL statements to change the database storage engine:

  USE mysql;
  ALTER TABLE proc engine=myisam;
  ALTER TABLE event engine=myisam;
  ALTER TABLE func engine=myisam;

  Note

  If you receive the error ERROR 1067 (42000): Invalid default value for 'modified', first execute the SET SQL_MODE='ALLOW_INVALID_DATES'; statement.

• Q: What is the root password after the database is restored?

  A: The method to obtain the root password varies by version:

  • If your instance runs MySQL 5.7 or 8.0, the root password is the same as the root password of your self-managed database.

  • If your instance runs MySQL 5.5 or 5.6, you must reset the root password before you can use the database. For more information, see the official documentation.

• Q: The error InnodDB: Assertion failure in thread 140xxx in file page0zip.ic xxx occurs during startup. How can I resolve this?

  A: This error may be caused by insufficient disk space. Try to expand the disk of your self-managed database server and then try again.

• Q: How do I resolve the following errors that occur during startup: [ERROR] Failed to open the relay log xxx, [ERROR] Slave: Failed to initialize the master info xxx, or [ERROR] Failed to create or recover replication info repositories.?

  A: These errors occur because the RDS instance is configured for high availability (HA), but the local self-managed database does not use primary and secondary nodes. You can safely ignore these errors as they do not affect startup.

• Q: How can I resolve the [ERROR] Data Dictionary initialization failed error that occurs during startup?

  A: This error may be caused by an incompatible operating system. The underlying operating system of ApsaraDB RDS is Linux. We recommend that you use a Linux-based operating system to restore the database, as using a different operating system may cause version compatibility issues.

• Q: The sudo systemctl start mysqld command fails to start the local MySQL service. What should I do?

  A: This may be caused by SELinux being in Enforcing mode in Linux. You can run the getenforce command to check the current status of SELinux.

  • If this is a development or testing environment, we recommend that you change the SELinux mode to Permissive mode. Then, try to start MySQL by using sudo systemctl start mysqld.

  • For production environments, we recommend that you check the SELinux logs to determine the specific reason for the startup failure and then fix the SELinux policy based on this information.

• Q: After restoration, why are the time values in my data inconsistent with the local time, and how do I fix it?

  A: If the time zone of the self-managed database is different from that of the RDS instance, you must modify the time_zone parameter of the self-managed database to match that of the RDS instance. If the time_zone parameter of the RDS instance is set to system, check the region where the RDS instance is located and set the time_zone parameter of the self-managed database to the time zone of that region.

• Q: I receive the error Access denied for user 'XXX' when connecting to a self-managed database. How can I resolve this?

  A: Verify that you are using the correct account and password from the original ApsaraDB RDS for MySQL instance.

• Q: I forgot my password when connecting to the self-managed database. What should I do?

  A: If you forget your account or password, start the MySQL process with the --skip-grant-tables parameter. This action bypasses permission checks upon startup, allowing you to log in to the database without an account or password. Please note, using the --skip-grant-tables parameter is a security risk. You should change your account and password immediately after you log in.

• Q: After connecting to the self-managed database, I can see only system databases or some of the databases. How do I handle this?

  A: Restart the database, and then connect using the root account or a high-privilege account from the original RDS instance to view the databases.