Replication between On-Premise MySQL and MySQL on AWS RDS

Replication between On-Premise MySQL and MySQL on AWS RDS

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.

Step 1:

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.

Step 2:

Now edit the file "/etc/mysql/mysql.conf.d/mysqld.cnf" add the below line to enable bin-log.

Note: Don’t copy and paste, check your conf file replace the comment if the line exists else add the line.

Step 3:

Restart your MySQL Server by below command,

Step 4:

Create MySQL on AWS RDS.

Step 5:

Once MySQL get created on RDS, Copy the database of Master on-premise MySQL server to RDS MySQL Server with below command,

Step 6:

On on-premise Master MySQL server create a user named “replicator” and grant the “privilege” to this user by below command,

Step 7:

Flush the table with read lock by below command,

Step 8:

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.

Step 9:

Make the database writable again by below command,

Now our master on-premise MySQL configuration is completed.

Step 10:

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,

Once you are logged in to RDS MySQL server use the below command to connect to your on-premise MySQL server,

Note: Don’t copy and paste the above command you need to pass the value at few of the places.

  1. Make sure to replace the ‘on_prem_mysql_IP’ with the IP of on-premise MySQL server.
  2. file‘ will be replaced by the value we obtain from "SHOW MASTER STATUS" above, which will be “mysql-bin.000007” as per my value.
  3. position will be replaced by the value we again obtain from "SHOW MASTER STATUS" above, which will be 154 as per my value.

Step 11:

It’s time to start replication by below command,

To stop replication use the below command,

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,

If there will be no error you will see,

Developer: Nikhil raj
Price: Free

Leave a Reply