another simple script for backup mysql databases

backup-all-databases.sh

#!/bin/bash

#create user for dump
#mysql> GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to dump@localhost IDENTIFIED BY 'dumppwd';
#mysql> FLUSH PRIVILEGES;

BACKUP_PATH=/mnt/mysqlbackup/dump

# make backup directory
mkdir -p $BACKUP_PATH

# remove backup's older than 14 days
find $BACKUP_PATH -type f -ctime +13 -exec rm {} \;

# get all databases
db=`mysql -u dump -pdumppwd -Bse 'show databases'`

for n in $db; do
	BACKUP_FILE=$BACKUP_PATH/$n-`date +%Y-%m-%d`.sql.bz2
	mysqldump             \
		--skip-set-charset  \
		--user=dump         \
		-pdumppwd            \
		$n |  \
		bzip2 -9 > $BACKUP_FILE
	echo $BACKUP_FILE created
done