Knowledge

Stream MySQL backup directly to S3 bucket

#Databases

Backing up your database is very important. Creating a backup in the most efficient way is essential when you do not have unlimited disk space.

Published on August 24, 2022 · 1 minute read

  1. Offsite backup location
  2. How to stream the backup directly to S3

Offsite backup location

When creating backups for your database it is recommended to save the backups to an offsite storage facility. This way when you crash or delete the wrong files on your server, you'll always have the backups in place on another filesystem.

And if this is the case, then you'll probably want to know how to stream the backup directly to the external location! Because saving the file locally first would be very inefficient and also consumes a lot of diskspace.

So a simple mysqldump to save to local disk won't do it anymore. Let's have a look!

How to stream the backup directly to S3

First we start the backup using mysqldump, I have some preferred parameters on there, but you can change this to your own preferences.

Then we pipe the data directly to gzip to compress the data first, we use the highest (9) compression to keep storage costs low as possible.

After compressing we pipe it through pv to make sure we don't hit any bandwidth limits, we use here a limit of 1MB (1m) per second. In case you want faster uploads you should increase this.

After that we can safely stream the data to S3 using the s3cmd command line tool, which can be installed very easily using apt. We use the --acl-private option to make sure the backup can never be accessed publicly.

# Credentials
DATABASE="Your database name here"
PASSWORD="Your database password here"
BACKUP_PATH="File path for saving on the S3 bucket here"

# Backup command
mysqldump --password=$PASSWORD --add-drop-table --column-statistics=0 --extended-insert --no-tablespaces --single-transaction --skip-comments $DATABASE |
gzip -9 |
pv -L 1m -q |
s3cmd --acl-private put - s3://$BACKUP_PATH;

Subscribe to our newsletter

Do you want to receive regular updates with fresh and exclusive content to learn more about web development, hosting, security and performance? Subscribe now!