#!/bin/sh
#
#  1. Download this file
#  2. Edit the settings below
#  3. Create ~/.my.cnf with the password for the user you specified:
#       [client]
#       password=123456
#  4. $ chmod 600 ~/.my.cnf
#  5. $ chmod +x backup_mysql.sh
#  6. $ ./backup_mysql.sh
#

## Config
user="root"
mysql="mysql --user=$user"
mysqldump="mysqldump --user=$user --quick --quote-names --extended-insert --add-drop-table --comments --lock-tables"
folder="db_backup/"
filename="db.$(date +%Y-%m-%d-%H-%M-%S).tar.bz2"

## Code
mkdir $folder

databases=`echo "SHOW DATABASES;" | $mysql | tail -n +2`
for database in $databases; do
    mkdir ${folder}${database}
    tables=`echo "SHOW TABLES FROM $database;" | $mysql | tail -n +2`
    for table in $tables; do
        $mysqldump $database $table > ${folder}${database}/$table.sql
    done
done

tar -c $folder | bzip2 > $filename
rm -r $folder