How to establish the replication between on-premise MySQL and AWS MySQL RDS?
Here your on-premise MySQL will be master and MySQL running on AWS RDS will be your Slave.
Log in to the terminal where master MySQL is installed, for example, I have installed my MySQL server on Ubuntu.
So, take the access of Ubuntu Server by SSH.
Now edit the file
/etc/mysql/mysql.conf.d/mysqld.cnf add the below line to enable bin-log.
[mysqld] # # * Basic Settings server-id = 101 auto-increment-increment = 2 auto-increment-offset = 2 #bind external address bind-address = 0.0.0.0 # log-bin enable log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_format = mixed #binlog_do_db = include_database_name binlog_ignore_db = mysql
Note: Don’t copy and paste, check your conf file replace the comment if the line exists else add the line.
Restart your MySQL Server by below command,
sudo systemctl restart mysql
Create MySQL on AWS RDS.
Once MySQL get created on RDS, Copy the database of Master on-premise MySQL server to RDS MySQL Server with below command,
# copy dump on rds :~$ mysqldump --databases <database_name> \ --single-transaction \ --compress \ --order-by-primary \ -u <username of onprem mysql server> \ -p<password of onprem mysql server> | mysql \ --host=<endpoint of MySQL RDS> \ --port=3306 \ -u <username of RDS mysql server> \ -p<password of RDS mysql server>
On on-premise Master MySQL server create a user named “replicator” and grant the “privilege” to this user by below command,
mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicator'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Flush the table with read lock by below command,
#Flush table with read lock FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
Now check the master status by below command,
Make sure to note “File” and “Position” value, we will be using it later on RDS MySQL side.
Make the database writable again by below command,
mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES;
Now our master on-premise MySQL configuration is completed.
Take the access of your MySQL RDS running on AWS with the help of any MySQL client package, as am using Ubuntu so I can directly install
mysql-client on my workstation machine which I am using, once the mentioned package get installed I need to use below command,
:~$ mysql -u <username of RDS MySQL> -h <Endpoint of RDS MySQL> -p<password of RDS MySQL>
Once you are logged in to RDS MySQL server use the below command to connect to your on-premise MySQL server,
mysql> call mysql.rds_set_external_master ('on_prem_mysql_IP', 3306, 'replicator', 'replicator', 'file', position, 0);
Note: Don’t copy and paste the above command you need to pass the value at few of the places.
Make sure to replace the `on_prem_mysql_IP` with the IP of on-premise MySQL server. `file` will be replaced by the value we obtain from `SHOW MASTER STATUS` above, which will be `mysql-bin.000007` as per my value. position will be replaced by the value we again obtain from "SHOW MASTER STATUS" above, which will be 154 as per my value.
It’s time to start replication by below command,
mysql> call mysql.rds_start_replication;
To stop replication use the below command,
mysql> call mysql.rds_stop_replication;
To skip the repl_error, if you will by mistake delete the table from slave rather deleting it from the master, use the below command,
To test the Status of slave use the below command,
mysql> SHOW SLAVE STATUS \G
If there will be no error you will see,
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Top comments (0)