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