Migrate from RDS MySQL to Aurora MySQL in near zero downtime

Introduction

In this tutorial, we will look at how to migrate from Amazon RDS MySQL to Amazon Aurora MySQL with minimal downtime. As with any database migration, there are several options. For migrating data from a MySQL DB Instance to an Amazon Aurora MySQL DB Cluster, we recommend to use a special type of node called an Aurora Read Replica for the source MySQL DB instance. Amazon RDS uses the MySQL DB engines' binary log replication functionality and updates made to the source MySQL DB instance are asynchronously replicated to the Aurora Read Replica. As replication lag between source DB instance and Aurora Read Replica approaches zero, redirect your client applications to the Aurora Read Replica, and make the Aurora Read Replica a standalone Aurora MySQL DB cluster. For more information, refer Amazon Aurora documentation.

The methodology and steps discussed in this tutorial are applicable to any application’s database that resides on Amazon RDS MySQL DB instance and needs to be migrated to Aurora MySQL DB cluster.

The tutorial is not within the free tier and will cost you less than $1 provided you follow the steps in the tutorial and terminate your resources at the end of the tutorial.

What you will learn

  • How to use RDS Read Replicas to migrate from RDS MySQL to Aurora MySQL with minimal downtime
  • How to promote a read replica to become the new standalone Aurora MySQL DB cluster
  • How to connect your application to the new DB cluster

 AWS Experience

Intermediate

 Time to Complete

10 - 20 minutes

 Cost to Complete

Less than $1

 Services Used

Pre-requisites

  1. Existing AWS account, if you don’t have an AWS account then you can create new account to get started.
  2. Amazon EC2 Key Pairs are used to connect securely to your EC2 Linux-based instances using SSH. If you already have a key pair, you can reuse it for this tutorial. If you don’t have key pair then follow the instructions to create new key pair in your preferred region, in which you will complete this tutorial.
  3. It is recommended that you complete this tutorial in Default VPC. Each AWS account is automatically created with a Default VPC in each region, containing a basic network configuration, where resources can be provisioned in any of that region’s availability zones, and can have direct access to the Internet. In rare circumstances, customers can re- purpose these default VPCs, or delete them entirely. If you cannot find the default VPC in your account and region, you can recreate it by following the steps listed here. 

