How to Set Up Replication Between AWS Aurora and an External MySQL Instance

Amazon RDS Aurora (MySQL) provides its own low latency replication. Nevertheless, there are cases where it can be beneficial to set up replication from Aurora to an external MySQL server, as Amazon RDS Aurora is based on MySQL and supports native MySQL replication. Here are some examples of when replicating from Amazon RDS Aurora to an external MySQL server can make good sense:

• Replicating to another cloud or datacenter (for added redundancy)
• Need to use an independent reporting slave
• Need to have an additional physical backup
• Need to use another MySQL flavor or fork
• Need to failover to another cloud and back

In this blog post I will share simple step by step instructions on how to do it.

Steps to setup MySQL replication from AWS RDS Aurora to MySQL server

1. Enable binary logs in the option group in Aurora (Binlog format = mixed). This will require a restart.
2. Create a snapshot and restore it (create a new instance from a snapshot). This is only needed to make a consistent copy with mysqldump. As Aurora does not allow “super” privileges, running mysqldump --master-data  is not possible. The snapshot is the only way to get a consistent backup with the specific binary log position.
3. Get the binary log information from the snapshot. In the console, look for the “Alarms and Recent Events” for the restored snapshot instance. We should see something like:
   
   Shell

   Binlog position from crash recovery is mysql-bin-changelog.000708 31278857

   1	Binlog position from crash recovery is mysql-bin-changelog.000708 31278857

4. Install MySQL 5.6 (i.e. Percona Server 5.6) on a separate EC2 instance (for Aurora 5.6 – note that you should use MySQL 5.7 for Aurora 5.7). After MySQL is up and running, import the timezones:
   
   Shell

   # mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql

   1	# mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql

   
   Sample config:
   
   MySQL

   [mysqld] log-bin=log-bin log-slave-updates binlog-format=MIXED server-id=1000 relay-log=relay-bin innodb_log_file_size=1G innodb_buffer_pool_size=2G innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 # as this is replication slave

   1 2 3 4 5 6 7 8 9 10 11

   [mysqld] log-bin=log-bin log-slave-updates binlog-format=MIXED server-id=1000 relay-log=relay-bin   innodb_log_file_size=1G innodb_buffer_pool_size=2G innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 # as this is replication slave

5. From now on we will make all backups from the restored snapshot. First get all users and import those to the new instance:
   
   Shell

   pt-show-grants -h myhost...amazonaws.com -u percona > grants.sql

   1	pt-show-grants -h myhost...amazonaws.com -u percona > grants.sql

   
   # check that grants are valid and upload to MySQL
   
   Shell

   mysql -f < grants.sql

   1	mysql -f < grants.sql

   
   Make a backup of all schemas except for the “mysql” system tables as Aurora using different format of those (make sure we connect to the snapshot):
   
   Shell

   host="my-snapshot...amazonaws.com" mysqldump --single-transaction -h $host -u percona --triggers --routines --databases `mysql -u percona -h $host -NBe "select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('mysql', 'information_schema', 'performance_schema')"` > all.sql

   1 2 3 4 5

   host="my-snapshot...amazonaws.com" mysqldump --single-transaction -h $host -u percona --triggers --routines --databases `mysql -u percona -h $host -NBe "select group_concat(schema_name separator ' ') from information_schema.schemata where schema_name not in ('mysql', 'information_schema', 'performance_schema')"` > all.sql

6. Restore to the local database:
   
   Shell

   mysql -h localhost < all.sql

   1	mysql -h localhost < all.sql

7. Restore users again (some users may fail to create where there are missing databases):
   
   Shell

   mysql -f < grants.sql

   1	mysql -f < grants.sql

8. Download the RDS/Aurora SSL certificate:
   
   Shell

   # cd /etc/ssl # wget 'https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem' # chown mysql.mysql rds-combined-ca-bundle.pem

   1 2 3

   # cd /etc/ssl # wget 'https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem' # chown mysql.mysql rds-combined-ca-bundle.pem

9. Configure MySQL replication. Take the values for the binary log name and position from #3 above. Please note: now we connect to the actual instance, not a snapshot:
   
   Shell

   # mysql -h localhost ... mysql> CHANGE MASTER TO MASTER_HOST='dev01-aws-1...', MASTER_USER='awsreplication', MASTER_PASSWORD='<pass>', MASTER_LOG_FILE = 'mysql-bin-changelog.000708', MASTER_LOG_POS = 31278857, MASTER_SSL_CA = '/etc/ssl/rds-combined-ca-bundle.pem', MASTER_SSL_CAPATH = '', MASTER_SSL_VERIFY_SERVER_CERT=1; mysql> start slave;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   # mysql -h localhost   ...   mysql> CHANGE MASTER TO MASTER_HOST='dev01-aws-1...', MASTER_USER='awsreplication', MASTER_PASSWORD='<pass>', MASTER_LOG_FILE = 'mysql-bin-changelog.000708', MASTER_LOG_POS = 31278857, MASTER_SSL_CA = '/etc/ssl/rds-combined-ca-bundle.pem', MASTER_SSL_CAPATH = '', MASTER_SSL_VERIFY_SERVER_CERT=1;   mysql> start slave;

10. Verify that the slave is working. Optionally add the SQL_Delay option to the CHANGE MASTER TO (or anytime) and specify the slave delay in seconds.

I hope those steps will be helpful for setting up an external MySQL replica.

You May Also Like

Grow Your Business with an AWS RDS MySQL Environment. This solution brief outlines setting up MySQL Amazon RDS instances to meet your company’s growing needs.

5 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Khurram Naseem

5 years ago

mysqldump with terabytes of data, how we can optimise it ?

0

Chris Schneider

5 years ago

@khurram you may want to try mydumper (https://github.com/maxbube/mydumper)

0

perydell

5 years ago

Is Amazon supporting external replicas for all RDS instances or is this just an Aurora thing?

0

Ian

5 years ago

Out of interest do you have a guide to do the reverse of this? On prem MySQL to Aurora?

0

Ramki

5 years ago

aws supports restoring xtrabackup backups for mysql 5.6 compatible Aurora, it doesn’t support mysql 5.7 yet.

0