Coast To Coast with Mysql and Docker

Coast to Coast...Wait..What?

I was a big fan of World Wrestling Entertainment (WWE), before 2010 at least, when it was hardcore and action packed. There were some brutal finishing moves but nothing caught my fancy as much as the signature move of Shane McMahon which was aptly named Coast To Coast where in he executed a missile dropkick from one corner of the ring to the other.Little did I know then that this manoeuvre would be my inspiration for designing a solution engineering problem that I had to solve recently.

Problem Statement

In one of our projects that I lead, a design choice that we took was to Dockerize the entire stack in hope of 12 Factoring our system.There were definite merits that we gained that I will explore in some other posts.

The project’s topology had the usual suspects as components, a Static Frontend hosted on Nginx calling a RESTful API, which in turn used a Mysql database and a Redis server to store data. All of these components were running in their own Docker containers on their respective VMs. The production environment hosting the above topology also had a VPC setup with a public and a private subnet.The Frontend VM resided in the public subnet and the others resided in the private subnet.

Another decision we took ,we decided to go hit the ground running with a pure environment replication (meaning the aforementioned configuration was replicated in 2 AWS Regions) for the sake of high availability and redundancy. In this we had the live instance of the system running, fondly called Live for further references in the US-West-1 Region and the standby system running in the US-East-1 Region, henceforth called Standby.

The live traffic was directed to the Live environment in the US-West-1 and the Standby environment was to replicate it over a period of time.The Standby was expected to be groomed and usable in seconds ,if it ever was called to duty in case of outages of the Live.

This meant that the DB of the Standby had to be mirrored with the state of the DB of the Live when it came on its mark.
The above was easier said than done because of the 2 Mysql Servers counterparts running inside docker containers which were running on separate VMs inside private subnets of separate VPC of two different AWS Regions.

The corollary to the above was that a database backup and restore protocol would be implemented and tested. In my experience, database backups often would be taken, but rarely tested.Due to the aforementioned setup, our Backup and Restore mechanism had to be on point.

Was it a good problem to solve?Something like killing two birds with a single stone?Yep!.

Candidate Solutions

Since we were using Docker already, I figured Docker was the best bet for the solution. I explored Dockerhub to check whether the community had created a solution ready that was just a drop in. There were a couple of promising ones :

1.Tutum’s excellent Mysql backup image: It created a .sql backup on the host’s volume.

2.Tutum’s Dockup: Which tar-balled the volumes from any container and uploaded to a S3 bucket.

The above images though excellent were complement of each other. The first image provided the backup in the way I wanted a .sql file but did not upload it to a offline location (s3) and the second one uploaded to s3 but did not produce a .sql file but a .tar.gz of the /var/lib/mysql folder of the container as a backup.

I wanted a combination of both with a flexible way to control my backup and restore mechanism.

Solution Design

Inspired by the above but due to their constraints, I set out to create a utility which would be packaged as a docker container that could just work and present us with an ‘offline’ backup and restore mechanism.
The pieces of this solutions were:

For Backup

1.Run mysqldump against a database.

2.Get the .sql uploaded to a a well known location in s3 for easier retrieval.

For Restore

1.Retrieve the dump from the well known location.

2.Run the restore via mysql command line to restore the data.

Another choice that I made was to run this entire utility as a Docker container. But since every Docker container has a single process running, achieving the two above together would require a polymorphic utility. That meant wrapping up the two commands in some kind of wrapper. Due to its spartan nature(lack of VM) at runtime, I chose to code this wrapper in Golang.

Implementation

As per the above specifications,I came up with this.

The Go code when built yields a binary file that is packaged via Docker and pushed to Dockerhub here.

Prerequisites

1.To run this as a Docker image,you need to have docker installed on your machine.

2.Of course, since this is a Go binary you can run it as is by setting the environment variables as mentioned in the Readme.

3.You would need valid s3 credentials for an IAM user with proper bucket rights.

4.Also the machine where this utility is being run needs to have access to the internet, and the database should be reachable from it.

Usage

To access it just have to do the following command.

 $ docker pull kaddiya/mysql-backup-restore:2

Note : Not a fan of the latest tag.This library will be revisioned numerically.

The library can be used in two setups:

1. Where the database instance to backup is accessible at sample.db.com and the database instance to restore is accessible at standby.db.com

Usage for Backup

