Backup all mysql databases to individual files

Feb 16th, 2011 | By | Category: Internet, Technology

Thanks go out to Sonia Hamilton, as her blog is where I found the original version of this script, however it did need a fair bit of tweaking to work properly in BSD so I went ahead and redid parts of it  as you can see below.

This script will back up each of your databases separates into their handy dandy own .sql files and then gzip them up for you.  Enjoy

#!/bin/bash
# sonia 16-nov-05 & Ryan 16-feb-11
# backup each mysql db into a different file, rather than one big file
# as with –all-databases – will make restores easier

USER=””
PASSWORD=””
OUTPUTDIR=”/usr/mysqlbackup”
MYSQLDUMP=”/usr/local/bin/mysqldump”
MYSQL=”/usr/bin/mysql”

#Clean out yesterdays news
rm -rf $OUTPUTDIR/*

#Get a list of databases
databases=$(/usr/local/bin/mysql -u$USER -p$PASSWORD -e “SHOW DATABASES;” | grep
-Ev “(Database|information_schema)”)

#Dump each database in turn
for db in $databases; do
echo “Now Dumping $db”
$MYSQLDUMP -f -u$USER -p$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done

Tags: , , , ,

Leave a Comment