Physical migration from MySQL by using Percona XtraBackup and Amazon S3

Complete the steps in Creating an IAM policy to access Amazon S3 resources.

Complete the steps in Creating an IAM role to allow Amazon Aurora to access AWS services.

Complete the steps in Associating an IAM role with an Amazon Aurora MySQL DB cluster.

Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

In the top right corner of the Amazon RDS console, choose the AWS Region in which to create your DB cluster. Choose the same AWS Region as the Amazon S3 bucket that contains your database backup.

In the navigation pane, choose Databases, and then choose Restore from S3.

Choose Restore from S3.

The Create database by restoring from S3 page appears.

Under S3 destination:

1. Choose the S3 bucket that contains the backup files.

2. (Optional) For S3 folder path prefix, enter a file path prefix for the files stored in your Amazon S3 bucket.

   If you don't specify a prefix, then RDS creates your DB instance using all of the files and folders in the root folder of the S3 bucket. If you do specify a prefix, then RDS creates your DB instance using the files and folders in the S3 bucket where the path for the file begins with the specified prefix.

   For example, suppose that you store your backup files on S3 in a subfolder named backups, and you have multiple sets of backup files, each in its own directory (gzip_backup1, gzip_backup2, and so on). In this case, you specify a prefix of backups/gzip_backup1 to restore from the files in the gzip_backup1 folder.

Under Engine options:

1. For Engine type, choose Amazon Aurora.

2. For Version, choose the Aurora MySQL engine version for your restored DB instance.

For IAM role, you can choose an existing IAM role.

(Optional) You can also have a new IAM role created for you by choosing Create a new role. If so:

1. Enter the IAM role name.

2. Choose whether to Allow access to KMS key:

   • If you didn't encrypt the backup files, choose No.

   • If you encrypted the backup files with AES-256 (SSE-S3) when you uploaded them to Amazon S3, choose No. In this case, the data is decrypted automatically.

   • If you encrypted the backup files with AWS KMS (SSE-KMS) server-side encryption when you uploaded them to Amazon S3, choose Yes. Next, choose the correct KMS key for AWS KMS key.

     The AWS Management Console creates an IAM policy that enables Aurora to decrypt the data.

   For more information, see Protecting data using server-side encryption in the Amazon S3 Developer Guide.

Choose settings for your DB cluster, such as the DB cluster storage configuration, DB instance class, DB cluster identifier, and login credentials. For information about each setting, see Settings for Aurora DB clusters.

Customize additional settings for your Aurora MySQL DB cluster as needed.

Choose Create database to launch your Aurora DB instance.

Ensure that you are prepared for encrypted replication:

For more information, see Creating SSL certificates and keys using openssl in the MySQL documentation.

You need the certificate authority certificate, the client key, and the client certificate.

Connect to the Aurora MySQL DB cluster as the primary user using SSL.

For information about connecting to an Aurora MySQL DB cluster with SSL, see TLS connections to Aurora MySQL DB clusters.

Run the mysql.rds_import_binlog_ssl_material stored procedure to import the SSL information into the Aurora MySQL DB cluster.

For the ssl_material_value parameter, insert the information from the .pem format files for the Aurora MySQL DB cluster in the correct JSON payload.

The following example imports SSL information into an Aurora MySQL DB cluster. In .pem format files, the body code typically is longer than the body code shown in the example.

call mysql.rds_import_binlog_ssl_material(
'{"ssl_ca":"-----BEGIN CERTIFICATE-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END CERTIFICATE-----\n","ssl_cert":"-----BEGIN CERTIFICATE-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END CERTIFICATE-----\n","ssl_key":"-----BEGIN RSA PRIVATE KEY-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END RSA PRIVATE KEY-----\n"}');

For more information, see mysql.rds_import_binlog_ssl_material and TLS connections to Aurora MySQL DB clusters.

Ensure that the /etc/my.cnf file for the external MySQL database has the relevant entries.

If encrypted replication is not required, ensure that the external MySQL database is started with binary logs (binlogs) enabled and SSL disabled. The following are the relevant entries in the /etc/my.cnf file for unencrypted data.

log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

If encrypted replication is required, ensure that the external MySQL database is started with SSL and binlogs enabled. The entries in the /etc/my.cnf file include the .pem file locations for the MySQL database server.

log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# Setup SSL.
ssl-ca=/home/sslcerts/ca.pem
ssl-cert=/home/sslcerts/server-cert.pem
ssl-key=/home/sslcerts/server-key.pem

