mysql备份shell

单次

#!/bin/bash
# script name master_dbbak.sh
BACKUP_FOLDERNAME=/home/bak  #数据库备份文件的主目录
DB_HOSTNAME="localhost"
DB_USERNAME="root"
DB_PASSWORD="cccc@123.0"
DATABASES=(
            "basename"   #备份的数据库名,可列举多个
#            "test"                    
)
#=========
echo "Bash Database Backup Tool"
CURRENT_DATE=$(date +%F)
BACKUP_FOLDER="${BACKUP_FOLDERNAME}/${CURRENT_DATE}"
LOGFILE=${BACKUP_FOLDER}/${CURRENT_DATE}.log
mkdir $BACKUP_FOLDER
#统计需要被备份的数据库
count=0
while [ "x${DATABASES[count]}" != "x" ];do
    count=$(( count + 1 ))
done
echo "[+] ${count} databases will be backuped..." >$LOGFILE
for DATABASE in ${DATABASES[@]};do
    echo "[+] Mysql-Dumping: ${DATABASE}" >>$LOGFILE
    echo -n "   Began:  ";echo $(date) >>$LOGFILE
    if $(/usr/bin/mysqldump -h ${DB_HOSTNAME} --single-transaction --master-data=2 --flush-logs -u${DB_USERNAME} -p${DB_PASSWORD} -ntd -R  ${DATABASE} > "${BACKUP_FOLDER}/01_${DATABASE}.sql");then
        echo "  Dumped Function successfully!" >>$LOGFILE
    else
        echo "  Failed dumping this database!" >>$LOGFILE
    fi
        echo -n " Function Dumped  Finished: ";echo $(date)	>>$LOGFILE
	if $(/usr/bin/mysqldump -h ${DB_HOSTNAME} --single-transaction --master-data=2 --flush-logs -u${DB_USERNAME} -p${DB_PASSWORD} ${DATABASE} > "${BACKUP_FOLDER}/02_${DATABASE}.sql");then
	    echo "  Dumped Data successfully!" >>$LOGFILE
    else
        echo "  Failed dumping this database!" >>$LOGFILE
    fi
        echo -n " Data Dumped  Finished: ";echo $(date) >>$LOGFILE
done
echo
echo "[+] Packaging and compressing the backup folder..." >>$LOGFILE
cd $BACKUP_FOLDER
zip ${CURRENT_DATE}.zip 01_${DATABASE}.sql 02_${DATABASE}.sql ${CURRENT_DATE}.log
mv ${CURRENT_DATE}.zip $BACKUP_FOLDERNAME
echo "[+] Cleaning the backup folder..." >>$LOGFILE
cd $BACKUP_FOLDERNAME
rm -rf $BACKUP_FOLDER
find ${BACKUP_FOLDERNAME} -mtime +15 -name "*.zip" -exec rm -rf {} \;

全备

#!/bin/bash
#function: 主库备份所有库,并记录master status,用于做主从同步恢复时使用
BACKUP_FOLDERNAME=/home/masterbak
DB_USERNAME="root"
DB_PASSWORD="xxxxx@123.0"

LOG_FILE=${BACKUP_FOLDERNAME}/mysqllogs_`date +%F`.log
DATA_FILE=${BACKUP_FOLDERNAME}/mysql_backup_`date +%F`.sql.gz

MYSQL_CMD="/usr/bin/mysql -u$DB_USERNAME -p$DB_PASSWORD"
MYSQL_DUMP="/usr/bin/mysqldump --set-gtid-purged=off  -u$DB_USERNAME -p$DB_PASSWORD -A  -B --flush-logs --single-transaction -e"


$MYSQL_CMD -e "flush tables with read lock;" 
echo "-----show master status result-----" >>$LOG_FILE
$MYSQL_CMD -e "show master status;" >>$LOG_FILE
${MYSQL_DUMP} | gzip > $DATA_FILE
$MYSQL_CMD -e "unlock tables;"


find ${BACKUP_FOLDERNAME} -mtime +10 -name "*.sql.gz" -exec rm -rf {} \;
find ${BACKUP_FOLDERNAME} -mtime +10 -name "*.log" -exec rm -rf {} \;