Implementation

  • 1.1 The CloudFormation.yaml Template on the GitHub page will launch an Amazon EC2 instance of type t2.micro with latest Amazon Linux 2 OS, bootstrap Apache/PHP, and install a simple address book web application. The template will also create an Amazon RDS MySQL database instance in free tier, i.e. of type db.t2.micro and with no Multi-AZ setup or read replicas. The WebTier Security Group will allow only SSH and HTTP connections to the web server (EC2 instance), and the DBTier Security Group will only allow the WebTier Security Group to initiate database connections to the RDS DB instance over the TCP port 3306.

    Go to the Github link and download the files as shown.

    Click the Code option and then click on Download ZIP. The zip folder will be downloaded by your browser.

    Go to the downloaded folder location on your machine and then unzip the folder. You will have all files as shown above.

    (click to zoom)

    1.2 - Open the AWS CloudFormation Console and sign in with your AWS account credentials. Choose the Region drop-down and select the appropriate AWS Region. This tutorial uses the US West (Oregon) Region. Click on Create stack.

    (click to zoom)

    Select Template is ready, and choose Upload a template file as the source template. Then, click on Choose file and upload the CloudFormation.yaml (downloaded in previous step). Click Next.

    (click to zoom)

    Populate the form as with the values specified below, and then click Next:

    Stack Name: A unique identifier without spaces

    DBInstanceID: RDS DB instance identifier. It defaults to rdsdb

    DatabaseName: Database name. It defaults to mydb

    KeyName: The existing key pair in this account and region

    LatestAmild: Don't change this parameter, it will install the latest Amazon Linux 2 OS AMI

    MasterUsername: Choose subnet to deploy the instances

    MasterPassword: Password for MySQL database access

    SubnetID: Choose subnet to deploy the instances

    VPC: Choose VPC (Above subnet should be of this VPC)

    Note: The resources that are created here will be prefixed with whatever value you specify in the Stack Name. Please specify a value that is unique to your account.  

    Once you click Next, on the Stack Options page, accept all of the defaults and click Next. 

    On the Review page, click Create stack.

     

    (click to zoom)

    1.3 - At this point, you will be directed back to the CloudFormation console and will see a status of CREATE_IN_PROGRESS. Please wait here until the status changes to COMPLETE.

    (click to zoom)

    You can click on the refresh icon to see the progress of resources creation.

    (click to zoom)

    Once CloudFormation status changes to CREATE_COMPLETE, go to the Outputs section.

    Make a note of the Output values from the CloudFormation environment that you have launched, as you will need them for the remainder of the lab.

    For this tutorial setup, the values are:

    (click to zoom)

    1.4 – Open a new browser tab and navigate to the web server interface by entering the EC2 instance’s Public DNS name (from preceding step) into the browser. You should see a website that looks like the example.

    Click on the RDS tab.

    (click to zoom)

    Enter the credentials and database name as shown. The RDS Endpoint name was noted in previous step Click the Submit button.

    (click to zoom)

    You will see a brief message of a connection attempt by the application to connect to the database on the RDS DB instance, as shown.

    You will review the contents of file rds.conf.php

    (click to zoom)

    Upon successful connection completion, you will be redirected to a simple address book application displaying all of the information from the database.

    (click to zoom)

    Feel free to play around with the address book and add/edit/remove content from your RDS database by using the Add Contact, Edit, and Remove links in the Address Book. The changes made to the address book in this tutorial are shown.

    (click to zoom)

    1.5 - Navigate to the EC2 console, choose the EC2 instance (Webserver) and choose Connect.

    (click to zoom)
    In the Connect to your instance dialog box, choose EC2 Instance Connect (browser-based SSH connection) and then choose Connect. A browser window opens displaying the EC2 instance command line interface (CLI).
    (click to zoom)
    From the command line, navigate to the directory /var/www/html. List the files and directories by issuing ls command. View the content of the file rds.conf.php by using more command. This configuration file has captured the information about the Database Endpoint DNS name, credentials, and the name of database, which you had entered in step 1.4 while connecting to the database from the application. You will also revisit this file in Step 5.
    (click to zoom)

  • Amazon RDS uses the MySQL DB engines' binary log replication functionality to create a special type of DB cluster called an Aurora Read Replica for a source MySQL DB instance. Updates made to the source MySQL DB instance are asynchronously replicated to the Aurora Read Replica. In this step, you will create Aurora Read Replica and monitor the replication progress.

    2.1 - Open the Amazon RDS Console, and from the left navigation pane, choose Databases, and then choose the MySQL DB instance that you want to use as the source for your Aurora Read Replica. Under the Actions drop down, choose Create Aurora read replica.
    (click to zoom)
    2.2 – For the purpose of this tutorial, you will configure the Aurora Read Replica with default values and give the DB instance identifier an appropriate name as shown in the example.
    Choose Create read replica
    (click to zoom)
    (click to zoom)
    (click to zoom)
    (click to zoom)
    (click to zoom)
    2.3 – Aurora DB Cluster is created with 2 instances: writer and reader. After few minutes the status of the DB cluster changes to Available. Note that the source RDS DB Instance rdsdb Role changes to that of Master (from the role of Instance as seen in step 2.1)
    (click to zoom)
    2.4 – Click on the Aurora DB cluster to display its details. Click on Configuration tab, under Database section confirm the DB cluster role is Replica and the Replication source is the ARN of RDS MySQL DB Instance rdsdb.
    (click to zoom)
    2.5 - Click on Connectivity & security tab, under the Endpoints section note down the Endpoint name of Writer instance type – for this tutorial it is aurora-db-cluster.cluster-cp94ll5qcjxh.us-west-2.rds.amazonaws.com
    (click to zoom)

  • Before you can promote the Aurora Read Replica to a stand-alone DB cluster (Step 5) and redirect your application to the endpoint for the Aurora Read Replica (Step 6), stop any write transactions from being written to the source RDS MySQL DB instance. The way to do this is by modifying the read_only parameter in the parameter group assigned to the RDS DB instance.

    3.1 – From RDS console, navigate to Parameter group, and select the custom Parameter group (that was created by CloudFormation template) and for Parameter group actions, choose Edit.
    (click to zoom)
    3.2 – Under Parameters section, search for read_only parameter. The default value is{TrueIfReplica}. Explicitly set this value to 1, which converts the instance into a read-only mode.
    (click to zoom)
    3.3 – Choose Save changes. This parameter has a dynamic apply type, which means that its setting takes effect immediately and doesn’t require a reboot.
    (click to zoom)
  • 4.1 - After the Amazon Aurora cluster has been created and loaded with the initial set of data, Amazon RDS service establishes binlog replication from the RDS MySQL instance to the Amazon Aurora cluster. You can monitor this replication process by observing the CloudWatch metric Aurora Binlog Replica Lag on the Amazon Aurora cluster. The AuroraBinlogReplicaLag metric is defined as the amount of time a replica DB cluster running on Aurora with MySQL compatibility lags behind the source DB cluster. Choose Monitoring tab and under CloudWatch section, search for binlog metric as shown.
    (click to zoom)
    4.2 – The CloudWatch metric Aurora Binlog Replica Lag gives you a high-level view of the binlog replica lag, you can find a more precise measurement by logging in to the newly created Amazon Aurora cluster. To do so, use the MySQL client and run the command show slave status\G. This command returns a lot of very useful information, but the specific metric that we want is Seconds_Behind_Master. When this metric reaches 0 i.e. there is no replication lag, your newly created Amazon Aurora cluster is in sync with your original RDS MySQL DB instance.
     
    Follow the instructions as shown in Step 1.5 to SSH into your EC2 instance and then connect to your Aurora DB Cluster’s Writer Endpoint (noted in Step 2.5) a urora-db-cluster.cluster-cp94ll5qcjxh.us-west-2.rds.amazonaws.com and execute the command show slave status\G
     
    The Amazon Aurora cluster has the same master user name and master password as the source RDS DB instance.
     
    At this point, the migration of database from the source RDS MySQL DB instance to Aurora DB Cluster is completed.
    (click to zoom)

  • In this step, you will promote your Aurora Read Replica to be a Standalone Aurora Cluster. Promotion should complete fairly quickly, and you can read from and write to the Aurora Read Replica during promotion.

    5.1 – To promote an Aurora Read Replica to an Aurora DB cluster, navigate to Amazon RDS console. In the left navigation pane, choose Databases. Choose the DB cluster aurora-db-cluster for the Aurora Read Replica. For Actions, choose Promote.

    (click to zoom)
    Choose Promote Read Replica.
    (click to zoom)
    5.2 – Confirm that the promotion process has been completed. Click on the Aurora DB cluster, choose Logs & events tab, scroll down to Recent events and confirm that there is a Promoted Read Replica cluster to stand-alone database cluster event logged.
    (click to zoom)
    (click to zoom)
    The Aurora DB cluster role changes to Master as seen in Configuration tab under Database section. Prior to the Promotion event, the role was Replica as seen in Step 2.4.
    (click to zoom)
    5.3 – You will also notice that source RDS MySQL DB instance rdsdb is no longer in Master role (as seen in Step 2.3) and now has the role of Instance.
    (click to zoom)
    5.4 – After the promotion is complete, the source RDS MySQL DB Instance and the Aurora Read Replica are unlinked, and you can safely delete the RDS DB instance if you want to. AWS Recommends to take snapshot of production RDS DB Instance prior to its deletion.
     
    In this tutorial, you are going to stop the RDS MySQL DB instance without creating snaphot. In the left navigation pane, choose Databases. Choose the source RDS DB instance rdsdb. For Actions, choose Stop.
    (click to zoom)
    Choose No for Create snapshot?
    Then, choose Yes, Stop Now.
    (click to zoom)
    5.5 – You will see the RDS DB instance rdsdb is now in Stopped state.
    (click to zoom)

  • The Application is now ready to start writing to Aurora Standalone Cluster.

    6.1 – SSH into your web server EC2 instance, navigate to the directory /var/www/html. You will modify the file rds.conf.php by executing command sudo vi rds.conf.php
    (click to zoom)
    6.2 – Change the RDS_URL parameter to Aurora Cluster Writer Node DNS name - aurora-db-cluster.cluster-cp94ll5qcjxh.us-west-2.rds.amazonaws.com (noted in Step 2.4). Then save the file.
    (click to zoom)
    6.3 – Confirm that the changes are in effect, by reading the file again – cat rds.conf.php
    (click to zoom)
    6.4 – Open a new browser tab and navigate to the web server interface by entering the EC2 instance’s Public DNS name - ec2-34-219-103-10.us-west-2.compute.amazonaws.com into the browser. Choose the RDS tab. The web server connects to the Aurora Standalone Cluster and displays the same address book information from the database.
    (click to zoom)
    6.5 – Confirm that you can also make the write transactions to this database hosted on Aurora Standalone Cluster. Feel free to play around with the address book and add/edit/remove content from your database by using the Add Contact, Edit, and Remove links in the Address Book. The changes made to the address book in this tutorial is shown.
     
    Note: Some storage constraints are not factored in this tutorial and should be considered for your production migrations. For more details, refer to Migrating Your Databases to Amazon Aurora white paper and documentation.
    (click to zoom)

  • It is a best practice to delete instances and resources that you are no longer using so that you don’t keep getting charged for them. Remember that all running Amazon EC2 and RDS resources will incur an hourly on-demand charge.

    7.1 - Delete all Aurora database instances created in this tutorial from the RDS Console.

    a. Select the Aurora Reader instance, click on Actions, then Delete. Type in ‘delete me’ in the field. Then press the Delete button.

    b. Select the Aurora Writer instance, click on Actions, then Delete. Uncheck ‘Create final snapshot’ and ‘Retain automated backups’ (For production database you want to create the final snapshot in the event you need to restore the database). Click on ‘I acknowledge’ checkbox and type in ‘delete me’ in the field. Then press the Delete button. This will delete the Aurora DB Cluster as well.

    7.2 - Delete the EC2 instance created during the workshop. In the EC2 Console main page, click on Running Instances; or click on Instances on the left menu. Click on the checkbox next to the EC2 instance created for the web app. Then click on Actions, then Instance State, then Terminate. Confirm on Yes, Terminate when prompted.

    7.3 - Delete the EC2 keypair (if you created new keypair for this tutorial). Click on Key Pairs on the left menu under the EC2 Console. Click on the checkbox next to the keypair created. Then Delete.

    7.4 – Navigate to AWS CloudFormation Console and then select the CloudFormation Stack that you created to deploy the resources for this tutorial. Click on the Delete button from the top right corner. CloudFormation will automatically remove all resources that it launched earlier.
    (click to zoom)
    Click Delete stack and Check the CloudFormation console to ensure the stack that you selected is deleted.
    (click to zoom)

Was this page helpful?

 Feedback

Congratulations!

In this tutorial, you learned how the Aurora Read Replica can be used, to achieve a near zero downtime, during migration from source MySQL DB instance to Amazon Aurora MySQL Cluster. You also learned how to monitor the replication, to stop writing to RDS MySQL DB instance after replication lag approaches zero, and to convert the Aurora Read Replica into a standalone Aurora MySQL DB cluster.

Next Steps

Now that you have learned how to migrate from RDS MySQL Database to Amazon Aurora MySQL Database cluster, you can learn more in-depth about data migration to Aurora by referring to the documentation.