MySQL 5.6 以上脚本自动备份

淩亂°似流年 2022-05-24 08:50 169阅读 0赞

# cat /sh/.my.cnf 
    [client]
    host = localhost
    user = root
    password = '123456'

\# more mysqldump-2.sh \#!/bin/bash\#\#\#chuang by 2018-05-24\#\#\#mysqldump \#\#\#释放内存echo '==========================================='echo "free the memory"echo 3 > /proc/sys/vm/drop\_cachesecho "\`date\` begining bak..."\#\#查看脚本执行的过程set -x \#\#\#非0 退出set -e\#数据库IPdbserver='172.17.35.244'\#数据库用户名dbuser='root'\#数据库密码dbpasswd='123456'\#备份数据库,多个库用空格隔开dbname='wordpress'\#数据库端口port='3306'\#备份输出路径backpath='/data1/backup/mysql/'logpath='/data1/backup/logs/'\#备份时间backtime=\`date +%Y%m%d%H%M\`out\_time=\`date +%Y%m%d%H%M%S\`\#Define DIR and mkdir DIR 判断目录是否存在，不存在则新建if \[ ! -d $backpath \];then mkdir -p $backpath echo -e "\\033\[35m $backpath 这个目录创建成功!!!\\033\[1m"else echo -e "\\033\[35m $backpath 这个目录已经存在...\\033\[0m" fi\#Define DIR and mkdir DIR 判断目录是否存在，不存在则新建if \[ ! -d $logpath \];then mkdir -p $logpath echo -e "\\033\[35m $logpath 这个目录创建成功!!!\\033\[1m"else echo -e "\\033\[35m $logpath 这个目录已经存在...\\033\[0m" fi\#\#ROOT执行，否则退出root=$(id -u)if \[ "$root" -ne 0 \] ;then echo must run as root exit 1fiecho "\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# $\{backtime\} \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#" echo "开始备份" \#日志记录头部echo "" >> $\{logpath\}/$\{dbname\}\_back.logecho "-------------------------------------------------" >> $\{logpath\}/$\{dbname\}\_back.logecho "备份时间为$\{backtime\},备份数据库 $\{dbname\} 开始" >> $\{logpath\}/$\{dbname\}\_back.log\#正式备份数据库for DB in $dbname; do \#source=\`/usr/bin/mysqldump -h $\{dbserver\} -u $\{dbuser\} -p$\{dbpasswd\} -P$\{port\} $\{DB\} > $\{backpath\}/$\{DB\}-$\{out\_time\}.sql\` 2>> $\{backpath\}/mysqlback.log; source=\`/usr/bin/mysqldump --defaults-extra-file=/sh/.my.cnf $\{DB\} > $\{backpath\}/$\{DB\}-$\{out\_time\}.sql\` \#数据成功安全导出 echo "" >> $\{backpath\}/mysqldump.log echo "-----------------------------------------------------------" >> $\{backpath\}/mysqldump.log echo "$\{out\_time\}: The $\{DB\} successfully exported safely." >> $\{backpath\}/mysqldump.log\#备份成功以下操作 if \[ "$?" == 0 \];then cd $backpath \#为节约硬盘空间，将数据库压缩 tar zcvf $\{DB\}-$\{backtime\}.tar.gz $\{DB\}-$\{out\_time\}.sql > /dev/null \#删除原始文件，只留压缩后文件 rm -f $\{DB\}-$\{out\_time\}.sql \#删除15天前备份，也就是只保存15天内的备份 find $backpath -name "\*.tar.gz" -type f -mtime +15 -exec rm -rf \{\} \\; > /dev/null 2>&1 echo "数据库 $\{dbname\} 备份成功!!" >> $\{logpath\}/$\{dbname\}\_back.log else \#备份失败则进行以下操作 echo "数据库 $\{dbname\} 备份失败!!" >> $\{logpath\}/$\{dbname\}\_back.log fidoneecho "完成备份"echo "\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# $\{backtime\} \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#"