Backing up MySQL on DigitalOcean

If your Droplet has an extremely active database its worth exporting the data into a static file just before taking a snapshot, this was 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.