Discover and share the best of the web!
Learn more about Digg by taking the tour.
Set up a MySQL backup solution in 15 minutes! (all using open source bits)
zmanda.com — MySQL is increasingly the backend of most dynamic content sites, (including digg). This article discusses how to set-up a comprehensive backup process for backing up MySQL within 15 minutes (all using open source software). A detailed how-to with actual commands, config file etc.
amandabackup- 804 diggs
- digg it
- tekayr, on 10/12/2007, -5/+0Awesome too,
- pgtourdotnet, on 10/12/2007, -4/+2Gee Digg.. could have just spit out the entities and not remove everything in between the greaterthan/lessthan. Tear.
**EDIT**
I'm on a role for clicking the wrong button. Bury me :'(
- pgtourdotnet, on 10/12/2007, -4/+2Gee Digg.. could have just spit out the entities and not remove everything in between the greaterthan/lessthan. Tear.
- BackupDigger, on 10/12/2007, -1/+1From the website, it appears it can work for remote backups too!!! Cool. I will get it a try
- brightspark, on 10/12/2007, -2/+0tried it.. looks like it fits the need of comprehensive backup tool for mysql.. trying it more..
- pgtourdotnet, on 10/12/2007, -1/+3Thanks for the link, I'll be giving this a whirl shortly :)
As for future comments, we don't care if is better. - cgriffin, on 10/12/2007, -1/+9Another nice solution is AutoMySQLBackup:
http://sourceforge.net/projects/automysqlbackup/- zodieman, on 10/12/2007, -0/+1I use this and it's great! Fire and forget.
- ashlvsya, on 10/12/2007, -0/+1Unless you have a picky host that doesn't allow "Safe Mode" off
- nofxjunkee, on 10/12/2007, -0/+1I've been using automysqlbackup since april with my home server scp'ing the compressed backups from my hosted server. I couldn't be happier unless I had incremental mysql backups just as easily.
- tpv2066, on 10/12/2007, -1/+4This looks nice, but if you are running MySQL on a managed webhost, like many of us are, this would be secondary to your host's backups. I know mine does it every 6 hours to tape, and can restore in 30 minutes or less the entire SQL server.
- nofxjunkee, on 10/12/2007, -0/+3Your hosts backup plan sounds nice, but if you run your own dedicated server or VM, like many of us are, then backing up mysql yourself is a good idea.
In other words, what's the point of your comment?
- nofxjunkee, on 10/12/2007, -0/+3Your hosts backup plan sounds nice, but if you run your own dedicated server or VM, like many of us are, then backing up mysql yourself is a good idea.
- yodaj007, on 10/12/2007, -0/+13Why not just create a cron task? I'm not sure I see the advantage of installing another piece of software when cron works just as well? I've had this running daily, without any problems.
#!/bin/sh
fn=/home/MySQL_Backups/$(date -I).sql.bz2
mysqldump --all-databases -ubackup -ppassword | bzip2 -c > $fn- Chrysalid, on 10/12/2007, -0/+1Wow, that actually works! Thanks for this great tip. It comes in handy.
- maxdamage, on 10/12/2007, -0/+0I have been doing this for years, I thought it was the standard way to do remote backups.
- jcostom, on 10/12/2007, -0/+1I do what you do, except with --opt on the mysqldump.
I've got another 3 line script that runs monthly out of cron, leaving me only older monthly backups of the db's, as well as the complete current & previous months. - rogueman, on 10/12/2007, -0/+1#!/bin/sh
mysqldump -u root myapp | gzip > /backup/backup-`date +%e-%m-%y`.sql.gz
cd /usr/local/tomcat/webapps/
tar -c myapp/* | gzip > /backup/myapp-`date +%e-%m-%y`.tar.gz
echo "Database Backup" | mail -s "Database Backup `date +%e-%m-%y`" -a /backup/backup-`date +%e-%m-%y`.sql.gz me@gmail.com
echo "App Backup" | mail -s "App Backup `date +%e-%m-%y`" -a /backup/myapp-`date +%e-%m-%y`.tar.gz me@gmail.com - maxdamage, on 10/12/2007, -0/+0If you have a remote server with cpanel you can always do this
#!/bin/sh
date=`date | sed -e 's/[:]/_/g' -e 's/ /_/g'`
wget http://uname:pass@www.mysite.com:port/getsqlbackup/dbname.gz -O /backup/dmname.$date.gz - aaroncampbell, on 10/12/2007, -0/+0I use this script. It creates backups in /db_backups in directories for each day...I run it hourly, so each file is named by the date/hour like this: /db_backups/YYYY_MM_DD/YYYY_MM_DD-HH.sql.bz2
Then it rsyncs them to my server at home. Every so often I burn them to DVD.
#!/bin/sh
Dir=`date +"/db_backups/%Y_%m_%d"`
FileName="`date +"$Dir/%Y_%m_%d-%H"`.sql.bz2"
if [ ! -d "$Dir" ]
then
mkdir "$Dir"
fi
mysqldump -A --opt --user=backup --password=password --host=localhost | bzip2 > "$FileName"
rsync --bwlimit=40 -a -e ssh /db_backups ezdrsync@10.0.0.1:
- manonmars, on 10/12/2007, -0/+1The article could have just pointed to zmanda recovery manager http://www.zmanda.com/backup-mysql.html.
- DontSayFanboy, on 10/12/2007, -0/+1That's a good solution, but for those of you that don't want to install zmanda and deal with the overhead of learning a whole new program, here is a simple shell script that I use to backup my home mythtv box.
It is nowhere near as fancy as the linked article, and it does lock the tables while the database is happening, but since this is just a quick and dirty job for my mythtv system at home, I can live with it
I run it from cron, once per day. It creates a full backup of all the databases to a different hard disk. I then run the following command daily to clean up any backups older than a month.
find /backup/puppies/mysql/ -ctime +30 -exec rm '{}' ;
-----
#!/usr/bin/perl
$ = "n";
$mysqldump = "/usr/bin/mysqldump";
$compress = "/bin/gzip -9 ";
@t = localtime;
$outfile = "/backup/puppies/mysql/all-databases-";
$outfile .= ($t[4]+1)."-".$t[3]."-".($t[5]+1900).".sql";
$command = "$mysqldump --all-databases --opt --password='XXXXX' ";
$command .= "--lock-tables -u root > $outfile ";
system ($command);
system ($compress.$outfile);
- DontSayFanboy, on 10/12/2007, -0/+1*****, yoda beat me too it, and his is better.
- elroy, on 10/12/2007, -0/+1I do the same thing, only with a shell script. In addition, I rotate backups, so I have a month history. I use diff to tell if there's been a change since the last backup, so I don't store duplicates. Works pretty well.
- dc2447, on 10/12/2007, -0/+1@yodaj007 - it isn't a great idea to a: never delete old dumps b: run mysqldump with the default options --opt is your friend
There are many ways to backup mysql, here are a few
* Mysqdump
* Mysqlhotcopy
* Amanda
* Replication
There isn't a "right" way- dinergy, on 10/12/2007, -0/+0there may not be a "right" way, but i tend to agree with the philosophy that less software is better. why install 3rd-party backup tools when you already have everything you need on the server?
set up replication between db's and then write a bash script to dump data to compressed archive on a cron schedule. no need to increase foot-print or rely/support another piece of software.
i think people take the software approach instead of the easy, simple and elegant solution too often because they think software is "better."
- dinergy, on 10/12/2007, -0/+0there may not be a "right" way, but i tend to agree with the philosophy that less software is better. why install 3rd-party backup tools when you already have everything you need on the server?
- Modulo, on 10/12/2007, -0/+1can anyone suggest a similar article for (please don't hit me, I just use it for my own bookmarks) a WAMP setup?
- zoom1928, on 10/12/2007, -0/+1This solution is no better than the non-solution of mysqldump. Neither can be used on a production system since they can lock-up your database server for hours because they lock the tables. MySQL doesn't have a way of backing-up data. That's why so many people pay so much for Oracle because you can backup Oracle.
- dinergy, on 10/12/2007, -0/+1actually, unless you run rman, oracle must be shutdown prior to tape backups as well. we have cron jobs that execute shutdown and startup scripts tied to networker backups.
so, really, oracle is no better. any database with locking and/or running files will cause a backup to fail. or at the very least by useless/corrupt.
this is why with mysql you use replication/clustering between machines and shutdown the secondary server for data dump...not the production one. - BackupDigger, on 10/12/2007, -0/+0I think you can use ZRM for MySQL with MySQL replication also (See http://mysqlbackup.zmanda.com). You can do a hot backup with MySQL replication.
If you are comparing with Oracle, you are likely using InnoDB for your MySQL tables.
For InnoDB, the quick way to do hot backups is to use LVM snapshot (no locks are required) and backup using mysqlhotcopy. It appears ZRM for MySQL provides lvm snapshot option also.
Of course, these hot backup methods require additional hardware resources :-(
- dinergy, on 10/12/2007, -0/+1actually, unless you run rman, oracle must be shutdown prior to tape backups as well. we have cron jobs that execute shutdown and startup scripts tied to networker backups.
- josmtx, on 10/12/2007, -3/+0Yeah, and this is why you have granular authorization, so that you can GRANT everything to a backup user, even with grant privilege.
Thanks for making your DB easier to get into. Oh, please use backup or backup_user as your password, pass123 is way too complex. - forkqueue, on 10/12/2007, -0/+1As others have pointed out, this is a non-solution because it requires locking tables.
The real solution is to set up MySQL replication, and use mysqldump on the replicated host. This has been the case since MySQL 3.x
The Digg Toolbar for Firefox lets you Digg, submit content, and keep track of Digg even when you're not on the Digg site. Download the official 
© Digg Inc. 2008 —
Content posted by