backup all databases from mysql to separate files

mysql-backup-all.sh

#!/bin/bash


# Problem :Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
# Solution 1: --single-transaction
# Solution 2: GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'root'@'localhost';


echo "****************************************";
echo "* Backup All Databases                 *";
echo "****************************************";

# backup each mysql db into a different file, rather than one big file
# as with --all-databases - will make restores easier

USER="root"                     
PASSWORD="root"
OUTPUTDIR="/www/data/backup/mysql" # backup destination

MYSQLDUMP="/usr/bin/mysqldump"
MYSQLCHECK="/usr/bin/mysqlcheck"
MYSQL="/usr/bin/mysql"

echo -e "\nCheck databases...\n";
$MYSQLCHECK --all-databases --user=$USER --password=$PASSWORD

# 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`

echo "List of databases:";
for db in $databases; do
    echo -e "\t - $db"
done

# dump each database in turn
for db in $databases; do
    #show only date `date +%Y-%m-%d`
    DATE=`date +%Y_%m_%d_%H_%M` 
    echo "Processing $db";
    
    $MYSQLDUMP \
        --force --opt --single-transaction \
        --user=$USER --password=$PASSWORD \
        --databases $db > "$OUTPUTDIR/$db.$DATE.sql"
done

echo -e "\ndone.\n";