This topic describes how to migrate data from RDS for MySQL to an on-premises MySQL database through a physical or logical backup file.

Migrate data by using a physical backup file

Background information

Due to software restrictions, you can restore data only in a Linux operating system. If you want to restore data to a Windows operating system, then you must first restore data to Linux and then migrate the data to Windows.

Prerequisites

Percona XtraBackup has been installed in your operating system. MySQL 5.6 and earlier versions require Percona XtraBackup 2.3. MySQL 5.7 requires Percona XtraBackup 2.4. You can download and install Percona XtraBackup from its official website. For more information, see Percona XtraBackup 2.3 and Percona XtraBackup 2.4 at the official website.

Procedure

This example assumes that your on-premises server runs the RHEL6/x64 operating system and the path for storing the physical backup file is /home/mysql/.

  1. Download the physical backup file of the source RDS for MySQL instance and upload the file to the destination server. For more information about how to obtain the physical backup file, see Download the data backup files and log backup files of an RDS for MySQL instance. If the destination server can access the source RDS for MySQL instance, then you can run the wget "url" command to download the physical backup file. url in the command indicates the URL from which you can download the physical backup file.
  2. Go to the path where the physical backup file is saved.
    cd /home/mysql/
  3. Decompress the physical backup file.
    tar vizxf filename.tar.gz
    filename.tar.gz indicates the name of the physical backup file.
  4. Check whether the databases contained in the physical backup file after compression are correct.
     cd filename/ll
    The system displays the following information, where db0dz1rv11f44yg2, mysql, and test are the databases in the source RDS for MySQL instance:
    -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 . /
    When the system displays innobackupex: completed OK!, the data is restored.
  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 to the file.
    # 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 to obtain the root permissions.
    rm -rf mysql
    mysql_install_db --user=mysql --datadir=/home/mysql/
    When the system displays the following information, MySQL is reinstalled:
    Installing MySQL system table...
    OK
    Filling help table...
    OK
  8. Modify the file owner.
    chown -R mysql:mysql /home/mysql/
  9. Start the MySQL 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 the database integrity.
    show databases;
    When the system displays the following information, the database is restored:
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db0dz1rv11f44yg2   |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+

Migrate data by using a logical backup file

This example assumes that your on-premises server runs the RHEL6/x64 operating system and the path for storing the logical backup file is /home/mysql/.

Procedure
  1. Download the logical backup file of the source RDS for MySQL instance and upload the file to the destination server. For more information about how to obtain the logical backup file, see Download the data backup files and log backup files of an RDS for MySQL instance. If the destination server can access the source RDS for MySQL instance, you can run the wegt "url" command to download the logical backup file. url in the command indicates the URL from which you can download the logical backup file.
  2. Go to the path where the logical backup file is saved.
    cd /home/mysql/
  3. Decompress the logical backup file.
    tar vizxf filename.tar.gz
    filename.tar.gz indicates the name of the logical backup file.
  4. Decompress the compressed SQL file.
     gunzip filename.sql.gz
    filename.sql.gz indicates the name of the compressed SQL file.
  5. Import data to the destination database.
     mysql -u userName -p -h hostName -P port dbName < filename.sql
    filename.sql indicates the name of the decompressed SQL file.