How in the Tech

Automated backups of Mysql databases to Gmail

By Adam Myers ⋅ April 7, 2010 ⋅ Tags: Backup, Gmail

As a webmaster I’m obsessive over having consistent and recent backups of websites I’m responsible for. There are certainly worse things to have OCD over but the thought of losing everything I’ve worked hard on is impossible to set aside. Generally, even the cheapest of web hosting services offer some sort of routine backups for their clients. They may not be daily snapshots, but for many any backup is better than no backup.

My web host at the time of this writing offers a daily backup but speaking from experience, this is not something you want to rely on to safeguard your data, unfortunately. The problem in my situation, the backups were stored on the same disk as my existing web files. This is conveinent in that if I need to restore anything I don’t need to go through the web host’s support channels – it’s already live on the file system. The major downside, while data eventually ends up on tape – or somewhere else out of my grasp, it doesn’t happen as frequently as the nightly snapshots they provide. A disk failure can potentially still leave you missing the last few days of data or content – as happened to me many months back.

The old adage if you want something done correctly, do it yourself applies aptly here. Therefore, I maintain my own backup system, in addition to the one provided by the web host, that provides off-site redundancy of all my data – the key to any disaster recovery backup system in my opinion. Transferring the actual files off site is pretty easy to automate but database driven web sites, which most are, requires another level of backup. Truth be told, for a majority of database driven web sites the database is easily the most important data set! Database corruption is very real and the results could be disastrous.

Off site backups may not be feasible for all users however. In this situation, Backup2Mail is quite the find. Leveraging your Gmail account and it’s theoretically limitless data store space, it’s possible to automate Mysql database backups and store them safely off site in your Gmail Inbox – assuming they fit under the 25MB attachment limit; a safe assumption for most web sites actually.

Backup2Mail is a PHP based script that runs on your web host’s server that dumps the Mysql database to a file, compresses the file, and emails it to your Gmail. Automation is leveraged through cron – the Unix/Linux scheduler. The installation and configuration is as easy as it comes:

• Launch cron with crontab -e command in a shell session
• Paste the line 0 0 * * * php /home/your_account/backup2mail/index.php >/dev/null 2>&1 in the crontab editor window. Exiting and saving when finished. This will execute the script index.php every day at midnight; the first 0 controls the minutes of the hour while the second 0 defines the hour, on a 24 hr clock.
• From your shell window execute crontab -l to ensure the job above was scheduled correctly
• Lastly, edit index.php from Backup2Mail and provide the necessary information for your Mysql and Gmail configurations.

Download Backup2Mail