AWS RDS之间如何建立复制


How to configure Replication between AWS MySQL RDS to MySQL 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/

AWS RDS(Relational Database Service)是AWS提供的一项托管关系型数据库服务,它可以自动处理数据库的常规管理任务,例如备份、软件升级、补丁管理、监控、容灾等,让用户可以更专注于应用程序的开发和运维。以下是AWS RDS的详细说明: 1. 数据库引擎:AWS RDS支持多种主流的关系型数据库引擎,包括MySQL、PostgreSQL、Oracle、SQL Server、MariaDB和Amazon Aurora等。用户可以根据实际需求选择适合的数据库引擎。 2. 自动化管理:AWS RDS可以自动处理数据库的常规管理任务,例如备份、软件升级、补丁管理、监控、容灾等,减少了用户的管理工作量和风险。 3. 可伸缩性:AWS RDS可以根据需要自动或手动调整数据库实例的计算和存储资源,以应对不同的负载需求。用户可以选择按需付费或预留实例来优化成本和性能。 4. 安全性:AWS RDS提供多种安全性功能,例如网络隔离、数据加密、访问控制、审计日志等,以保障数据库的安全和合规性。用户也可以自定义VPC和安全组等网络设置。 5. 多区域容灾:AWS RDS可以在多个AWS区域之间自动或手动进行数据库复制和故障转移,以提高业务的可用性和容灾性。用户可以选择异地备份和区域读取等功能。 总之,AWS RDS是一项可靠、安全、弹性、易用的托管关系型数据库服务,它可以让用户更专注于应用程序的开发和运维,而不用担心数据库的管理和维护。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值