The PHP Cult
Shamelessly promoting PHP, Myself, and my services
MySQL Database Backups, and your health.
Author: Vidyut Luther18 Feb
If you read my blog, chances are you are aware of this new fad called the internet, and this new language called PHP. You’re also probably aware of things like MySQL and SQL, and the importance of data to visitors of your website. So, keeping that in mind, I’d also like you to know about backing up this data.
As I grow my business, the number of clients I run into that do not do backups, astounds me. I’m not talking about end user laptops/desktops, but rather backups of their SugarCRM database, or their file server, or even their company website, which is powered by Drupal/Wordpress/CMS Du Jour. When, not If, the database is corrupted, or the disk fails, they have no idea what they would do then. They just hope that it doesn’t happen to them.
So I did some thinking, and decided I should write a post, and make available to the world, a simple script, which I believe will at least give you onsite backups. This is what the script does:
- Backs up each mysql database into it’s own little .sql file.
- Makes a tar ball of the .sql files.
- Copies this tarball to a remote server of your choice. (optional/requires more than editing a few lines)
The script is available to download/view here.
Change the configuration options at the top of the file, make sure everything works, and then put it in your cron. Feel free to post questions/comments in the comments section.
7 Responses for "MySQL Database Backups, and your health."
You asked for a username but you use the root username for running mysql and mysqldump.
Mistake?
For those looking for a piece of software rather than just scripts, try http://www.zmanda.com/backup-mysql.html.
I do a similar technique for backing up databases on a live server via a ssh script, but find that using rsync (I use the services of http://rsync.net BTW - which I find works great) and not compressing the resulting mysqldump sql scripts means that rsync will simply transfer the differences rather than the entire sql script - saving transfer times/bytes. Since its usually just the addition or modification of INSERT INTO statments that will change between backup tasks. Compressing the scripts, whilst making them smaller would most likely make rsync work a little bit harder/transfer larger chunks of data.
Like you say, I amazes me how often backups are overlooked after “go live”. With rsync and a few simple scripts I backup my live server every few hours on a cron job and my development laptop when required via a simple script.
Whilst your offering only a simple script it is still flawed, some problems are;
1) your mysqldump needs atleast to include –allow-keywords, otherwise when you come to restore the dump file you will have problems if reserved words have been used.
2) your backup strategy suggests that its exceptable to lose the data changes made to the DB from now() until the last DB dump. You need to also backup the bin logs at regular intervals or have your binlogs on a replicating partition like DRDB.
3) Your tarball’d dump files should include the last used binlog number as of the result of –flush-logs, so you can do point in time recovery from the dump file and then replaying the binlogs to restore data since the dump/snapshot was taken.
4) You need to stress the importance of having correct file permissions on the dump script since it has the SQL user password in it, also the locally saved tarball and remote file should also have correct file permissions no unauthorized user can read the dump data.
—-
@Peter Mescalchin
Your rsync backups are worthless as they fail to deal with corruption of the source dump file. Any good backup system includes taking differentials of the data and rotating them, so if you get data corruption in a backup you still have the ability to restore the non-corrupt backup that you made the period before.
It is unecessary to take dumps of the DB every few hours, this indicates you don’t understand the point of point in time recovery with the bin log.
—-
The above is only basic advice, however “it amazes me how often I hear/see of people who think they know what there doing with backups and critize others who have no backup plan when in fact their own methods are flawed”.
Your solution will not work for larger MyISAM production databases (mysqldump will hold a lock for too long).I find the best solution is to use LVM2 to snapshot the filesystem for backups. I wrote a script for my current employer to:
1. For all mysqld sockets on the specified filesystem (”find” is your friend here), fork a “FLUSH TABLES WITH READ LOCK” and wait.
2. Snapshot the filesystem using LVM2.(much faster than mysqldump).
3. Release all the locks.
4. Back up the filesystem snapshot (I use dump to back up to tape, you could use rsync here).
5. Delete the snapshot.
This solution has been running with no problems for over six months. I use MySQL replication to get the data onto the backup server (you could use a second mysqld on a separate filesystem for this purpose) which means the live database is not locked during a snapshot.
-Dan Gardner
There’s a pretty good commercial option that we’re using, called Continuous Data Protection, by a company named R1Soft.
The website is r1soft.com
It does live backups of our mysql databases, and other data as well, we’ve been using it successfully, and it’s price is ludicrously cheap (and no, I’m not related to the company at all).
Michael S. Moody
Sr. Systems Engineer
Global Systems Consulting
Web: http://www.GlobalSystemsConsulting.com
Hey — create a gmail account and tar/gz the .sql and email it to gmail. Even a largish db shrinks because of repetitive text statements (insert into…). If they are too big as one, divvy by db or even table. Works create. Just go into gmail now and then and delete the old ones to make sure you don’t overrun space.
Leave a reply