TECHIES WORLD

For Techs.... Techniques.... Technologies....

LinuxMysql

How to configure replication between RDS MySQL and Self-hosted MySQL

admin

Amazon Relational Database Service is a distributed relational database service by Amazon Web Services. We can create the replicas of database instance within RDS at any time through the AWS console by following some simple steps. But if we need to create replicas outside to RDS like in EC2 or any other self hosted server, a lot of configurations are required.

This article explains the detailed steps to configure master-master replication between RDS and Mysql service hosted on EC2. Please note that the same procedure can be user for the migration of database from RDS to EC2 of any selfhosted server and vice versa.

Step1: Edit the following values in RDS Master parameter groups.

    auto_increment_increment = 2
    auto_increment_offset = 2

Step2: Create a temporary read replica of RDS Master and wait until Master and Slave become online.

Step3: Connect to RDS Master terminal and run the following command.

    call mysql.rds_set_configuration('binlog retention hours', 24);

Step4: Create replication user and assign the required privileges using the following commands in RDS Master terminal.

CREATE USER 'repl-user'@'EC2-Master' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl-user'@'EC2-Master';

Where we need to replace,

repl-user with the username of replication user.
EC2-Master with the hostname or ipaddress of EC2 instance where MySQL is hosted.
pass with the password of replication user.

Step5: Open a MySQL connection to RDS read replica and run the commands.

call mysql.rds_stop_replication;
SHOW SLAVE STATUS;

Please note down the the slave status values as we need those values in the upcoming steps.

Step6: Take mysqldump from RDS read replica for all required databases.

mysqldump -h RDS-REPlCA -u RDS-USER -p DB > DB.sql

Where we need to replace,

RDS-REPlCA with the ipaddress or hostname of RDS read replica.
RDS-USER with the username of RDS MySQL.
DB with the name of the database.

This process need to be repeated for all databases in RDS read replica.

Step7: Copy all the dump files to EC2 Master server.

Step8: Login to EC2 Master instance and add the following lines to Mysql configuration.

server-id = 101
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
replicate-wild-ignore-table=mysql.rds_%
auto_increment_increment = 2
auto_increment_offset = 2

Step9: Import the DB dump for all required databases to EC2 Master.

mysql -h localhost -u USER -p DB < DB.sql

Where we need to replace,

USER with the username of MySQL hosted in EC2.
DB with the name of the database.

This process need to be repeated for all databases.

Step10: Restart Mysql service in EC2 instance.

#service mysqld restart

Step11: Connect to EC2 Master terminal and set the source as rds-master using the following command.

CHANGE MASTER TO MASTER_HOST='RDS-Master', MASTER_USER='repl-user', MASTER_PASSWORD='pass', MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;

Where we need to replace,

RDS-Master with the ipaddress or hostname of RDS master server.
repl-user with the username of replication user.
pass with the password of replication user.
Relay_Master_Log_File with the value of MASTER_LOG_FILE noted from slave status.
Exec_Master_Log_Pos with the value of MASTER_LOG_POS noted from slave status.

Step12: Start the slave in EC2 Mysql instance.

START SLAVE;

Step13: Create replication user in EC2 Master with the following commands.

CREATE USER 'repl-user'@'RDS-Master' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'RDS-Master';

Where we need to replace,

repl-user with the username of replication user.
RDS-Master with the hostname or ipaddress of RDS master instance.
pass with the password of replication user.

Step14: Check the EC2 MySQL master status.

show master status;

Please note down the the master status values as we need those values in the upcoming steps.

Step15: Connect to RDS Master terminal and start slave.

call mysql.rds_set_external_master('EC2-Master',3306,'repl','pass','MASTER_LOG_FILE',MASTER_LOG_POS,0);
call mysql.rds_start_replication;

Where we need to replace,

EC2-Master with the ipaddress or hostname of EC2 MySQL master server.
repl-user with the username of replication user.
pass with the password of replication user.
MASTER_LOG_FILE with the value of MASTER_LOG_FILE noted from master status.
MASTER_LOG_POS with the value of MASTER_LOG_POS noted from master status.

Step16: Remove the temporary read replica that we have created in RDS.

That's all…

Leave a Reply