RDS for MySQL supports the migration of cloud data to the local database by using physical and logical backup files.

Export using a physical backup file

Background information

Due to software restrictions, data recovery is supported only in Linux currently. If you want to recover data to Windows, you need first of all recover data to Linux and then migrate the data to Windows.

Prerequisites

RDS adopts the open source software Percona XtraBackup 2.0.6 to perform full physical backup on the MySQL database. You must download the software for data recovery. Visit the official website (http://www.percona.com/) of Percona XtraBackup and download the version compatible with your operating system. For example: Download the RHEL6/x86_64 version and run the rpm command to install it.

sudo rpm -ivh percona-xtrabackup-2.0.6-521.rhel6.x86_64.rpm
Procedure

This example assumes that the local server runs the RHEL6/x64 system and the path to the backup file is /home/mysql/.

  1. Download the RDS physical backup file and upload the file to the target server. For more information about how to obtain the backup file, see Download RDS data and log backup. If the target server can access the source instance, you can use wget "url" to download the backup file. url indicates the backup file download address.
  2. Switch to the backup file path.
    cd/home/mysql/
  3. Decompress the backup file.
    tar vizxf filename.tar.gz
    filename.tar.gz indicates the name of the backup file.
  4. Check whether the databases contained in the decompressed file are correct.
    cd filename/
    ll
    The system displays the following information, in which db0dz1rv11f44yg2, mysql, and test are the databases in RDS:
    -rw-r--r-- 1 root root       269 Aug 19 18:15 backup-my.cnf
    drwxr-xr-x 2 root root      4096 Aug 21 10:31 db0dz1rv11f44yg2
    -rw-rw---- 1 root root 209715200 Aug  7 10:44 ibdata1
    drwxr-xr-x 2 root root      4096 Aug 21 10:31 mysql
    drwxr-xr-x 2 root root      4096 Aug 21 10:31 test
    -rw-r--r-- 1 root root        10 Aug 19 18:15 xtrabackup_binary
    -rw-r--r-- 1 root root        23 Aug 19 18:15 xtrabackup_binlog_info
    -rw-r--r-- 1 root root        77 Aug 19 18:15 xtrabackup_checkpoints
    -rw-r--r-- 1 root root      2560 Aug 19 18:15 xtrabackup_logfile
    -rw-r--r-- 1 root root        72 Aug 19 18:15 xtrabackup_slave_info
  5. Recover the data file.
    innobackupex --defaults-file=./backup-my.cnf --apply-log ./
    Data is successfully recovered when the system displays innobackupex: completed OK!
  6. Modify the configuration file. In the backup-my.cnf file, comment out innodb_fast_checksum, innodb_page_size, and innodb_log_block_size, and add datadir=/home/mysql, as shown in the following example.
    # This MySQL options file was generated by innobackupex-1.5.1.
    # The MySQL Server
    [mysqld]
    innodb_data_file_path=ibdata1:200M:autoextend
    innodb_log_files_in_group=2
    innodb_log_file_size=524288000
    #innodb_fast_checksum=0
    #innodb_page_size=16364
    #innodb_log_block_size=512
    datadir=/home/mysql/
  7. Reinstall MySQL and obtain the root permission of the database.
    rm -rf mysql
    mysql_install_db --user=mysql --datadir=/home/mysql/
    If the system displays the following information, the mysql system table is successfully reinstalled.
    Installing MySQL system table...
    OK
    Filling help table...
    OK
  8. Modify the file owner.
    chown -R mysql:mysql /home/mysql/
  9. Start the mysqld process.
    mysqld_safe --defaults-file=/home/mysql/backup-my.cnf &
  10. Log on to the database from a client.
    mysql–u root –p
  11. Verify database integrity.
    show databases;
    The database is successfully recovered when the system displays the following information:
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db0dz1rv11f44yg2   |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+

Export using a logical backup file

This example assumes that the local server runs the RHEL6/x64 system and the path to the backup file is /home/mysql/

Procedure
  1. Download the RDS logical backup file and upload the file to the target server. For more information about how to obtain the backup file, see Download RDS data and log backup. If the target server can access the source instance, you can use wegt "url" to download the backup file. url indicates the backup file download address.
  2. Switch to the backup file path.
    cd /home/mysql/
  3. Decompress the backup file.
    tar vizxf filename.tar.gz
    filename.tar.gz indicates the name of the backup file.
  4. Decompress the SQL file.
    gunzip filename.sql.gz
    filename.sql.gz indicates the name of the compressed SQL file.
  5. Perform logical import to import data to the target database.
    mysql -u userName -p -h hostName -P port dbName < filename.sql
    filename.sql indicates the name of the decompressed SQL file.