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.

 

Are DigitalOcean snapshots destroyed after restoring them?

No snapshots are not destroyed when a snapshot is restored. They are kept for as long as your retention policy is set for.

If you are using SnapShooter to manage your droplet and you need to restore your Digital Ocean it can be worth pausing backups to make sure your old backups are not destroyed until your happy you have fixed the issue.

How to pause backups

  1. Login to SnapShooter
  2. Select the droplet you wish to pause

  3. On the top right press the pause symbol
  4. You should see the confirmation message

    Your droplet snapshot schedule have been paused

  5. Your droplet is now paused and will have a status of paused. No more backups will be scheduled and no old backups will be removed.

How to unpause/resume Backups

  1. Login to SnapShooter
  2. Select the droplet you wish to resume
  3. On the top right press the play symbol
  4. You should see the confirmation message

    Your droplet snapshot schedule have been resumed

  5. Your droplet is now resumed and will have a status of activated. Backups will continue on scheduled and old backups will be removed based on your retention policy.

Does DigitalOcean shutdown the droplet to take snapshot or backup?

The simple answer is no, DigitalOcean does not require you to turn off your droplet to take a snapshot or backup.

In the past, DigitalOcean did require the server to be shutdown first. You used to have to shut down, take snapshot and reboot. Which is a process anyone running a server does not want to do, luckily now you can keep them running.

SnapShooter’s Process

At SnapShooter we do not turn on or off servers so we will take a snapshot in the current state your server is in. You may find a snapshot takes a little bit longer to take a snapshot while online. Snapshots also take a longer if your server is under heavy load. From experience, we don’t notice the servers performance drop while taking snapshots.

 

Can you download DigitalOcean Snapshots?

digitalocean-snapshot

Sorry, you can’t download DigitalOcean Snapshots

Unfortunately, you are unable to download your DigitalOcean snapshots and backups outside of the DigitalOcean environment. They have not made this feature available. There has been talking about making it possible but we have not seen DigitalOcean make any steps towards making this possible.

You can do a lot with DigitalOcean snapshots within their network. Create new droplets from Snapshot, restore existing droplets to snapshot and moving snapshot across data centres.

Alternatives

We have a sister product called Backup Stream that can take downloadable snapshots of any linux using a streaming backing method. It’s in closed beta but you can join the waiting list here

 

Here at SnapShooter we find that snapshots are an extremely powerful way to protect droplets and volumes. Recovering a server is extremely fast compared to restoring from an external source, network lag is very real when your moving GB’s of data in a hurry (especially when uploading from a consumer ISP connection). We also value the importance of not putting all your eggs in one basket. We recommend to all our customers and DigitalOcean customers to also create off-site backups.

Here is our recommended a list of articles created by the Digital Ocean community to get you started.