AWS RDS之间如何建立复制
来源:互联网 发布:java中400是什么意思 编辑:程序博客网 时间:2024/06/15 13:55
How to configure Replication between AWS MySQL RDS to MySQL RDS
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之间如何建立复制
- AWS RDS Rename DataBase - RDS重命名数据库
- aws的rds导出问题
- AWS RDS 介绍 三)创建 RDS 实例
- AWS OpsWorks新增Amazon RDS支持
- AWS中RDS修改mysql的编码
- AWS RDS Oracle DB remote connection
- AWS RDS 解决时区,utf8问题
- aws rds配置还是要使用域名
- 如何在AWS下建立一个windows VPS
- 如何在亚马逊 AWS EC2建立一个Debian服务器
- AWS Beanstalk For .NET及RDS For SQL Server
- AWS增加Redis并对RDS进行若干改进
- Converting Your AWS EC2 & RDS Instances To Amazon VPC
- 亚马逊AWS学习——创建RDS实例
- 试用AWS RDS的还原到时间点功能
- Data Migration to AWS RDS - 数据库迁移总结
- AWS RDS 无super权限下创建function
- std:move基本用法和理解
- 数据库事务ACID特性
- Spark Streaming官方文档复习笔记-4
- jenkins插件 extended choice parameter
- C#编程利器之三:接口(Interface)
- AWS RDS之间如何建立复制
- 第五期
- python os.path模块
- c++第一次实验:1,本月有几天? 2,输出完数
- “ora:无法解析指定的连接标识符”的问题解答
- python正则表达式详解
- react中遇到的问题
- java数组
- PHP实现文章评论系统