A Cloud Architect Company
upgrade MySQL RDS Database
Amazon Web Services

How to upgrade MySQL version in RDS with zero to minimal downtime using AWS DMS

Introduction

  • We can easily Upgrade our AWS MySQL RDS Database Instance from MySQL 5.7 to 8.0 with just modify the DB Instance version. But when you modify your DB instance’s version, it takes the modification time minimum of 15 mins to 4 hours or higher. In real time scenarios it will lead to downtime in our live application at the time of DB upgrading. 
  • Nobody likes interruptions. Here I come with a solution that will give you almost Zero downtime when upgrading your MySQL RDS Database Instance.
  • In this article I will show you how to upgrade your MySQL RDS Database Instance version from 5.7 to 8.0 or Latest version with Zero downtime using DMS (Data Migration Service).
  • DMS is provided by AWS which helps to Migrate your database from On-Premise, DB in EC2 Instance or RDS to the same or any of the types that mentioned above. Let’s get started…

Create Parameter Groups

Open your AWS console and search for RDS and click it.

On the left side of the RDS page you can see parameter groups. Click it again click Create parameter group button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime create Pg

  • Choose the Parameter group family to mysql5.7.
  • Enter Group name to mysql-57 or whatever you want to provide as your convenience.
  • Then provide a description about the parameter group then click Create.

 

Also Learn: How to setup RDS auto scaling in AWS 

 

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime create Parameter group

You can see the parameter group could be created.

Select the newly created group click Parameter group actions followed by Edit.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Edit Parameter group 5.7

Search for binlog_format and change the value to ROW like the picture below.

Click the Save changes button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Modify Parameter group 5.7

Create another parameter group with the Parameter group family mysql8.0.

Group name mysql-80 and give a description and click Create.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Edit Parameter group 8.0

Then Edit the newly created mysql-80 parameter group and search for binlog_format and change the value to ROW then finally click Save changes.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Modify Parameter group 8.0

Create RDS Database Instance with MySQL 5.7

On the left navigation section under the Amazon RDS Click the Databases section followed by Create database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database

Select the standard create option. For Engine choose MySQL engine.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database Engine

For MySQL version choose 5.7.34. Because first we are going to create an older version of MySQL.

For Templates choose the Free tier section. It will reduce the cost for creating a database. Here we are going to create a database for testing purposes only. So free tier is enough for now.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database Engine version

Under Settings, for DB instance identifier, provide a name for your database instance.

For master username enter admin and Master password provide a password for your database username and type the password again for confirmation.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database DB Settings

Under the Instance configuration, for DB Instance class select the least size for this demo.

For storage type choose gp2 and select 10 for Allocated storage.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database Instance Config

For the Connectivity section, first select Don’t connect to an EC2 compute resource option. We can configure it later.

Next the network-type choose IPv4, and VPC, choose any VPC as you want to use. For myself I choose the default one.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database Connectivity

  • For the DB subnet group select default.
  • For Public access, choose yes. Then only we can connect with our database from outside of the VPC.
  • Then for the Security group select the Create new option and provide a Name for it.
  • And choose any of the availability zone that you want.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database DB Network

Scroll down and expand the Additional configuration section.

Under the DB parameter group choose the parameter group which you create from the beginning of this tutorial for MySQL version 5.7.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database Addtional options

Scroll down to the last, click Create database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Database

Now your database is being created. It will take around 10 minutes.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Creating Database

In the meantime we have to modify the security group which is created by the RDS database.

Go to the VPC section followed by security groups section search for the security group which is created by the RDS.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Security Group

Select the security group and select inbound rules.

Click Edit inbound rules button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Select inbound rules

You can see a single inbound rule with port range 3306 for mysql database connection.

For source choose Anywhere and provide 0.0.0.0/0 IP range like the picture below. Then click the save rules button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Edit inbound rules

Create Tables and add Contents

After 10 minutes your MySQL RDS database is successfully created and its status will be Available.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime DB Available

Choose the database that you created now and under the Connectivity & security section you can find the Endpoint of your database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime DB Endpoint

Connect to your EC2 instance via SSH connection and login as a root user.

Then run the following command with your main MySQL database Endpoint.

mysql -h <your_main_database_endpoint> -P 3306 -u admin -p

It asks for a password and provide the password for the admin user.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime DB Login

Now first we are going to create a database inside of MySQL database using the following command. 

CREATE DATABASE <database_name>;

Then create a table with the name Persons.

CREATE TABLE <database_name>.Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Gender varchar(255)
);

Now we have to inject a detail of a person using the following command.

INSERT INTO <database_name>.Persons
VALUES ('0023', 'Holland', 'Tom', 'male');

Run the following command to view the contents of the table.

SELECT * FROM <database_name>.Persons;

