1028 Ali RDS How to restore a cloud database MySQL backup file to a self-built database

Reference https://help.aliyun.com/knowledge_detail/41817.html

Restore the backup files of the cloud database MySQL to the self-built database

Update Time: 2017-07-27 14:52:13

The cloud database MySQL version uses the open source software Percona Xtrabackup to back up the database, so you can use the software to restore the backup files of the cloud database MySQL to the built-in database, this article describes the detailed procedures.

For information on how to back up the MySQL version of cloud database, see Backing up RDS data.

Pre-conditions

• The local MySQL database is installed on a 64-bit Linux system and is the same version as the MySQL version of the cloud database.

  description : Due to software limitations, it is currently only supported to restore the backup files of the cloud database MySQL to the self-built MySQL database installed in the Linux system. However, the recovered data files under Linux can be used normally in Windows and Linux operating systems.

• The Data Recovery Tool Percona Xtrabackup 2.2.9 or later is installed in the operating system. If not installed, please download it from Percona Xtrabackup website, please refer to the official documentation for installation instructions.

• The operating system has the data backup file installed to unzip the tool rds_backup_extract.sh. If it is not installed, please click Download.

Operation Steps

2. To get a data backup file, follow these steps.

   1. To log on to the RDS management console.

   2. Selects the region of the destination instance.

   3. Click the ID of the target instance to go to the basic information page.

   4. In the left navigation bar, select Backup Recovery and go to the Backup Recovery page.

   5. Select the Data Backup tab.

   6. Select a time range for the query, and then click Query .

   7. In the Data backup list, locate the data backup that you want to download, and click its corresponding download , as shown in.

   8. In the instance backup file Download window, click Copy Extranet address to get the data backup file extranet, as shown in.

4. To download the data backup file, the following steps are described below.

   1. Log in to ECS on cloud server.

   2. Execute the following command to download the data backup file.

      1. wget -c ‘<数据备份文件外网>‘ -O <自定义文件名>.tar.gz

      Parameter description:

      • -C: Enable the breakpoint continuation mode.

      • -O: Saves the downloaded results as a specified file (the recommended file name is included in the URL).

        Note : If the prompt shows 100% progress, the file download is complete.

5. To restore the downloaded data backup to the local MySQL database, follow the steps below.

   2. To unzip the downloaded data backup file by executing the following command.

      1. bash rds_backup_extract.sh -f < data backup file name >. Tar. GZ -c /home/ mysql/data

      Parameter description:

      • - F: Specifies the backup set file to decompress.

      • -C: Specifies the directory to which the files will be extracted. Optional parameters, if not specified, unzip to the current directory.

   4. Executes the following command to query the files generated after decompression.

      1. ls -l /home/mysql/ data
      After successful execution of the

      Command, the system returns the following results, where the blue font is the database that the RDS instance contains when the backup file was generated.

   6. Perform the following command to restore the extracted backup files.

      1. innobackupex ---file=/home//data/backup-< Span class= "KWD" >my. CNF --apply-log /home/mysql/data

      If the system returns similar results as follows, Indicates that the backup file was successfully restored to the local database.

7. To avoid version problems, you need to modify the BACKUP-MY.CNF parameter, following the steps below.

   1. Executes the following command to edit the backup-my.cnf file in text mode.

      1. vi /home< Span class= "pun" >/mysql/data/ backup-my. CNF

   2. Execute the following command, commenting out the following parameters.

      1. #innodb_fast_checksum
      2. #innodb_page_size
      3. #innodb_log_ Block_size

   3. Press the ESC key, and then enter : Wq to save and close the editor.

9. Executes the following command, modifies the owner of the file, and determines that the file belongs to the MySQL user.

   1. Chown -< Span class= "PLN" >r mysql:mysql /home/mysql/data

11. Execute the following command to start the MySQL process.

    1. Mysqld_safe -- defaults-file=/home/ mysql/data/backup< Span class= "pun" >-my.--user=mysql --< Span class= "PLN" >datadir=/home/mysql/< Span class= "PLN" >data &

13. Execute the following command to log in to the MySQL database to verify that the process started successfully.

    1. mysql -uroot

    If the system returns the following result and the process starts successfully, the parameter comment and the modified file owner are successfully executed.

14. When the restore is complete, the user created in the RDS instance is not included in table mysql.user and needs to be new. Before creating a new user, execute the following command.

    1. Delete FromMysql.Dbwhere User<> ' root ' Span class= "KWD" >and Char_length (user0; from Mysql.where User<> ' root ' and Char_length ( user) >0;

1028 Ali RDS How to restore a backup file of a cloud database MySQL to a self-built database