How to migrate a MySQL Database from DigitalOcean to AWS RDS

Cristian Llanos — Sun, 09 May 2021 15:58:39 +0000

Objective

At the end of this process, we'll have successfully moved our selected MySQL schemas onto another MySQL Database (to AWS RDS for instance). We'll make use of some automated bash scripts to ease our way.

Warning: the strategy shown in this guide will incur downtime for the clients of our databases while the migration process is running. Use this guide as a learning resource at your own risk.

If you are looking for the lowest migration downtime possible, I suggest having a look at AWS Database Migration Service.

Prerequisites

Before starting, make sure you have the following binaries on your server (versions used on this guide have been provided as a reference).

mysqldump (Ver 10.13 Distrib 5.7.33)
mysql (client Ver 14.14 Distrib 5.7.33)
bash (version 4.4.20(1)-release)
date (GNU coreutils 8.28)
Both Source and Destination Databases were previously created and are syntax compatible. Users and passwords have already been created for the migration, as well
You already know how to log in to your DigitalOcean server and are comfortable using a command-line text editor such as Vim, Nano, Emacs (whichever works for you on your server)

Database Credentials

Create a password file ~/.mysql_source_config and put the Source Database password using the following structure for mysqldump

[mysqldump]
password=the_password
host=localhost
port=3306

Set recommended permissions to protect the file from prying eyes. The command chmod 600 <file> gives read and write permission only to the owner of the file (the system user who creates it in this case).

chmod 600 ~/.mysql_source_config

If the previous command did not work for you it may be due to a lack of permissions from your logged-in user.

Let's do the same process and store the destination MySQL database password by creating a ~/.mysql_destination_config. The config name will be targeting mysql in this case.

[mysql]
password=the_password
host=https://aws-rds-host.com
port=3306

And don't forget about its permissions

chmod 600 ~/.mysql_destination_config

Backing up the database

We can do it by exporting all schemas, but we'll do it this time by providing which schemas we want to migrate (a_database_schema and another_database_schema)

mysqldump --defaults-extra-file="~/.mysql_source_config" -u root --databases a_database_schema another_database_schema > backup-$(date +"%Y_%m_%d_%H_%M_%S").sql

The previous command uses root as the source database username and it's reading the password from ~/.mysql_source_config. The output of this command will be a file containing the DDL of our schemas, tables, and inserts.

Why have you separated the password from the command?

Two reasons come to my mind:

Security wise it's preferable to have them in different files to prevent us from running commands with the password in it. Remember that all commands we run on the terminal will end up in the history file. And we don't want to leak our password by accident
It becomes easier to automate other commands/scripts that may need the same password by reusing the same file

Create a script directory

We need a place to put the scripts we are going to create

# Let's place ourselves in the user directory
cd

# And create ~/bin directory if it does not exist
mkdir bin

# We need to set appropriate permissions
# Allowing read, write and execute access only to the owner of the file
chmod 700 bin

We now have to tell the system where to find our executable files. We need to add the following line to our shell configuration (in this case ~/.bashrc)

export PATH=$PATH:$HOME/bin

Create a backup script

Let's create a runnable script ~/bin/mysql-backup that will help us test our backup command.

# Let's navigate inside our scripts directory
cd ~/bin

# And create a new file
touch mysql-backup

# Don't forget to protect the file
# This time we'll make it executable since this file will become our backup script
chmod 700 mysql-backup

Let's put our backup command inside this new file mysql-backup like this:

#!/bin/bash
# This command will output a file with the DDL to recreate our schemas, tables, and values

# If no parameter has been given, we'll use this name for the generated file
DEFAULT_FILE=backup-$(date +"%Y_%m_%d_%H_%M_%S").sql

mysqldump \
          --defaults-extra-file="~/.mysql_source_config" \
          --user=root \
          --databases \
            a_database_schema \
            another_database_schema \
          > ${1:-$DEFAULT_FILE}

Make sure --user and --databases schemas match yours.

Create a restore script

Let's create another runnable script that uses the previous command's output to restore it on another database: ~/bin/mysql-restore.

# Let's place ourselves in the previously created ~/bin directory
cd ~/bin

# And create a new file
touch mysql-restore

# Don't forget to protect the file
# We'll make it executable since this file will become our restauration script
chmod 700 mysql-restore

The contents of the file should look like this:

#!/bin/bash
# This command will take a SQL file and execute it on the Destination Database

mysql \
        --defaults-extra-file="~/.mysql_destination_config" \
        --user=migration \
        < $1

Make sure --user matches yours.

Create a migration script

Let's create a runnable script that will integrate the previous commands to do a synchronous migration ~/bin/mysql-migrate.

# Let's place ourselves in the previously created ~/bin directory
cd ~/bin

# And create a new file
touch mysql-migrate

# Don't forget to protect the file
# We'll make it executable since this file will become our restauration script
chmod 700 mysql-migrate