It will show the table like the below screenshot.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime DB Create table and Insert

Create another RDS DB Instance from the Primary Instance.

Select the main MySQL RDS database.

Select Modify and click Restore to point in time.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Modify Main Database

Select the Restore time to the latest restorable time.

For DB engine select MySQL Community Edition and for DB instance identifier provide a name for your restoring database instance.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time

  • For instance configuration leaves it as default.
  • For Multi AZ deployment under Availability & durability, for testing purpose select do not create a standby instance
  • In a real time production environment choosing to create a standby instance is a recommended way.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time

Scroll down to the bottom. Leave all the other things as default.

Click the Restore to point in time button to create a new database instance.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time Create

Now you can see a new restored database instance is being created.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time Creating

Once the restored DB instance is created you can see the Endpoint of the restored database under the Connectivity & security.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time Created

Using the new Endpoint of the restored database instance run the following command in your EC2 instance to connect with the restored database.

mysql -h <restored_database_endpoint> -P 3306 -u admin -p

Run ‘SHOW DATABASES;‘ command and you can see the database which is created in the primary database.

Run the following command to see whether the table which we created in the primary database is replicated in this restored database.

SELECT * FROM <database_name>.Persons;

If your output is like the below picture, your restored database instance is successfully restored from the primary database instance.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Restore Database Point In Time Checking

Database Migration Service

  • Now we are going to create a replication from the primary database instance to restored or secondary database instance.
  • Here what is replication means, whenever a changes are made from primary database, they will immediately replicated to the secondary database.
  • Since this is a homogeneous database migration (the source and target database engines are the same). 

Create Replication Instance

  • Navigate to the DMS page in AWS console and on the left side panel under the Migrate data section click Replication instances.
  • Replication instances help to create a connection between the source and target database instances to transfer the data.
  • Click the Create replication instance button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Replication Instance

Enter a name to your replication instance.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Replication Instances

  • For instance configuration choose a least type of instance class for this tutorial purpose only.
  • For the Engine version, choose the latest version of DMS.
  • For the Multi-AZ, choose Dev or test workload for testing purpose.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Replication Instance Engine Version

For VPC select the VPC which you select for your primary database instance and the same as the subnet group.

For now select the public accessible to enable.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Replication Instance Connectivity & Security

  • For the availability zone, choose the one where you created your primary database instance. The Database instances and replication instances should be in the same availability zone.
  • For VPC security groups choose the security group that we created earlier.
  • Click the create replication instance button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Replication Instance Advanced Settings and Create

Replication instances take around 15 to 20 minutes to create.

Once it is created successfully it shows Available status like the picture below.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Created Replication Instance

Create Source and Target Endpoints

On the left side panel under the Replication instances choose Endpoints.

Then click the Create endpoint button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Create Endpoints

  • First we are going to create a Source endpoint. So choose Source endpoint for Endpoint type.
  • Check the Select RDS DB instance box.
  • Choose the Primary RDS DB Instance.
  • Under endpoint configuration, for the Endpoint identifier give a name like the below picture.
  • Choose the source engine as MySQL.Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Choose Endpoint Type

Choose Provide access information manually for Access to the endpoint database.

And the Server name, Port and the user name are filled default and for password, enter the password for the user.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Provide Endpoint Details

  • Expand the Test endpoint section, choose the VPC which you use for this demo, and choose the replication instance that you created earlier.
  • Click Run test to test the connection to the database.
  • If the connection was successful it will show like the picture below.
  • Finally click Create endpoint button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Test Source Endpoint Connection

Now your source endpoint is in Active status.

Click Create endpoint button to create target endpoint.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Source Endpoint Created

  • Choose the Endpoint type as Target endpoint and check the box for Select RDS DB instance.
  • Select restored database instance as target RDS instance.
  • For Endpoint identifier, enter a name for the target endpoint  and choose the target engine as MySQL.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Target Endpoint Create

Choose Provide access information manually for Access to the endpoint database.

And the Server name, Port and the user name are filled default with the restored database details and for password, enter the password for the user.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Target Endpoint details

  • Expand the Test endpoint section, choose the VPC and the replication instance.
  • Click Run test to test the connection to the restored database.
  • If the connection was successful it will show like the picture below.
  • Finally click Create endpoint button

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Test Target Endpoint Connection

Now our two endpoints are Active.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Source and target Endpoints

Create Database Migration Task

Now login to your primary MySQL RDS Database and run the following commands to insert user details to the table and list out the table.

 

INSERT INTO <database_name>.Persons
VALUES ('0024', 'Maguire', 'Toby', 'male');

SELECT * FROM <database_name>.Persons;

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Insert User Details into Table in Primary DB

Login to the restored database and run the following command to see the changes made from the primary database are replicated to this database.

