Setting up a mysql backup strategy it’s hardly an exciting task, so having a simple solution it’s key to actually get it out of your to-do list.
Here’s a simple, automated and low-cost alternative that I use to keep MySQL database backups of small to medium-sized projects.
Setting up automatic backups
automysqlbackup it’s a simple shell script that automates the creation of daily, weekly and monthly MySQL backup.
It’s available on the repositories for Debian and Ubuntu. The project it’s officially hosted on SourceForge but you can also find lots of several of forks hosted on GitHub.
If you’re using Ubuntu, the installation it’s completely straightforward; all you need it’s sudo apt install automysqlbackup and you’re done.
Backups are saved on /var/lib/automysqlbackup, organized by daily/weekly/monthly directories and then by database name.
There are a few settings that you can modify on /etc/default/automysqlbackup, such as the backup dir, whether to send informative e-mails or to keep a “latest” directory.
Off-site backups
Of course, having automated backups it’s just part of the solution: you need to keep an off-site copy of your data in case your server it’s compromised.
A very simple and cheap alternative it’s using Google Drive as external storage: for USD 1.99 you get 100 GB which are shared with other Google services such as Photos and Gmail, but even the free 15 GB are plenty.
For saving your backups to Drive you can use drive, which it’s a tiny program to pull or push files to the service. There are several platform packages for various distributions.
After the installation, you need to initialize the client, so you can get an OAuth token to authorize the application access to your Drive account.
The client doesn’t do synchronization, it trusts the user to determine the authoritative version of a file or folder, which might be problematic in some cases but it’s specially useful for copying the backups, since that will automatically take care of deleting older backups —which you can still find on your “Trash” for 30 days since deletion.
You can set a daily cron job to upload your latest backups using something like this:
25 4 * * * cd /root/gdrive/mysql-backups && drive push -no-prompt
