Backup multiple MySQL databases into separate files

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

USER="root"
PASSWORD="secret"
OUTPUTDIR="/var/lib/bacula"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# clean up any old backups - save space
rm "OUTPUTDIR/*bak" > /dev/null 2>&1

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD
    --databases $db > "$OUTPUTDIR/$db.bak"
done

---

Possibly related posts: (automatically generated)

• Do not use MySQL Administrator for backup
• Automated backup script for Mysql
• Resetting the Root Password on Unix Systems