During my programming work I had to do some system administration tasks, and since a while I'm also maintaining some servers. This is a log for the problems I find - and hopefully their solutions.

Tuesday, October 11, 2005

Backup all mysql databases to separate dump files

It's easy to just simple do a

mysqldump --alldatabases > mysqlbackup.sql

to backup everything into a large SQL dump, so that you have the inserts there if you have a booboo happening. But what a nightmare you face when you just have to get those lines that affect your recently-crashed site! Well, there's a bit more sophisticated, but much better solution: have each of your database backed up in a separate SQL dump file with the following shell script:

for i in /var/lib/mysql/*/; do
dbname=`basename $i`
/usr/bin/mysqldump $dbname > /home/db_backups
done

Of course you might have to add login parameters to the mysql line above, but that shouldn't be a problem. (Hint: -u myusername --password=mysecretpassword)

3 Comments:

Blogger Paula said...

Hey I know this is a crazy old post, but I found it on google and it helped me a bunch, thanks!

10:25 AM

 
Blogger Cass Surek said...

Indeed, rather old but very useful! Thanks for the concise piece of knowledge. :) Cass

1:17 PM

 
Blogger starcelebs said...

wow! thanks so much even though this is so old. I have modified it even further so you can keep as many days as you want without over writing.

date=`date -I`
for i in /var/lib/mysql/*/; do
dbname=`basename $i`
mysqldump --password=yourpw $dbname > /root/mysqlbackups/$dbname-$date.sql
done

7:22 PM

 

Post a Comment

<< Home