Hourly Database Backup Plan

November 17, 2015 950 views
MySQL DigitalOcean

Correct me if i am wrong, but am i right in saying that Digital Ocean only backup your database once a week?

Can anyone suggest a good way to automate a daily or hourly backup... to email, dropbox or some other storage... thank you.

1 Answer


IIRC, there's not a definite set schedule for backups. When I lasted used them, the backups spanned across a mix of daily and every other day. Without being able to define a schedule, the backups are honestly a last resort until a more fine-tuned and granular option is available.

That said, MySQL backups are finicky and should be treated as such if you value the data. Ideally, you don't want to backup in the midst of writes being performed as this can result in incomplete data, and that can pose a security risk depending on what data was written and what wasn't.

As an example, let's say that a new user is registering. Registration means that data is being written, so if you shutdown and in the process only the username gets written but not the password, and your application / code does not account for this, that user (unless your code accounts for it and prevents it) could technically login with just their username. Not a big deal when it comes to a service that just provides a profile or similar, though if you were selling something (i.e. a service), chances are good that they just got in for free.

How large is the backup and how large is it expected to get over the next few weeks, months etc? If you're looking for a short-term solution, AutoMySQLBackup works well:

For critical data, and to prevent the above mishaps from occurring, you'd want to look in to something that would ensure all current writes are complete, shutdown MySQL, perform the backup, and restart.

  • @jtittle thank you

    Only 10-15 writes are made every 24 hours, but the information is very important (hence the backup).

    These are all very small records (all text based... less than 200 characters per record)

    • @mosaic

      In such a case, you should be fine using the script above (MySQLAutoBackup).

      The real issue is when you're doing XX to XXX+ writes per second. At this point, timing becomes more critical and making sure that you're configuration is setup to account for a backup process launching and in turn, potentially preventing writes (on a single server config) becomes mandatory. Ideally, in this position, you'd be on a cluster setup with at least a Master/Slave configuration (thus preventing actual downtime).

Have another answer? Share your knowledge.