Alphatek – Steven's Tech Blog

To backup MySQL databases, there is the well known and handy executable called mysqldump. It has one big shortcoming though, you can either backup one database to a file or all databases to one giant file with the “–all-databases” option but you can’t backup every database to separate dump files.

This is not very practical if you have big databases and need to restore only a part of them…So I searched for a way to backup every database to separate files. The process is quite simple with a little scripting:

• Find a way to list all databases
• For each database, dump it to a file using mysqldump

I quickly found a blogpost from 2005 about that, but it was a bit outdated and not accurate anymore for MySQL 5.1. So here is an updated version, I hope it helps and saves time to someone:

#!/bin/bash
# This script backups every MySQL database to its own file

#Some variables you can set how you like
USER=’root’
PASSWORD=’mypassword’
OUTPUTDIR=’/root/backup’
MYSQLDUMP=’/usr/bin/mysqldump’
MYSQL=’/usr/bin/mysql’

#Clean up any old backups
rm -f $OUTPUTDIR/*

#Get a list of databases names except the system one
databases=`$MYSQL –user=$USER –password=$PASSWORD -e ‘SHOW DATABASES;’ | grep -Ev ‘(Database|information_schema)’`

#Dump each database in turn and compress the output
for db in $databases; do
$MYSQLDUMP –opt –hex-blob –force –user=$USER –password=$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done

PS: WordPress seems to replace double dashes (- -) with a single one (-) in the code, so just be aware of that.

I also strongly advise you to use the –hex-blob option for mysqldump if you have any blobs (binary files like executables, images…) in your database, otherwise you may end up with an un-restorable database, even if the backup process went well.

This entry was posted on Thursday, January 7th, 2010 at 1:57 pm and is filed under /dev/null. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.