$ docker run --env dumper_db_host=sample.db.com --env dumper_db_port=3306 --env dumper_db_user=user --env dumper_db_password="pswd" --env dumper_db_name="sample" --env s3_access_key=access_key --env s3_secret_key="secret_key" --env s3_bucket_name="sample-db-backups" --env dumper_mode=BACKUP --env dumper_s3_region="us-west-1" --env path_in_bucket="/data" kaddiya/mysql-backup-restore:2

Usage for Restore

$ docker run --env dumper_db_host=standby.sample.db.com --env dumper_db_port=3306 --env dumper_db_user=user  --env dumper_db_password="pswd" --env dumper_db_name="sample" --env s3_access_key=access_key  --env s3_secret_key="secret_key" --env s3_bucket_name="sample-db-backups" --env dumper_mode=RESTORE  --env dumper_s3_region="us-west-1" --env path_in_bucket="/data" kaddiya/mysql-backup-restore:2

2. Where the database instance to backup is running in a docker container named container.db.com and the database instance to restore is running in a docker container named container.standby.db.com.

Usage for Backup

$ docker run --env dumper_db_host=sample.db.com --env dumper_db_port=3306 --env dumper_db_user=user --env dumper_db_password="pswd" --env dumper_db_name="sample" --env s3_access_key=access_key --env s3_secret_key="secret_key" --env s3_bucket_name="sample-db-backups" --env dumper_s3_region="us-west-1" --env path_in_bucket="/data"  --env dumper_mode=BACKUP --link container.db.com:container.db.com kaddiya/mysql-backup-restore:2

Usage for Restore

$ docker run --env dumper_db_host=standby.sample.db.com --env dumper_db_port=3306 --env dumper_db_user=user --env dumper_db_password="pswd" --env dumper_db_name="sample" --env s3_access_key=access_key --env s3_secret_key="secret_key" --env s3_bucket_name="sample-db-backups" --env dumper_s3_region="us-west-1" --env path_in_bucket="/data"  --env dumper_mode=RESTORE --link standby.db.com:container.standby.db.com  kaddiya/mysql-backup-restore:2

A Note on whats happening here

The library takes the liberty of creating 2 folders in the s3_bucket_name/path_in_bucket path.

These two folders are
- latest
- archived

The backup and restore mechanisms are complements of each other. For the Yin of backup there is the Yang of restore. This library tends to accentuate this in its polymorphic way. The switch is the environment variable mode.

1.In the BACKUP mode it uploads the latest backup of the database at sample.db.com to
s3_bucket_name/path_in_bucket/latest/sample-latest-backup.sql: The latest backup
s3_bucket_name/path_in_bucket/archived/timestamped-sample-backup.sql: The timestamped backup

2.In the RESTORE mode it downloads the latest backup from s3_bucket_name/path_in_bucket/latest/sample-latest-backup.sql and restores it at the database instance running at standby.sample.db.com

It currently doesn’t have the ability to take in a particular filename to restore the database from a file in the archived folder.
This will be useful for restoring a database to a point in time.

The examples are just a starting point on how to use this utility. With different combinations of the dumper_db_host and dumper_db_name and mode and path_in_bucket you can achieve other things as well like:
- Having a dump of a particular environment on a local development machine to debug an issue.
- Reseting the database to a particular baseline at a point in time

Merits

1.This library can be used to abstract away the complexity of backup and restore of databases under any topology.

2.Due to the containerization, execution of this library is extremely cheap.

3.With no assumptions on the context of execution of the library, the control for execution is entirely in your hands.You can run the backup and the restore scripts on demand, daily, hourly etc.

Conclusion

With this utility I was able to migrate my data on the Live from one coast (US-West-1) by running the library in the BACKUP mode and restored the standby on US-East-1 by running it in the RESTORE mode.

I synchronized the execution on the VMs by introducing ntp and made the restore run one minute after backup.

In case of a outage on Live,I can run the scripts on demand and have the Standby groomed to the latest snapshot trivially.

Please take this for a spin and let me know, how does it work for you?

Also a big thanks to Tom Bishop for guiding me to start down this solution lane!

[EDIT-1]: I have pushed a changeset which ensured that the backup would include the procedures that were created as well. Unless we specify the --routines flag the database dump doesnt include the procedures ;) The tag to use now is 2. With this the library dumps out all the procedures that were created.