This article will document how to migrate a MySQL database instance on an Elastic Compute Service (ECS) to a RDS instance running on the cloud. As an example we will migrate a WordPress MySQL database hosted on a Docker instance into a Alibaba Cloud MySQL RDS instance.

To find out how to setup a MySQL RDS instance see article:

Setup MySQL RDS instance.
Create, setup and configure a MySQL ApsaraDB for RDS instance.

Backup Container MySQL instance

We will be using command 'mysqldump' to back up a MySQL database instance running on a docker instance. This command will generate backup a scripts that will restore the database and its data. For large volume backups, other backup strategies should be used.

Below is a script (backupMySql.sh) used to invoke 'mysqldump' on a container MySQL database instance:

docker ps

# Invoke mysql command console and execute mysqldump
docker_image=$1
mysql_user=$2
mysql_pw=$3
mysql_db=$4
docker exec $docker_image /usr/bin/mysqldump -u $mysql_user --password=$mysql_pw $mysql_db > $(echo $(date '+%Y%m%d%H%M%s')).sql

# list the backup file generated
find . -name "*.sql"
Contents in backupMySql.sh

The script above can be run using the following command:

. ./backupMySql.sh 'docker_image_id' 'mysql_name' 'mysql_password' mysql_database

Using an existing MySQL Docker

You can only have one docker instance bound to the MySQL port. If you already have a MySQL instance running, you will not be able to run another MySQL docker instance that uses the same port.

It is still possible to create a backup script by running 'mysqldump' on the running Docker instance and copying the generated scripts out of the docker instance. Below is a script showing how this can be done.

# remote into Docker instance
docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sqldocker 

# copy the generated scripts out of the Docker instance
cp CONTAINER:/usr/bin/backup.sql localbackup_backup.sql

example:docker cp d6d9899a687d:/usr/bin/backup.sql mssqlbackup.sql

Restore database onto RDS

use the following command to restore the scripts generated from 'mysqldump'.

cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql 
-u [user] -password=[pw] -h[db_host] DATABASE

Verify RDS connection

To verify the MySQL RDS connection you can try to connect to the instance from the ECS using the internal endpoint. (assuming the ECS is in the same VPC)

The command below uses an MySQL Docker container to execute a MySQL command to list all users in the database. If you a valid connection and required permissions, you will be able to  see a list of users.

docker run mysql:5.7 mysql -h[...].mysql.rds.aliyuncs.com -u[db_user] -p[db_pw] -D[db_instance] -e"select Host, User from mysql.user"
Running a MySQL container to execute command against a RDS database