Backup all MySQL Databases

This shell script needs to have execute permissions. You’ll also need to modify the SQLUSR and SQLPWD with the values of a user that has read permissions on all databases you want it to backup. This script needs to be run as a user who has write permissions to BKPDIR.


#!/bin/bash
SQLUSR=sql_username
SQLPWD=sql_password
BKPDIR=/my/backups/sql/
RDATE=`date +%Y%m%d`
mysql -u $SQLUSR -p$SQLPWD -e "show databases;" -s > tmplst.tmp
exec 6 while read -u 6 dta
do
FN1="mysqldump -u $SQLUSR -p$SQLPWD "$dta" | gzip > “$BKPDIR$dta.$RDATE”.sql.gz”
echo $FN1 >> bkup_cmd.tmp
done
exec 6<&-
fi
source bkup_cmd.tmp
rm tmplst.tmp
rm bkup_cmd.tmp