If you run a website or a forum, you are probably using MySQL as its backend database engine. MySQL is one the most popular open-source applications on the planet and pretty good one as well. Being the best and most popular doesn’t mean free of issues and headaches that come with managing databases… are you backing up your databases? If not, you may want to start backing them up multiple times a day, to multiple off-site locations.
There are many scripts available to backup MySQL databases. Here are couple of options that I have used and last one that I am testing:
- Automatic MySql Backup Script
This script finds all of the MySQL databases on your server, individually backs them up (for easy recovery of specific data), compresses them, and stores the file in a specified directory and/or emails it to you. It has been updated since the initial public release and includes several new features. - Zmanda Recovery Manager (ZRM) for MySQL
ZRM Community version is available free for download, for extended functionality, you may want to buy the enterprise edition but in my opinion, for basic needs, you shoud be good with the free version. With ZRM for MySQL you can:- Schedule full and incremental backups of your MySQL database.
- Start immediate backup or postpone scheduled backups based on thresholds defined by you.
- Choose to do more flexible logical or faster raw backups of your database.
- Perform backup that is the best match for your storage engine and your MySQL configuration.
- Backup your remote MySQL database through a firewall.
- Configure on-the-fly compression and/or encryption of your MySQL backups to meet your storage and security needs.
- Get e-mail notification about the status of your backups and receive MySQL backup reports via RSS feed.
- Monitor and browse your backups.
- Define retention policies and delete backups that have expired.
- Recover a database easily to any point in time or to any particular transaction, e.g. just before a user made an error.
- Parse binary logs to search and filter MySQL logs for operational and security reasons.
- Ultimate MYSQL backup script
I am using very basic version of this script that I found on vbulletin community forum. Its been working great so far so I haven’t had chance to mess around with it. As they say, if its working, don’t fix it! Some of the features in this script are pretty neat which made me want to test it out before implementing it.
Here is list of its features:- Is one of the few backup scripts that elegantly turns your site ‘off’ whilst carrying out a backup
- Stores a copy of your backup on your server
- Transfers another copy to a different server (offsite backup)
- Keeps a month’s worth of backups
- Sends you a nice report each day with detailed stats on how long each element took
- Works with any site regardless of application, e.g. vBulletin, WordPress, Drupal, etc
Regardless of which script you end up using for your websites, please make sure you read through the supplied documentations on each scripts website and contact them for support. I take no responsibility for your actions or how these might behave on your webhosting environment.
What do I use?
I won’t go into details of which scripts I use to backup my databases but I will give you an overview of the setup? I use one of the MySQL scripts to perform backups of the databases along with the entire server, using RSync + cronjobs to an off-site server with about 50GB of space which is enough for databases and important files. Same process repeats twice a day but the backup is done to another (different) cloud environment with about 50GB of space. Yes, there is a cost involved in this but it isn’t much, both setups together cost around $20.00 / month.
If you are using a good script and would like to write about it, please contact me.