mysql备份脚本-mysqldump

# 首先需要创建备份用户：
create user bkpuser@'localhost' identified by 'bkpuser@123';
grant all on *.* to 'bkpuser'@'localhost';
FLUSH PRIVILEGES;



# 查看权限
mysql> show grants for 'bkpuser'@'%';
复制

#!/bin/bash  
# description:
# 此脚本为mysql备份脚本
# author: morb

# 数据库账号密码IP
USER="bkpuser"
PASSWORD="bkpuser@123"
IP="localhost"
PORT="3306"

# 需要备份的数据数据库
DATABASES=`/usr/local/mysql/bin/mysql  -u ${USER} -p${PASSWORD}  -e 'show databases' | egrep -v 'schema$|^Database' |xargs`

#备份命令
mysqldump_lnk="/usr/local/mysql/bin/mysqldump -u${USER} -p${PASSWORD} -h${IP} -P${PORT}"

# 备份目录、备份日志、备份时间
DATEDIR=`date +%Y%m%d`
BACKUP_DIR=/data1/mysqldump_db/${DATEDIR}
LOGFILE=${BACKUP_DIR}/data_backup.log 
DATE=`date +%Y%m%d_%H%M`

# 判断备份目录是否存在
test ! -d ${BACKUP_DIR} && { mkdir $BACKUP_DIR -p || { echo "备份目录创建失败">> $LOGFILE; exit 1; } }

cd $BACKUP_DIR
#开始备份之前，将备份信息头写入日记文件   
echo "--------------------" >> $LOGFILE   
echo "BACKUP DATE:" $(date +"%y-%m-%d %H:%M:%S") >> $LOGFILE   
echo "-------------------" >> $LOGFILE


function select_backup(){
# select databases backup
for DATABASE in ${DATABASES[@]};do
 ${mysqldump_lnk} --events  -R --opt  --set-gtid-purged=OFF --single-transaction --master-data=2 --flush-logs $DATABASE |gzip >${BACKUP_DIR}\/${DATABASE}_${DATE}.sql.gz

if [ $? -eq 0 ];then
   echo "$DATE--$DATABASE is backup succeed" >> $LOGFILE
else
   echo "Database Backup Fail!" >> $LOGFILE
fi
done
}

function del_backup(){
# 删除30天以上的备份文件（看需求自己更改）  
find $BACKUP_DIR  -type f -mtime +30 -name "*.gz" |xargs rm -f;
}


main()
{
  select_backup;
   del_backup
}

main > ./setup.log 2>&1
复制