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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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,
|
1 |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 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,
|
1 2 |
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,
|
1 2 3 |
#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,
|
1 2 |
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,
|
1 |
:~$ 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,
|
1 |
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,
|
1 |
mysql> call mysql.rds_start_replication; |
To stop replication use the below command,
|
1 |
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,
|
1 |
CALL mysql.rds_skip_repl_error; |
To test the Status of slave use the below command,
|
1 |
mysql> SHOW SLAVE STATUS \G |
If there will be no error you will see,
|
1 2 |
Slave_IO_Running: Yes Slave_SQL_Running: Yes |
