Improving your database backup policy

In this brief article I will reflect on a strategy which I normally adopt in an enterprise which requires a solid database backup policy in which frequent regular backups of their data-set is needed.

First and foremost get familiar with a filesystem snapshot technology; whether an enterprise tool such as VMware's Veeam, or something like LVM. Secondly, get familiar with a replication technology of your database system, for example in MySQL we have master-master or master-slave.

The strategy can be explained in these simple terms is:

1. Stop replication
2. Stop database service on slave/secondary node
3. Take a snapshot of the slave/secondary node
4. Start replication
5. Repeat steps 1 - 4 every hour
6. Repeat steps 1 - 4 every day at midnight
7. Repeat steps 1 - 4 every first day of the month at midnight
8. Every Sunday at midnight, purge all hourly backups of the previous week
9. Every first day of the month at midnight, purge all daily backups of the previous month excluding the first day

Now you have a nice catalog of backups which allow you to also do point-in-time recovery by applying binary logs in between backups. Adjust backup retention as necessary.