Backing up MySQL on DigitalOcean

Simon Bennett
Simon Bennett · Oct 02, 2019
Backing up MySQL on DigitalOcean Artwork

Did you know that SnapShooter offers support for backing up MySQL on DigitalOcean now? Learn more about MySQL backups

If your Droplet has an extremely active database it's worth exporting the data into a static file just before taking a snapshot, this way you guarantee your data is safely backed up and restorable without corruption.

For this guide, we are going to base it on using MySQL but this is possible for any database where you can export into a static file.

Export MySQL Database

For exporting we will use mysqldump the command if very easy to run

mysqldump -u [username] -p [database name] > [database name].sql

Create Backup User

I recommend you create a read-only user for reading the database you want to export.

Login to MySQL terminal

mysql -u root -p

Create user granted to just one database via localhost

GRANT LOCK TABLES, SELECT ON DATABASE.* TO 'mysqldump'@'localhost' IDENTIFIED BY 'PASSWORD';

Replace Database with your database name and password with a strong random password, you could also rename mysqldump to a different user.

If you want the user to have read access to any database

GRANT LOCK TABLES, SELECT ON *.* TO 'mysqldump'@'localhost' IDENTIFIED BY 'PASSWORD';

Once completed you can exit MySQL, type exit and enter.

Create Password File

As the user you are going to use for cron we need to make a file for the mysqldump users’ password to be stored. This enables us to login without having to enter the password into the terminal.

  • Create a .my.cnf file in the users home directory

~/.my.cnf

  • Copy the content below and replace with your mysql username and password.

[mysqldump] user=mysqldump password=secret

  • Now change the file permissions to 600 to prevent other users from reading it

chmod 600 ~/.my.cnf

Once this is in place we can run a test to make sure it’s running well.

mysqldump -u mysqldump DATABASE > dump.sql

The dump file should now contain a copy of your data, you can now remove that file once you check because when we create the dump we will also use gzip to compress it to save disk space and storage costs.

Crontab automation

Our command is ready to run on cron we just need to program the frequency of backups.

mysqldump -u mysqldump DATABASE | gzip > dump.sql.gz

We recommend you take the mysql dump just before SnapShooter does your backup. Next we edit our crontab

crontab -e

And add the following config

50 23 */2 * * mysqldump -u mysqldump DATABASE | gzip > dump.sql.gz >/dev/null 2>&1

In this example, we will take a mysqldump at 23:50 every day. Ten minutes before SnapShooter is due to take its nightly backup. Cron Generator is a great tool for build the cron schedule and you can adjust to get the timing right with your Droplet and the timezone your based in. If you database takes a long time to backup you may wish to adjust the time to be a bit earlier.

Remember each day the dump will be overridden which is okay as SnapShooter will create a new snapshot of the server with the most up to date MySQL backup on it.

Did you know that SnapShooter offers support for backing up MySQL on DigitalOcean now? Learn more about MySQL backups