mysql备份文件gz_将MySQL数据库备份到1个文件中,但每个数据库使用单独的.tar.gz

这样的事可能有用.它没有经过测试,但与我用于系统备份的情况略有不同.

# define common vars

OPTIONS="--verbose --lock-tables --flush-logs --force --quick --single-transaction"

AUTHFILE="/etc/mysql/rootauth.cnf"

BACKUPDIR="/srv/backup/mysql/"

BACKUPDATE=`date +"%y%m%d%H"`

# create temp folder (this isn't entirely safe, but be sure only root or backup user has

# write access here, you might want to use mktemp)

mkdir ${BACKUPDIR}/tmp/

# get a list of all the databases on the system

DBSQL="SELECT SCHEMA_NAME FROM information_schema.SCHEMATA where SCHEMA_NAME!='information_schema' \

AND SCHEMA_NAME!='performance_schema' order by SCHEMA_NAME"

DBS=`/usr/bin/mysql --defaults-extra-file=${AUTHFILE} --batch \

--skip-column-names --execute "$DBSQL"`

DBS=`echo $DBS | tr -d '\n' | sed -e "s/ \+/ /g"`

for DB in $DBS; do

# perform a per-database dump

BACKUPDIRDB="${BACKUPDIR}/tmp/${DB}"

mkdir -p ${BACKUPDIRDB}

/usr/bin/mysqldump --defaults-extra-file=${AUTHFILE} \

${OPTIONS} $DB > ${BACKUPDIRDB}/backup_${BACKUPDATE}

done

# create archive of everything

tar -czvf ${BACKUPDIR}/backup_${BACKUPDATE}.tar.gz ${BACKUPDIR}/tmp/

#remove temp files

rm -rf ${BACKUPDIR}/tmp/