Database migration from External MySQL to AWS RDS(Relational Database Service)

Enable binary logging in this MySQL instance

Under mysqld section in the configuration file.
Configuration file path for Ubuntu is at /etc/mysql/my.cnf
Sample configuration below.

[mysqld]
bind-address=0.0.0.0
binlog_format=row
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
expire_logs_days=10
max_binlog_size=100M
binlog_do_db=db_name

After the change is made in the configuration file, restart the MySQL service. Binary logging will then be enabled. This is to ensure that live replication of data can happen with DMS.(Database Migration Service by AWS)

Create an RDS instance with MySQL compatibility

While creating the RDS instance, make it publicly accessible. We need it to be publicly accessible for it to be accessed by SCT(Schema Conversion Tool)

Parameter groups is RDS’s way of managing server system variables for MySQL. After creation of the RDS instance, create a new parameter group.

Now in case you have stored functions and procedures that you need to be migrated as well, plus, you got some columns with blob data, then here are two parameters you gotta change. After creation of the new parameter group, change the following parameters

log_bin_trust_function_creators to 1
max_allowed_packet to 30000000 (or any other high value)

Save the changes made to the parameter group.
The first parameter is to ensure that RDS accepts function create statements without the characteristics parameters passed and also to ensure that the triggers in the database work.
The second parameter is by default set to a low value to catch incorrect packets. We set it to a high value for blob columns.

Now we can go to RDS dashboard and click Modify. Change the parameter group applied to the one that we just created. Choose the apply immediately option(which will result in some downtime for RDS which is okay since we are just setting it up) Reboot the instance, if you see ‘reboot-pending’ against the parameter group under configuration menu.

Setup SCT

Install SCT on your local system. It is GUI for schema conversion provided by AWS. Following is the link to the download page for SCT,
https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html
SCT uses JDBC connector for connecting to the source and target databases. Next step is to manually download the connector which can be found here,
https://dev.mysql.com/downloads/connector/j/
If you are using a Mac select ‘Platform Independent’ option under operating system for download. Once the zip is downloaded, extract it and there will be JAR file inside the directory. This file’s path has to be specified MySQL driver path when SCT wizard asks for it.

In AWS console, create a new IAM user for the SCT tool. We need API access for the SCT tool to apply schema changes and to run DMS tasks through it. Keep a note of the AWS access key and AWS secret key produced for the user. The services that will be used by this user are as follows,

  • AWS Database Migration Service
  • AWS Key Management Service
  • AWS Identity and Access Management
    The respective policies applied to this user will be,
    AmazonDMSVPCManagementRole, AWSKeyManagementServicePowerUser, IAMFullAccess

Start the SCT application. Under the global settings, add an AWS service profile and enter the AWS access and secret key. This will be used by SCT to access the AWS services.

Create a new SCT project, select source as MySQL and target as RDS with MySQL compatibility, enter credentials to access both the source and target databases.

Setup DMS

In the DMS Console on AWS, the first step is to create endpoints. Two endpoints are to be created, source and target. Source endpoint will point to the EC2 instance and target endpoint will point to the RDS instance. The procedure to create the endpoints is straightforward, the credentials to make the connections to both the EC2 and RDS instance have to be entered.

After the endpoints are created, a replication instance has to be spawned. This is where the migration will take place. After the creation of the replication instance, we create the migration task.

Run SCT to migrate the schema of the database

In the AWS SCT Project Window, we can see the on the left pane the MySQL source schema, Right click on the database name which you want to migrate, and select ‘Convert Schema’ option. Now on the right side pane RDS target schema is shown, right click on the database name and click on ‘Apply to database’ to migrate the schema to RDS.

Do a quick check to see whether the table schemas, stored functions, procedures and triggers are migrated to the RDS instance.

Run DMS

In the DMS console, create a new database migration task. We have to select the endpoints and the replication instance that were created before. In the migration type option, select ‘Migrate existing data and replicate ongoing changes’ This will ensure live replication after initial loading of data, which is done by tailing the binary log of the source database. Check the enable validation checkbox, DMS will verify all the data after loading it. This will take extra time but ensure data validity.

Note: We cannot migrate blob columns is because DMS only support blobs transfer with Oracle as Target database.

Manually copy table data for the tables with blob columns,

$ sudo mysqldump -u username -p jido table_name --hex-blob > table_name_dump.sql

The hex-blob flag ensures that the blob data is dumped in hexadecimal format in the dump file(s). Run both the dump files on RDS instance to restore the data.

Change connection strings in your application code.

After verifying the migration and making sure all data procedures, functions and triggers are migrated successfully as well. We can change the connection strings in the application code.

Stop DMS task and delete DMS replication instance in the DMS console.

Notes:

RDS can be connected to with MySQL workbench in two ways,

  • Through direct standard TCP/IP connection on the MySQL Port
  • Through an EC2 instance in the same VPC as the RDS instance, using TCP/IP over SSH.

Burstable instances will take far lesser time for data migration. RDS instance is able to take the workload when we import data into it.

If parameter groups changes seems to not work and you get an error while migrating functions/triggers, check configuration menu under your RDS instance. It must show ‘(in-sync)’ next to your parameter group. If it shows ‘reboot-pending’, reboot the RDS instance.

For access to the source database from SCT and from the DMS instance, create a new MySQL user using these statements:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pass';
CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
GRANT ALL ON . TO 'user'@'localhost';
GRANT ALL ON . TO 'user'@'%';
flush privileges;

Alternate migration plan (without live migration)

  • Migrate all schema through SCT
  • Right after migrating schema, drop the trigger which is migrated. As we don’t want the trigger to run when we run the dump file with the insert statements.
  • Dump the source data with the following options,
    --skip-triggers 
    --skip-add-drop-table
    --no-create-info
    --hex-blob
    

    Run the dump commands in the following manner,

    $ echo "SET FOREIGN_KEY_CHECKS=0;" >> dump.sql
    $ mysqldump --databases db_name --skip-triggers --skip-add-drop-table --no-create-info --hex-blob >> dump.sql
    $ echo "SET FOREIGN_KEY_CHECKS=1;" >> dump.sql
    
  • Run the resulting dump file on RDS instance
  • Create any triggers manually again, use the RDS migrated code produced from SCT.
  • Cutover from source database.