aws rds复制ec2 mysql_AWS RDS之间如何建立复制

Jul 18, 2016

2 min

RDS is fully managed Database service by AWS . Mysql is also available there. Relica - It is necessary to everyone for Backup the data, DR, Testing,etc. AWS also provide the Read replica option. Recently they launched cross region replica for Mysql and Postgresql. We are helping to you Replicate your On-prem MySQL database to RDS and replicate RDS to On-prem servers. Now we’ll guide you to replicate MySQL RDS to RDS. You can replicate it to any region.

Pre-requisites:

Both RDS instances are publically available.

They must have same Parameter group values.

MySQL version - MySQL supports replication from one release series to the next higher release series. (Ex. you can replicate 5.5 to 5.6, you can’t replicate 5.5 to 5.7)

Both RDS have the same Parameter group values and Same optional group values

Steps1: Set binlog format

Change Master and slave binlog_format to ROW.(It’ll skip some initial errors)

Step2: Create user for replication and grant replication privilege.

I have created an user called repl_user and its password repl_user.

CREATE USER 'rep_user'@'%' IDENTIFIED BY 'rep_user'; GRANT REPLICATION slave ON *.* TO 'rep_user'@'%' IDENTIFIED BY 'rep_user';

FLUSH PRIVILEGES;

Step3: Create a table and insert some rows.

Use PowerupDBA;

Create table rep_test (number int);

Insert into rep_test values (1);

Insert into rep_test values (2);

Insert into rep_test values (3);

Step4: Note the master binlog position.

Show master status;

Step5: Backup the all Master databases.

Copy the database from the external instance to the Amazon RDS DB instance using mysqldump.

mysqldump --single-transaction --routines --triggers --events --databases PowerupDBA -h ******.ciqk2zkqxh3j.us-east-1.rds.amazonaws.com -u powerupdba -p > Powerupdba_bak.sql

Step6: Restore the backup file to Slave.

mysql -h ****.ciqk2zkqxh3j.us-east-1.rds.amazonaws.com -u Powerupdba -p PowerupDBA < Powerupdba_bak.sql

Step7: Mention master server and replication user on Slave.

Connect to the Slave Amazon RDS DB instance as the master user and identify the external MySQL or MariaDB database as the replication master by using the mysql.rdssetexternal_master command.

CALL mysql.rds_set_external_master ('Master_RDS_EndPoint', 3306, 'Replication_user_name', 'Replication_User_password', 'BinLog_FileName', BingLog_Position, 0);

Step8: Start Replication

On the Amazon RDS DB instance, issue the mysql.rdsstartreplication command to start replication

CALL mysql.rds_start_replication;

Step9: Check replication;

Show slave status will show the replication is working or not.

Show slave status\G;

I have inserted 3 more rows.

Insert into rep_test values (4);

Insert into rep_test values (5);

Insert into rep_test values (6);

Now check this slave.

select * from rep_test;

Yup, its replicated :)

转载自：http://blog.powerupcloud.com/2016/07/18/how-to-configure-replication-between-aws-mysql-rds-to-mysql-rds/