First we setting up Mysql Instance as a Master, \u00a0here some changes in my.cnf file<\/p>\n
[js]<\/p>\n
server-id = 14 # any unique number
\nlog_bin = \/var\/lib\/mysql\/mysql-bin.log
\nreplicate-wild-ignore-table=mysql.rds_% # exclude mysq.rds_* tales during replication from RDS
\nauto_increment_increment = 2 # total number of master server
\nauto_increment_offset = 2 # any number, but not higher then above value.
\n[\/js]<\/p>\n
After making all changes restart mysql<\/p>\n
[js]
\n\/etc\/init.d\/mysql restart
\n[\/js]<\/p>\n
We done with configuration of mysql, Now first have to create a user for replication and secondly check binlog file name with it’s position.<\/p>\n
[js]
\nmysql -uroot -p
\nmysql> grant replication slave on *.* to ‘<REPLI_USER>’@'<RDS_IP>’ identified by ‘<REPLI_PASSWORD>’;
\nmysql> show master status;
\n+——————+———-+————–+——————+——————-+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
\n+——————+———-+————–+——————+——————-+
\n| mysql-bin.000015 | 2249 | | | |
\n+——————+———-+————–+——————+——————-+
\n[\/js]<\/p>\n
Record this output as we need these information later in next step.<\/p>\n
2. Configure RDS as Master and then Slave:- <\/strong><\/p>\n By default in RDS binlog already enabled and we can verify this by ruuning “show master status” command in mysql shell. We jut need to set two variables in “Parameter Groups”. So login to AWS console to set these parameter and don’t forget to reboot your instance.<\/p>\n If we need to exclude some table, then sorry to say RDS not come with this option but don’t bother about it we already did this in previous step. Here first we repeat some of above steps, like replication user creation and check master records.<\/p>\n [js] Note down these information as we need this to make Mysql instance slave of RDS. Now we are going to configure RDS as slave. Please make sure the file name and position value same as noted in step 1.<\/p>\n [js] Check slave status by running below command. If every thing is good then out put would be like this<\/p>\n [js] 3. Configure Mysql Instance as Slave:-<\/strong><\/p>\n Now proceeding to make Mysql Instance slave of RDS. We need to repeat steps as we did earlier. Also here make sure value of position and file name match your information.<\/p>\n [js] Now slave activated and we can check slave replication by using below command<\/p>\n [js] So both DB replicating data from each other. We can test while creating one table in RDS and second table in Mysql Instance and you can see these tables mirrored on both server. We didn’t cover up much theoretical part, this was just a brief overview. You can write me your queries at rajdeeps[at]intelligrape.com.<\/p>\n Thanks Herein, we are looking into a solution of RDS master-master replication. Unfortunately, AWS don’t support master-master replication on RDS but we walk through a different approach, master-master replication between RDS and External MySql Instance.\u00a0This External MySql instance might be EC2 or your own server. Here are few things those are already setup:- 1. MySql 5.6 […]<\/p>\n","protected":false},"author":165,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":65},"categories":[1174],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/17735"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/165"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=17735"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/17735\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=17735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=17735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=17735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"\/blog\/wp-ttn-blog\/uploads\/2015\/03\/Screenshot-from-2015-03-04-162315.png\")
<\/p>\n
\nmysql-uroot -p -h<RDS_HOST>;
\nmysql> grant replication slave on *.* to ‘<REPLI_USER>;’@'<MYSQL_INSTANCE_IP>’ identified by ‘<REPLI_PASSWORD>’;
\nmysql> show master status;
\n+—————————-+———-+————-+——————+——————-+
\n| File \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
\n+—————————-+———-+————–+——————+——————-+
\n| mysql-bin-changelog.074711 | 2249 | | | |
\n+—————————-+———-+————–+——————+——————-+
\n[\/js]<\/p>\n
\nmysql -uroot -p<password> -h<RDS EndPonit>
\nmysq> call mysql.rds_set_external_master(‘<mysql_instance_ip>’,3306,'<REPLI_USER>’,'<REPLI_PASSWORD>’,’mysql-bin.000015′,2249,0);
\nmysql> call mysql.rds_start_replication;
\n[\/js]<\/p>\n
\nmysql -uroot -p<password> -h<RDS_EndPoint> -e "show slave status\\G;" | grep Running
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\n[\/js]<\/p>\n
\nmysql -uroot -p;
\nmysql> CHANGE MASTER TO MASTER_HOST = ‘<rds_host_ip>’, MASTER_USER = ‘<REPLI_USER>’, MASTER_PASSWORD = ‘<REPLI_PASSWORD>’, MASTER_LOG_FILE = ‘mysql-bin-changelog.074711’, MASTER_LOG_POS = 2249;
\nmysql> start slave;"
\n[\/js]<\/p>\n
\nmysql -uroot -p<password> -e "show slave status\\G;" | grep Running
\nSlave_IO_Running: Yes
\nSlave_SQL_Running: Yes
\n[\/js]<\/p>\n
\nRajdeep Singh<\/p>\n","protected":false},"excerpt":{"rendered":"