Replication between On-Premise MySQL and MySQL on AWS RDS
Tags: aws, mysql, rds
Published: 2021-12-08 18:36:49

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,


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wpomb6feseh1wga8f62s.png)

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