公司新项目上线,今天建立全网备份
,现在这记录的是Mysql
部分
备份方式:全备+Binlog
备份地点:本地+异地
备份时间:每日0点,切割Binlog日志
1,环境介绍:
[root@jm1j-node1 scripts]# cat /etc/redhat-release
CentOS release 6.8 (Final)
[root@jm1j-node1 scripts]# mysql -V
mysql Ver 14.14 Distrib 5.6.35, for linux-glibc2.5 (x86_64) using EditLine wrapper
2,规范脚本存放目录
mkdir -p /server/scripts #规范是根本
[root@DB01 ~]# vi /server/scripts/mysqlbackup.sh #创建脚本文件
3,编写脚本内容如下
#!/bin/bash
#---------------------------------------------------------
# $Name: mysqlbackup.sh
# $Version: v1.0
# $Author: qiuyuetao
# $organization: https://www.dgstack.cn
# $Create Date: 2018-11-25
# $Description: Mysql backup script
#---------------------------------------------------------
#source /etc/init.d/functions
DAY=`date +%Y-%m-%d\-%H-%M`
BACK_DIR="/data/backup/mysql/dump"
DBUSER="root" #数据用户
DBPASSWD="***数据库密码"
BIN_PATH="/application/mysql/bin" #全局变量,可以使用which获取
LOG="/var/log/mysqlback.log" #备份日志
CKLOG="/var/log/bkmd5-"$DAY".log" #md5加密日志,后期发送邮件
## Close all tables and refresh log ####
"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "flush logs"
#"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "flush tables with read lock"
echo "mysqldump start `date +%F\ %H\:%M\:%S`" >> "$LOG"
## Each backup database to the target location ####
for i in `"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "show databases" |grep -vE "Database|information_schema|performance_schema"`
do
if [ ! -d "$BACK_DIR"/"$i" ]; then
mkdir -p "$BACK_DIR"/"$i"
fi
"$BIN_PATH"/mysqldump -u "$DBUSER" -p"$DBPASSWD" --default-character-set=utf8 --events --master-data=2 --lock-all-tables -f --log-error="$LOG" -B "$i"|gzip > "$BACK_DIR"/"$i"/"$DAY".sql.gz
md5sum "$BACK_DIR"/"$i"/"$(date +%F --date='0 days ago')"-*.sql.gz >>$CKLOG
done
echo "mysqldump stop `date +%F\ %H\:%M\:%S`" >> "$LOG"
echo "" >>"$LOG"
## Delete old 10day backup files 保留最近10天的数据####
for RM in `"$BIN_PATH"/mysql -u "$DBUSER" -p"$DBPASSWD" -e "show databases" |grep -vE "Database|information_schema|performance_schema"`
do
rm -rf "$BACK_DIR"/"$RM"/$(date +%F --date='10 days ago')*.sql.gz
done
echo 生产数据库备份完整性MD5初始值 |mail -s 备份校验 -a "$CKLOG" qiuyt@*****.com ##自己的邮箱
4,添加执行权限
[root@DB01 ~]# chmod +x /server/scripts/mysqlbackup.sh #添加执行权限
5,执行备份
[root@DB01 ~]# sh -x /server/scripts/mysqlbackup.sh
##查看日志
[root@DB01 dump]# cat /var/log/bkmd5-2018-11-25-12-32.log
76e90da81b805721a9f252120921916b /data/backup/mysql/dump/BITs/2018-11-25-12-32.sql.gz
f07b390f5adcdfab535a28bb7bedc86f /data/backup/mysql/dump/mysql/2018-11-25-12-32.sql.gz
562d9f88075c9f4ff1b88c53a7c8367b /data/backup/mysql/dump/test/2018-11-25-12-32.sql.gz
0edd81c125cf3ab03b0f2df1e7fdeda6 /data/backup/mysql/dump/test_wenda/2018-11-25-12-32.sql.gz
3ec3961ac32521a261c21038d47d8819 /data/backup/mysql/dump/vdms/2018-11-25-12-32.sql.gz
43dba97f279ccdd2c907619a58dc6593 /data/backup/mysql/dump/ylhc_test/2018-11-25-12-32.sql.gz
#如果没有发送邮件,请检查服务是否安装,启动,25端口是否启用等等,然后手动发送邮件测试
6,配置邮箱
1)查看是否安装mailx
[root@DB01 ~]# rpm -qa|grep mailx
mailx-12.4-8.el6_6.x86_64
libreport-plugin-mailx-2.0.9-32.el6.centos.x86_64
2)修改mail配置
set bsdcompat
set from=*****@163.com #自己的邮箱
set smtp=smtp.163.com
set smtp-auth-user=*****@163.com #自己的邮箱
set smtp-auth-password=***** ##授权码
set smtp-auth=login
如果无法收到邮件,请mail -s 手动测试
[root@DB01 ~]# echo "test" | mailx -v -s "tes88t" qiuyuetao@163.com
已收到邮件
7,定时备份 (正常可以加入crontab定时任务,每天0点执行)
上面没有找到,下面命令安装
yum install -y vixie-cron
添加定时任务
crontab -e
#mysqlbackup by qyt at 2018-11-25
00 00 * * * /bin/sh /server/scripts/mysqlbackup.sh >/dev/null 2>&1
8,备份邮件通知
每天就会收到,备份的邮件通知,代表备份成功。
如果想异地备份
,有多重方式,为了减少服务器压力,我使用rsync 客户端拉取方式
- QQ精品交流群
-
- 微信公众号
-