SELECT * FROM <database_name>.Persons;

You are not able to see the changes in the restored database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Check Replication In Secondary DB

  • Because the changes are not replicated to secondary database.
  • For this we need to create a Database migration task.
  • So click the Create database migration task button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task

  • For Task identifier, provide a name for the task.
  • Choose the replication instance which we created for the Replication instance.
  • For Source database endpoint, choose the endpoint you have created for source and for the Target database endpoint, choose the endpoint you’ve created for target.
  • For the Migration type, choose Migrate existing data and replicate ongoing changes.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task Configuration

Scroll down and under the Task settings, for the Editing mode choose Wizard.

Change the followings and keep the default values to the remaining options:

  1. Target table operation mode – Do nothing
  2. Turn on validation – Enable
  3. Turn on Cloudwatch Logs – Enable

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task Settings

  • Scroll down and expand Selection rules and Click Add new selection rule.
  • For Schema select Enter a schema.
  • For source name Enter the database name which you want to replicate to the target database.
  • For my scenario I use the test as a database which is going to be replicated.
  • For source table name the % symbol indicates all the tables inside the source database. So leave it as default.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task Schema Settings

Scroll down to the bottom and click Create task.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task Create task

Now your Database migration task will be in starting status.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Database Migration Task Creating

Once its status is shown like the below screenshot, go to the table statistics section.

You are able to see the Table Persons is updated successfully.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime DMS Table Statistics

So login to your restored database and run the following command to see the updated status.

SELECT * FROM <database_name>.Persons;

You can see the second name with the column PersonID 24 will replicated from the primary database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List All in Table in Secondary DB

Check the Replication

In Primary Database

Now login to the primary database and run the following commands to add a row inside the Persons table.

INSERT INTO <database_name>.Persons
VALUES ('0030', 'Olsen', 'Elizabeth', 'female');

SELECT * FROM <database_name>.Persons;

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Insert Row in Table in Primary DB

In Restored Database

  • Now login to the secondary database with its endpoint and run the following command to see the replication status.
SELECT * FROM <database_name>.Persons;
  • You can now see the replication from the primary database to the restored database is successfully working.
  • So from now whatever changes made from the primary database is replicated to the restored database immediately with the delay of milliseconds.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Users in Table in Secondary DB

Upgrade the RDS Restored Database instance to latest version

  • Now select the restored RDS database instance and click Modify.
  • For the DB engine version choose the latest version at the time you are working.
  • For currently 8.0.31 is the latest version for MySQL.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Modify RDS DB

Under the Additional Configuration, for the DB parameter group choose the one that you created from the beginning of this tutorial for version 8.0.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Modify RDS DB Parameter group

Leave all other things as default and click Continue.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Modify RDS DB Continue

Under the Schedule modification section, for When to apply modifications, choose Apply immediately.

Finally click the Modify DB instance button.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Modify RDS DB Instance

Now your restored RDS database is upgrading status.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Modifying RDS DB Instance

In the meantime, login to your primary database and run the following commands to insert a row of data into the Persons table.

INSERT INTO <database_name>.Persons
VALUES ('0035', 'Evans', 'Cris', 'male');

SELECT * FROM <database_name>.Persons;

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime List Insert a Row into Primary DB

  • Wait around 10 minutes to upgrade the database.
  • Once the upgrade is completed, Select the restored database and select the Configuration section.
  • You can see the engine version is 8.0.31. So it is successfully upgraded.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime RDS DB Successfully Upgraded

  • Now we need to check if the table modification from the primary database is replicated into the restored upgraded database.
  • So, login to the secondary database and run the following command.
SELECT * FROM <database_name>.Persons;
  • Now you are able to see the table is updated with all details from the primary database.

Upgrade MySQL 5.7 RDS DB Instance to Latest Version with Zero Downtime Login to Secondary RDS DB

We successfully completed to upgraded RDS database with the latest version.

Final Step

To this point whenever changes or updates are made from the old version primary database are parallelly replicated to the secondary database which is upgraded to the latest MySQL version. But we need to set up the latest MySQL version database as the primary one. For this just point the Secondary database as the primary database inside the application code. Now the application will use the database which was upgraded to the latest version. Then delete the old MySQL version database. 

Yeah! All good…

We are successfully upgrading the RDS database to the latest MySQL version with nearly zero downtime. Yes, During this whole process there is no downtime for your application.

I hope you all enjoy this article.

Article written by:

Jerin is working as a Junior Cloud Automation Engineer at EasyDeploy Technologies Pvt. Ltd. He is writing terraform scripts that creates services on AWS cloud. He can able to handle CI-CD process using AWS CodePipeline.

Leave a Reply

Your email address will not be published. Required fields are marked *

back to top

Contact Us to save your AWS bill by 40%

X