#/bin/bash
# the backup date
DATE=`date +%Y%m%d%H%M`
#backup path
BACKUP_PATH=/home/backup/mysqldata
#get all database name
SQL_STRING="SELECT SCHEMA_NAME AS db FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema');"
DBS=$(echo $SQL_STRING | mysql -uroot -pZa888888! -Bs)
#just backup the latest 5 days
find ${BACKUP_PATH} -mtime +5 -name "*.sql.gz" -exec rm -f {} \;
for DBNAME in $DBS
do
echo "backup "$DBNAME;
if [ ! -d ${BACKUP_PATH} ]; then
mkdir -p ${BACKUP_PATH}
fi
/usr/bin/mysqldump --set-gtid-purged=off -h 127.0.0.1 -R --opt $DBNAME | gzip > ${BACKUP_PATH}/${DBNAME}_${DATE}.sql.gz
echo "${DBNAME} backup finished"
done
echo "mysql backup finished!"