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.

[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.

Step 3:

Restart your MySQL Server by below command,

sudo systemctl restart mysql

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,

# 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>

Step 6:

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'@'%';

Step 7:

Flush the table with read lock by below command,

#Flush table with read lock
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

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,

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

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,

:~$ 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.

Step 11:

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,

CALL mysql.rds_skip_repl_error;

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