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:
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
Indeed, rather old but very useful! Thanks for the concise piece of knowledge. :) Cass
1:17 PM
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