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.

• 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 

 

You can see the parameter group could be created.

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

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

Click the Save changes button.

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

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

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.

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.

Select the standard create option. For Engine choose MySQL 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.

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.

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.

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.

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

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.

Scroll down to the last, click Create database.

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

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.

Select the security group and select inbound rules.

Click Edit inbound rules button.

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.

Create Tables and add Contents

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

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

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.

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.

Create another RDS DB Instance from the Primary Instance.

Select the main MySQL RDS database.

Select Modify and click Restore to point in time.

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.

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

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.

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

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

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.

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.

Enter a name to your replication instance.

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

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.

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

Replication instances take around 15 to 20 minutes to create.

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

Create Source and Target Endpoints

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

Then click the Create endpoint button.

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

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.

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

Now your source endpoint is in Active status.

Click Create endpoint button to create target endpoint.

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

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.

• 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

Now our two endpoints are Active.

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;

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.

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

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

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

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

Scroll down to the bottom and click Create task.

Now your Database migration task will be in starting status.

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.

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.

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;

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

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.

Leave all other things as default and click Continue.

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

Finally click the Modify DB instance button.

Now your restored RDS database is upgrading status.

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;

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

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

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.