mySQL Bash Backup Script

In on of those “oh ffs” moments I found myself writing a BASH script to quickly dump all database on a mySQL server.

#!/bin/bash
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;
DEST="/path/to/dump/folder"

USER="root";
PWD="XXXXXX";

DBS=(`$MYSQL  -u $USER -p$PWD  -Bse 'show databases'`);

for db in ${DBS[@]};
do
        `$MYSQLDUMP --default-character-set=utf8 --set-charset -u $USER -p$PWD $db | $GZIP -9 > $DEST/$db.sql.gz`
        echo "$db - DONE";
done;

This script gets a list of all databases, dumps them out with UTF8 encoding, and gzip compresses the SQL file into the given “DEST” folder.

If you want to skip over certain databases i.e. “mysql”

Change this line:

DBS=(`$MYSQL  -u $USER -p$PWD  -Bse 'show databases'`);

To:

DBS=(`$MYSQL  -u $USER -p$PWD  -Bse 'show databases' | grep -v "database_to_exclude"`);

Or for multiple exclusions

DBS=(`$MYSQL  -u $USER -p$PWD  -Bse 'show databases' | grep -v "database_to_exclude" | grep -v "another_database_to_exclude" | grep -v "etc"`);

I may re-write this in Python, if I get time.

Comments