RDS now has a provision to set a non RDS mysql instance (on premise or EC2) as its master and vice-versa.<\/p>\n
To test this out<\/p>\n
To do this login into the EC2 machine and edit the my.cnf or my.ini file.<\/p>\n
Under the mysqld section enter the following two lines<\/p>\n
5.Sync-up slave data with that of master and make sure the master is not undergoing any further updates.To achieve this first login to mysql and block all write statements with the below command.<\/p>\n Now take a mysqldump of the database.<\/p>\n Transfer the contents to the RDS database.<\/p>\n 6.Next we need the masters binary log co-ordinates .During replication slave start processing events in the binary log from this point.<\/p>\n Now, login into the RDS instance to configure the slave.<\/p>\n 7.Configure RDS to use the EC2 instance as its replication master 8.Start replication with the command.<\/p>\n Look for<\/p>\n 9.Stop replication with the command.<\/p>\n 10.Once stopped you can configure a new replication master as follows. Then set a new master using the mysql.rds_set_external_master command.<\/p>\n","protected":false},"author":219,"featured_media":0,"parent":0,"comment_status":"open","ping_status":"open","template":"","blog_category":[3606,3607],"user_email":"prarthitm@cloudthat.com","published_by":"324","primary-authors":"","secondary-authors":"","acf":[],"_links":{"self":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/1938"}],"collection":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog"}],"about":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/types\/blog"}],"author":[{"embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/users\/219"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/comments?post=1938"}],"version-history":[{"count":1,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/1938\/revisions"}],"predecessor-version":[{"id":42737,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog\/1938\/revisions\/42737"}],"wp:attachment":[{"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/media?parent=1938"}],"wp:term":[{"taxonomy":"blog_category","embeddable":true,"href":"https:\/\/www.cloudthat.com\/resources\/wp-json\/wp\/v2\/blog_category?post=1938"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}[mysqld]
\nlog-bin=mysql-bin
\nserver-id=1<\/code>
\nRestart the mysql server
\n4.Create a user for replication
\nWe have to create a user account on the master that the slave can use for replication.
\nLog into mysql and run the following commands<\/p>\nCREATE USER 'rep1'@'%' IDENTIFIED BY \u2018password';
\nGRANT REPLICATION SLAVE ON *.* TO 'rep1'@'%';<\/code><\/p>\nFLUSH TABLES WITH READ LOCK;<\/code><\/p>\nmysqldump \u2013u username \u2013ppassword dbname > dump.sql<\/code><\/p>\nmysql -u rdsuser -ppassword --host=RDS-endpoint --database=dbname < dump.sql<\/code><\/p>\nSHOW MASTER STATUS;
\n+------------------+----------+--------------+------------------+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+------------------+----------+--------------+------------------+
\n| mysql-bin.000001 | 107 | | |
\n+------------------+----------+--------------+------------------+<\/code><\/p>\n
\nLogin to RDS database and run the following command with the private ip of your ec2 instance, your replication username, password,your binary log name and co-ordinates.<\/p>\ncall mysql.rds_set_external_master('',3306,'rep1','password','mysql-bin.000001',107,0);<\/code><\/p>\ncall mysql.rds_start_replication;<\/code>
\nConfirm replication is happening by running the command<\/p>\nSHOW SLAVE STATUS\\G<\/code><\/p>\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes<\/code><\/p>\ncall mysql.rds_stop_replication;<\/code><\/p>\n
\nFirst reset the external mysql master using the command.<\/p>\ncall mysql.rds_reset_external_master;<\/code><\/p>\n