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

Export based on 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, first you need recover data to Linux and then migrate the data to Windows.

Prerequisites
Data restoration tool Percona XtraBackup has been installed in the Linux system.
  • For MySQL 5.6 and earlier version, install Percona XtraBackup 2.3.
  • For MySQL 5.7, install Percona XtraBackup 2.4.
For installation instructions, see Percona XtraBackup 2.3 and Percona XtraBackup 2.4.
Procedure

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

  1. Download the 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 downloading 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 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 based on a logical backup file

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

Procedure
  1. Download 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 downloading 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.