Mysql数据库备份脚本(mysqldump)-最新

#!/bin/bash

#备份整个数据目录
backdirname=mysql_data
backfilename=mysql_dbfile_
backsource=/var/lib/mysql
tar zcfP /backup/$backdirname/files/$backfilename$(date -d "today" +"%Y-%m-%d").tar.gz $backsource

#每个数据库备份成完整sql文件
MyUSER="root"
MyPASS="root1qwe" 
MyHOST="localhost"
 
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Backup Dest directory, change this if you have someother location
DEST="/backup"

# Main directory where backup will be stored
MBD="$DEST/mysql_data/sql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%Y-%m-%d")"
 
# File to store current backup file
FILE=""
# Store list of databases 
DBS=""
 
# DO NOT BACKUP these databases（不需要备份的数据，多个数据库空格分开）
IGGY="mysql performance_schema  information_schema"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
#$CHOWN 0.0 -R $DEST
#$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	[ ! -d $MBD/$db ] && mkdir -p $MBD/$db || :
	FILE="$MBD/$db/$db.$HOST.$NOW.gz"
    $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS --add-drop-table --complete-insert --extended-insert --lock-all-tables --quick $db | $GZIP -9 > $FILE
    fi
done