mysql-backup-restore

Service to backup and/or restore mysql databases to/from S3 and optionally to B2

How to use it

1. Create an S3 bucket to hold your backups
2. Turn versioning on for that bucket
3. (Optional) Create a B2 bucket to hold your backups
4. Supply all appropriate environment variables
5. Run a backup and check your bucket(s) for that backup

Environment variables

MODE Valid values: backup, restore. Restores are implemented only from S3.

DB_NAMES list of the database names

MYSQL_HOST hostname of the database server

MYSQL_USER user that accesses the database

MYSQL_PASSWORD password for the MYSQL_USER

MYSQL_DUMP_ARGS (optional) additional arguments to the mysqldump command, e.g., --max_allowed_packet=50M

S3_BUCKET e.g., s3://database-backups NOTE: no trailing slash

It's recommended that your S3 bucket have versioning turned on. Each backup creates a file of the form dbname.sql.gz. If versioning is not turned on, the previous backup file will be replaced with the new one, resulting in a single level of backups.

AWS_ACCESS_KEY used for S3 interactions

AWS_SECRET_KEY used for S3 interactions

B2_BUCKET (optional) Name of the Backblaze B2 bucket, e.g., database-backups. When B2_BUCKET is defined, the backup file is copied to the B2 bucket in addition to the S3 bucket.

It's recommended that your B2 bucket have versioning and encryption turned on. Each backup creates a file of the form dbname.sql.gz. If versioning is not turned on, the previous backup file will be replaced with the new one, resulting in a single level of backups. Encryption may offer an additional level of protection from attackers. It also has the side effect of preventing downloads of the file via the Backblaze GUI (you'll have to use the b2 command or the Backblaze API).

B2_APPLICATION_KEY_ID (optional; required if B2_BUCKET is defined) Backblaze application key ID

B2_APPLICATION_KEY (optional; required if B2_BUCKET is defined) Backblaze application key secret

B2_HOST (optional; required if B2_BUCKET is defined) Backblaze B2 bucket's Endpoint

Docker Hub

This image is built automatically on Docker Hub as silintl/mysql-backup-restore.

Playing with it locally

You'll need Docker Engine with the Docker Compose plugin and Make.

1. cd .../mysql-backup-restore
2. Upload test/world.sql.gz to the S3 bucket.
3. make db # creates the MySQL DB server
4. make restore # restores the DB dump file
5. docker ps -a # get the Container ID of the exited restore container
6. docker logs <containerID> # review the restoration log messages
7. make backup # create a new DB dump file
8. docker ps -a # get the Container ID of the exited backup container
9. docker logs <containerID> # review the backup log messages
10. make restore # restore the DB dump file from the new backup
11. docker ps -a # get the Container ID of the exited restore container
12. docker logs <containerID> # review the restoration log messages
13. make clean # remove containers and network
14. docker volume ls # find the volume ID of the MySQL data container
15. docker volume rm <volumeID> # remove the data volume
16. docker images # list existing images
17. docker image rm <imageID ...> # remove images no longer needed