How to migrate a MySQL Database from DigitalOcean to AWS RDS
Tags: aws, digitalocean, database, devops
Published: 2021-05-09 15:58:39

## 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](https://aws.amazon.com/es/dms/).

## 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`

```config
[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).

```bash
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.

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

And don't forget about its permissions

```bash
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`)

```bash
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

```bash
# 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`)

```bash
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.

```bash
# 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:

```bash
#!/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`.

```bash
# 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:

```bash
#!/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`.

```bash
# 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
```

The contents of the file should look like this:

```bash
#!/bin/bash
# A backup file will be created with the SQL needed for the migration.
# Feel free to delete it after the migration has run

# Append the timestamp to the name of the generated file
BACKUP_FILE=backup-$(date +"%Y_%m_%d_%H_%M_%S").sql

echo Making backup file from Source Database
mysql-backup $BACKUP_FILE

echo Restoring on Destination Database
mysql-restore $BACKUP_FILE

echo Done!
```

## Migration Strategy
1. Add password configurations for source and destination databases on the source server. Make sure all credentials are working properly
2. Place migration commands on a PATH directory (we created one ~/bin). Make sure you have already tested the commands are working as expected and there are no permission errors when running them.
3. Prepare a list of environment variables to update on Database client servers if applicable
4. Turn off Source Database writes on all clients. Run `php artisan down` on Laravel projects
5. Place your terminal in an empty directory (preferably), run `mysql-migrate` and wait until it finishes
6. Point all clients to the Destination Database host using their new credentials. Change environment variables on Database clients if needed. If applicable, update `.env` file on Laravel projects
7. Turn back on Database writes. This time pointing to the destination database. Run `php artisan up` on Laravel projects