mysql数据库备份脚本

最新推荐文章于 2024-08-16 14:30:25 发布

凤舞飘伶

最新推荐文章于 2024-08-16 14:30:25 发布

阅读量185

点赞数

分类专栏： Script

本文链接：https://blog.csdn.net/woshaguayi/article/details/118939299

版权

Script 专栏收录该内容

49 篇文章 4 订阅

订阅专栏

#!/bin/bash
MasterIp=109.288.139.186
USER=root
PORT=3306
PASSWD=22222
DATE=`date +%Y-%m-%d`
#DATE_NOW=`date +%Y-%m-%d.%H%M`
DATE_NOW=`date +%Y-%m-%d`
OLDDATE=`date +%Y-%m-%d -d "30 days ago"`　
BACKUPDIR=/data/backup_dump
BINLOG=/data/binlogserver
DATA_DIR_binlog=/data/mysql/binlog
mkdir -p ${BACKUPDIR}/${DATE}
function stop() {
ps uax | grep mysqlbinlog | grep raw | awk '{print $2}' | xargs kill
}

cd ${BACKUPDIR}/${DATE}
echo ”备份时间为${DATE},开始----” >> ${BACKUPDIR}/dbback.log
#mysqldump -u${USER} -p${PASSWD} -h${MasterIp} --flush-logs -A -R -E --triggers --master-data=2 --delete-master-logs  --single-transaction > full_${DATE_NOW}.sql
mysqldump -u${USER} -p${PASSWD} -h${MasterIp} --flush-logs -A -R -E --triggers --master-data=2   --single-transaction > full_${DATE_NOW}.sql

cd ${BINLOG}
rm -rf *
stop
ssh ${MasterIp}
mysqladmin -u${USER} -p${PASSWD} flush-logs
cd ${DATA_DIR_binlog}
Binlog_Name=basename `cat mysql-bin.index |tail -1`
mysqlbinlog -R --raw --host=${MasterIp} --user="${USER}" --password="$PASSWD" --stop-never  ${Binlog_Name} &

echo ”备份时间为${DATE},结束----” >> ${BACKUPDIR}/dbback.log
echo ”备份时间为${DATE},开始压缩----” >> ${BACKUPDIR}/dbback.log
tar zcvf full_${DATE_NOW}.sql.tar.gz full_${DATE_NOW}.sql
echo ”备份时间为${DATE},完成压缩----” >> ${BACKUPDIR}/dbback.log
echo ”备份时间为${DATE},删除full_${DATE_NOW}.sql----” >> ${BACKUPDIR}/dbback.log
rm -rf full_${DATE_NOW}.sql
echo ”备份时间为${DATE},完成删除full_${DATE_NOW}.sql----” >> ${BACKUPDIR}/dbback.log
cd ${BACKUPDIR}
echo ”开始删除60天前的数据${OLDDATE}----” >> ${BACKUPDIR}/dbback.log
rm -rf ${OLDDATE}
echo ”完成删除60天前的数据${OLDDATE}----” >> ${BACKUPDIR}/dbback.log

#!/bin/bash
# Name:mysqlFullBackup.sh
# PS:MySQL DataBase Full Backup.
# Write by:xxxx
# Last Modify:2021-07-19
# Use mysqldump --help get more detail.

PATH=/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
export PATH

#备份数据库的用户名和密码
dbuser='root'
VIP=103.23.12.16
dbpasswd='W33T3302'
backtime=`date "+%Y%m%d%H%M%S"`
backtable=gb_3
#日志备份路径
logpath='/data/mysql_backup/log'
if [ ! -d ${logpath} ];then
        mkdir -p ${logpath}
fi
#数据备份路径
datapath='/data/mysql_backup/data'
if [ ! -d ${datapath} ];then
        mkdir -p ${datapath}
fi
echo "备份时间为${backtime},数据库开始备份${backtable}表数据 " >> ${logpath}/mysqlbackup.log

mysqldump  -t bim -h${VIP} --set-gtid-purged=OFF -u${dbuser} -p${dbpasswd} --tables  ${backtable} >${datapath}/${backtable}.sql
mysql -uroot -pWkTysfrz@0202 -h${VIP} -D bim  -e 'TRUNCATE TABLE tb_trcevt'
if [[ $? == 0 ]];then
        cd $datapath
        #为节约硬盘空间，将数据库压缩
        tar zcvf ${backtime}${backtable}.tar.gz ${backtable}.sql > /dev/null 2>&1
        #删除原始文件，只留压缩后文件
        rm -f ${datapath}/${backtime}${backtable}.sql
        #删除7天前备份，也就是只保存7天内的备份
        find $datapath -name "*.tar.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
        echo "${backtime}数据库备份${backtable}表数据成功!!" >> ${logpath}/mysqlbackup.log
else
        echo "${backtime}数据库备份${backtable}表数据失败!!" >> ${logpath}/error.log
fi