First, you need to create a RDS instance (if not exist).
Than you should check the current value for the RDS binlog settings:
admin@awsgate [(none)]> call mysql.rds_show_configuration\G
*************************** 1. row ***************************
name: binlog retention hours
value: NULL
description: binlog retention hours…
1 row in set (0.00 sec)
If the value is NULL, the binlogs will be deleted after RDS is not requiring this for internal slaves anymore. Set it to the number of hours you want to keep the binlog. Please don’t forget, that every MB diskspace on Amazon costs money. So be aware of what you are setting here. For this howto, i’m choosing 24 hours:
admin@awsgate [(none)]> call mysql.rds_set_configuration(‚binlog retention hours‘, 24);
Additionally we need a user for the replication:
admin@awsgate [(none)]> GRANT REPLICATION SLAVE ON *.* TO ’slave’@’%‘ IDENTIFIED BY ‚Ultr4M3g4H!ghS3cur!ty‘;
The next step is to create a backup of your RDS instance. I’m using mydumper, because it is much faster and easiert to handle than mysqldump. You should know that mydumper will lock all tables for the backup. I recommend to setup a slave on RDS for creating the backup .
me@awsgate:~/dump/# /usr/bin/mydumper -u youruser-p ‚yourpassword‘ -h hostname.ident.region.rds.amazonaws.com -o ./ -e -c -k -l 120 –lock-all-tables
–lock-all-tables is required for getting the RDS master data.
–use-savepoints is not working with RDS, because you need SUPER permissions for that, which is not avaiable on RDS instances.
In my case i need to copy the backup to new slave machine. You can also do the export directly on the slave machine. Don’t forget to open the mysql port in the AWS security group for the RDS instance.
I recommend to not import the mysql database. It can result into errors:
me@slave:~/dump# rm -f mysql*
Now you can import the data:
me@slave:~/dump# myloader -d ./ -t 32 -u youruser -p yourpass
Now you need to find out the master log file and position:
me@slave:~/dump# cat metadata
Started dump at: 2015-11-24 14:26:56
SHOW SLAVE STATUS:
Host: 10.10.1.196
Log: mysql-bin-changelog.025550
Pos: 9170
Finished dump at: 2015-11-24 14:27:12
Now you can setup the master data on the slave and activate the slave process. You need to replace the values of MASTER_LOG_FILE and MASTER_LOG_POS with the values from the metadata file.
me@slave [(none)]> CHANGE MASTER TO MASTER_HOST=’hostname.ident.region.rds.amazonaws.com‘,MASTER_USER=’slave‘,MASTER_PASS=’Ultr4M3g4H!ghS3cur!ty‘,MASTER_LOG_FILE=’mysql-bin-changelog.025550′,MASTER_LOG_POS=9170;
me@slave [(none)]> START SLAVE;
The replication should be running now. Check it out:
me@slave [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: hostname.ident.region.rds.amazonaws.com
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-changelog.025557
Read_Master_Log_Pos: 9170
Relay_Log_File: mysqld-relay-bin.000016
Relay_Log_Pos: 9343
Relay_Master_Log_File: mysql-bin-changelog.025557
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…
Exec_Master_Log_Pos: 9170
Relay_Log_Space: 9574
…
Master_Server_Id: 950487267
Master_UUID: 43d7b440-4d6b-11e5-865d-06fdf6329d29
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
…
1 row in set (0.00 sec)