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:
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:
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"