Replication between On-Premise MySQL and MySQL on AWS RDS

Nikhil Kumar — Wed, 08 Dec 2021 18:36:49 +0000

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:

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

Managing Release on GITHUB

Nikhil Kumar — Mon, 06 Dec 2021 20:42:15 +0000

I was working around a few days back, managing the release of the project with versioning and proper tags associated with it which results in writing the python code.

I have written a simple python function that will call the API of GitHub and will Create/Fetch /Delete the release on GitHub.

Note** You can manage the same with CURL command too but I was facing some authentication issue, so haven’t done, I haven’t tried with explicitly passing the auth token in header by -H tag, you can give a try.

Have a look at the below snippet of code which can be easily integrated with Jenkins pipeline

import requests
import json

user = ""             #username of github account
repo = ""             #repository_name
auth = ""             #authentication token
url = "https://api.github.com"
header = {"Authorization": "token %s" %(auth), 'Accept': 'application/vnd.github.v3.raw'}
tag_name = ""         #tag_name of release
releaseId = ""        #releaseID

Here, tag_name and releaseId variables are only required if you want to delete a particular release, which you will rarely come across.

# Fetch latest release from github
def latestRelease():
    try:
        releases = requests.get('%s/repos/%s/%s/releases/latest' %(url,user,repo), headers=header).json()
        print(releases['tag_name'], releases['id'])
    except:
        print('Release Not Found')

The above code will be used to fetch the latest release present on GitHub.

# Create latest release on github
def postNewrelease():
    try:
        with open('release.json') as newRelease:
            _release = json.load(newRelease)
            release_new = requests.post('%s/repos/%s/%s/releases' %(url,user,repo), json=_release, headers=header).json()
            print(release_new)
    except:
        print("error while creating new release")

This snippet of code is used to create the release on GitHub, which contains payload in JSON format.

release.json will look something like below,

{
        "tag_name": "v1.6.0",
        "target_commitish": "master",
        "name": "v1.6.0",
        "body": "nikhil python api call release",
        "draft": false,
        "prerelease": false
}

Even with this payload, you can easily create prerelease of the master branch, which can be tested in the Staging environment.

# Delete release from github by passing tag_name & releaseId
def deleterelease():
    try:
        releaseDeleteTag = requests.delete('%s/repos/%s/%s/git/refs/tags/%s' %(url,user,repo,tag_name), headers=header)
        releaseDeleteId = requests.delete('%s/repos/%s/%s/releases/%s' %(url,user,repo,releaseId), headers=header)
        print(releaseDeleteId,releaseDeleteTag)
    except:
        print("error wile deleting release")

The above deletion method is not often required.

The full script is available in the below repository.

Github Link: https://github.com/nik0811/github-release-api.git

Feel free to comment below, if you will found any mistakes or required any type of modifications/enhancement. The code is fully tested so hopefully, you will not find any issues.

DEV Community: Nikhil Kumar

Replication between On-Premise MySQL and MySQL on AWS RDS

Managing Release on GITHUB