You can verify that SSL is enabled with the following command.

mysql> show variables like 'have_ssl';

Your output should be similar the following.

+~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+
| Variable_name | Value |
+~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+
| have_ssl      | YES   |
+~-~-~-~-~-~-~-~-~-~-~-~-~-~--+~-~-~-~-~-~--+
1 row in set (0.00 sec)

Determine the starting binary log position for replication. You specify the position to start replication in a later step.

Using the AWS Management Console

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the navigation pane, choose Events.

3. In the Events list, note the position in the Recovered from Binary log filename event.

   

Using the AWS CLI

You can also get the binlog file name and position by using the describe-events AWS CLI command. The following shows an example describe-events command.

PROMPT> aws rds describe-events

In the output, identify the event that shows the binlog position.

While connected to the external MySQL database, create a user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following is an example.

mysql> CREATE USER '<user_name>'@'<domain_name>' IDENTIFIED BY '<password>';

The user requires the REPLICATION CLIENT and REPLICATION SLAVE privileges. Grant these privileges to the user.

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>'@'<domain_name>';

If you need to use encrypted replication, require SSL connections for the replication user. For example, you can use the following statement to require SSL connections on the user account <user_name>.

GRANT USAGE ON *.* TO '<user_name>'@'<domain_name>' REQUIRE SSL;

In the Amazon RDS console, add the IP address of the server that hosts the external MySQL database to the VPC security group for the Aurora MySQL DB cluster. For more information on modifying a VPC security group, see Security groups for your VPC in the Amazon Virtual Private Cloud User Guide.

You might also need to configure your local network to permit connections from the IP address of your Aurora MySQL DB cluster, so that it can communicate with your external MySQL database. To find the IP address of the Aurora MySQL DB cluster, use the host command.

host <db_cluster_endpoint>

The host name is the DNS name from the Aurora MySQL DB cluster endpoint.

Enable binary log replication by running the mysql.rds_reset_external_master (Aurora MySQL version 2) or mysql.rds_reset_external_source (Aurora MySQL version 3) stored procedure. This stored procedure has the following syntax.

CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , mysql_binary_log_file_name
  , mysql_binary_log_file_location
  , ssl_encryption
);

CALL mysql.rds_set_external_source (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , mysql_binary_log_file_name
  , mysql_binary_log_file_location
  , ssl_encryption
);

For information about the parameters, see mysql.rds_reset_external_master (Aurora MySQL version 2) and mysql.rds_reset_external_source (Aurora MySQL version 3).

For mysql_binary_log_file_name and mysql_binary_log_file_location, use the position in the Recovered from Binary log filename event you noted earlier.

If the data in the Aurora MySQL DB cluster is not encrypted, the ssl_encryption parameter must be set to 0. If the data is encrypted, the ssl_encryption parameter must be set to 1.

The following example runs the procedure for an Aurora MySQL DB cluster that has encrypted data.

CALL mysql.rds_set_external_master(
  'Externaldb.some.com',
  3306,
  'repl_user'@'mydomain.com',
  'password',
  'mysql-bin.000010',
  120,
  1);

CALL mysql.rds_set_external_source(
  'Externaldb.some.com',
  3306,
  'repl_user'@'mydomain.com',
  'password',
  'mysql-bin.000010',
  120,
  1);

This stored procedure sets the parameters that the Aurora MySQL DB cluster uses for connecting to the external MySQL database and reading its binary log. If the data is encrypted, it also downloads the SSL certificate authority certificate, client certificate, and client key to the local disk.

Start binary log replication by running the mysql.rds_start_replication stored procedure.

CALL mysql.rds_start_replication;

Monitor how far the Aurora MySQL DB cluster is behind the MySQL replication primary database. To do so, connect to the Aurora MySQL DB cluster and run the following command.

Aurora MySQL version 2:
SHOW SLAVE STATUS;

Aurora MySQL version 3:
SHOW REPLICA STATUS;

In the command output, the Seconds Behind Master field shows how far the Aurora MySQL DB cluster is behind the MySQL primary. When this value is 0 (zero), the Aurora MySQL DB cluster has caught up to the primary, and you can move on to the next step to stop replication.

Connect to the MySQL replication primary database and stop replication. To do so, run the mysql.rds_stop_replication stored procedure.

CALL mysql.rds_stop_replication;