TRY AND ERROR

気になったこと、勉強したこと、その他雑記など色々メモしていきます。。Sometimes these posts will be written in English.,

Make replication between external Mysql and RDS-Aurora.

My external Mysql, which means non-AWS-RDS, has a large capacity records in it, and have been replicating between master and slave which are both external Mysql.It's difficult and too annoying about its slowness to dump data and to import them to Aurora.Instead of mysqldump, I tried to use percona-xtrabackup which is a third-party tools to migrate Mysql Database from S3, but restoring to Aurora with percona-xtrabackup didn't work since mysql version wasn't supported.(Accoding to error message, restoring by S3 data is accepted ver5.6 of source Mysql database.)
So I've given up to using percona-xtrabackup, and I'll show you the snippets how to make replication between external Mysql and RDS-Aurora with mysqldump.


# Refer to this for details.
http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html


This is my environments.

CentOS Linux 7.2.1511
mysql 5.7.16
innobackupex 2.4.8

First of all, in source Mysql you need to add a replication user with Aurora.

mysql > CREATE USER 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>';
mysql > GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>';


And then you make backup as gz archived file. At the same time you need to set option "--master-data=2" to put out MASTER_LOG_FILE and MASTER_LOG_POS statements.

$ MYSQL_PWD="xxxxxxxxxxxx" mysqldump --opt --all-databases --events --default-character-set=binary --master-data=2 -u myuser | gzip > ./backup.sql.gz


After the backup is finished, you need to search MASTER_LOG_FILE and MASTER_LOG_POS statements like this.
With zgrep command, you can grep in gz archived file as it is.

$ zgrep -i "CHANGE MASTER TO" backup.sql.gz > grepped.txt

Import backup data to Aurora.

$ zcat ./backup.sql.gz | mysql -u root -p -h aurora_host da_name 


Set master database info to Aurora with prepared stored procedure.
Then 'mysql bin file' and 'position' are able to be fetched from grepped.txt.

mysql > CALL mysql.rds_set_external_master ('source mysql host', port, 'repl_aurora', 'aurora password', 'mysql bin file', position, 0); 


Start replication.

mysql > CALL mysql.rds_start_replication;


Watch replication status.

SHOW SLAVE STATUS \G;