Migrating from RDS Aurora MySQL to DO Managed MySQLh1

As said in part I, our RDS Aurora costs were about 40% of our cloud costs (about 4k USD monthly). We could potentially migrate to DO managed MySQL database and bring the database cost down to about 1k USD monthly.

Trying to auto-migrateh2

When creating a Managed Database in DO, one could potentially migrate from an existing MySQL server just by following some simple steps.

Well, for us, it didn’t work. The RDS user is not allowed to make some changes required for this. So it just errored out.

Oh well … It was worth a try.

A more traditional migrationh2

Since automatically migrating was out of the question, we needed to find another way. DigitalOcean documentation only mentioned that one could migrate using a mysql dump - but could we use an initial dump and then manually push binlogs (like absolute savages) to it?

Experimentation showed us that we can. So we could with very little downtime potentially replicate everything to the new db and then do the cut-over.

Experimentation also showed us that any other way (like making a slave out of the new DB) failed.

The plan became:

Upload an initial dump to the new DB.
Keep manually pushing binlogs from the old DB to the new DB untill we are ready for the switch-over.
When we are ready for the swich-over - disable all new writes.
Push last binlogs to the new DB.
Cut-over to the new DB.

Here are the steps we took and the snags we encountered.

1. Initial preph3

Create a DO droplet that will act as a central hub for this migration, being connected to both DO and AWS.

Install tools:

sudo apt update && sudo apt install -y mysql-client awscli doctl

2. Prep Aurorah3

Enable binlog replication in Aurora parameter group:

CALL mysql.rds_set_configuration('binlog retention hours', 24*7);

Verify:
```
SHOW VARIABLES LIKE 'log_bin';
```

2. Create an Aurora Read Replicah3

Create an Aurora read replica from the AWS console or CLI
This will be the replica that the DO droplet will connect to. So make sure that you make it accesible for the droplet.

3. Initial DB Dumph3

Use mysqldump from the droplet:

mysqldump -h <aurora-endpoint> -u admin -p mydb > dump.sql

4. Clean Aurora-specific SQL from dump file.h3

The dump file will be filled with aurora specific SQL comments and statements. So it will need some cleanup in order to get imported properly.

In our case, it was simply:
- Remove ENGINE=aurora statements.
- Strip out /*!50606...*/ Aurora-specific options.

5. Import into DO Managed DBh3

Create DO managed DB

In our case, we had to also disable sql_require_primary_key:

doctl databases configuration update <db_id> --engine mysql --config-json '{"sql_require_primary_key": false}'

Import dump:

mysql -h <do-endpoint> -u <user> -p mydb < aurora-dump.sql

6. Catch-Up with Binlogsh3

Use mysqlbinlog to replay recent changes:

# first get binlog
mysqlbinlog --read-from-remote-server \
  --host=<aurora-endpoint> --user=admin -p \
  --raw --stop-never mysql-bin.000001 >>
  binlog_unclean.sql

# clean binlog the same as we did the original dump
# then import the clean binlog
mysql -h <do-endpoint> -u <user> -p mydb < binlog_clean.sql

7. Cutoverh3

Stop writes to Aurora.
Apply final binlog replay.
Point applications to DO DB endpoint.
Validate data consistency.
Remove replica + migration droplet.

Final remarksh2

Not a very smooth way that’s for sure - but it did work. We wound up with a minute of downtime when we did the final cut-over - not bad, not great.

See you in part 3, where we replace AWS Lambda completely.