Migrate RDS for MySQL data to the local MySQL database

Last Updated: Dec 12, 2017

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

  1. 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 Downloading Backup Data.

    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.

    1. cd /home/mysql/
  3. Decompress the backup file.

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

    1. cd filename/
    2. ll

    The system displays the following information, in which db0dz1rv11f44yg2, mysql, and test are the databases in RDS:

    1. -rw-r--r-- 1 root root 269 Aug 19 18:15 backup-my.cnf
    2. drwxr-xr-x 2 root root 4096 Aug 21 10:31 db0dz1rv11f44yg2
    3. -rw-rw---- 1 root root 209715200 Aug 7 10:44 ibdata1
    4. drwxr-xr-x 2 root root 4096 Aug 21 10:31 mysql
    5. drwxr-xr-x 2 root root 4096 Aug 21 10:31 test
    6. -rw-r--r-- 1 root root 10 Aug 19 18:15 xtrabackup_binary
    7. -rw-r--r-- 1 root root 23 Aug 19 18:15 xtrabackup_binlog_info
    8. -rw-r--r-- 1 root root 77 Aug 19 18:15 xtrabackup_checkpoints
    9. -rw-r--r-- 1 root root 2560 Aug 19 18:15 xtrabackup_logfile
    10. -rw-r--r-- 1 root root 72 Aug 19 18:15 xtrabackup_slave_info
  5. Recover the data file.

    1. innobackupex --defaults-file=./backup-my.cnf --apply-log ./

    Data is successfully recovered when the system displays innobackupex: completed OK!.

  6. Modify the configuration file.

    Comment out innodb_fast_checksum, innodb_page_size, and innodb_log_block_size in the decompressed file backup-my.cnf, and add datadir=/home/mysql, as shown in the following example.

    1. # This MySQL options file was generated by innobackupex-1.5.1.
    2. # The MySQL Server
    3. [mysqld]
    4. innodb_data_file_path=ibdata1:200M:autoextend
    5. innodb_log_files_in_group=2
    6. innodb_log_file_size=524288000
    7. #innodb_fast_checksum=0
    8. #innodb_page_size=16364
    9. #innodb_log_block_size=512
    10. datadir=/home/mysql/
  7. Reinstall MySQL and obtain the root permission of the database.

    1. rm -rf mysql
    2. mysql_install_db --user=mysql --datadir=/home/mysql/

    MySQL is successfully reinstalled when the system displays the following information:

    1. Installing MySQL system table...
    2. OK
    3. Filling help table...
    4. OK
  8. Modify the file owner.

    1. chown -R mysql:mysql /home/mysql/
  9. Start the MySQL process.

    1. mysqld_safe --defaults-file=/home/mysql/backup-my.cnf &
  10. Log on to the database from a client.

    1. mysql u root p
  11. Verify database integrity.

    1. show databases;

    The database is successfully recovered when the system displays the following information:

    1. +--------------------+
    2. | Database |
    3. +--------------------+
    4. | information_schema |
    5. | db0dz1rv11f44yg2 |
    6. | mysql |
    7. | performance_schema |
    8. | test |
    9. +--------------------+

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 Downloading Backup Data.

    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.

    1. cd /home/mysql/
  3. Decompress the backup file.

    1. tar vizxf filename.tar.gz

    filename.tar.gz indicates the name of the backup file.

  4. Decompress the SQL file.

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

    1. mysql -u userName -p -h hostName -P port dbName < filename.sql

    filename.sql indicates the name of the decompressed SQL file.

Thank you! We've received your feedback.