首页 » Linux » 正文

MySQL自动备份数据并通过邮箱发送

| Linux,MySQL | | 暂无评论 | 1551 views 
实验环境:腾讯云服务器
操作系统：CentOS 7
   腾讯云、阿里云默认关闭了25端口，需要采用465端口通过smtp来发送邮件，由于个人博客一般数据量都不大，可以直接备份并通过邮件来把数据直接以附件形式发送到指定邮箱，数据量大也可以备份完成之后自动发送邮件到指定邮箱提示管理人员进行数据备份检查

1、部署SMTP发信环境

yum install -y mailx
##停止相应的邮件发送服务
systemctl stop sendmail  postfix 
systemctl disable sendmail  postfix

2、请求数字证书

##采用的是163邮箱作为发信邮箱，所以请求的是163证书
##如果是QQ邮箱，把smtp.163.com改成smtp.qq.com即可，文件名根据实际情况修改
mkdir -p /root/.certs/
echo -n | openssl s_client -connect smtp.163.com:465 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ~/.certs/163.crt
certutil -A -n "GeoTrust SSL CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -A -n "GeoTrust Global CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -A -n "GeoTrust SSL CA - G3" -t "Pu,Pu,Pu" -d ~/.certs/./ -i ~/.certs/163.crt
certutil -L -d /root/.certs

3、修改mailx配置文件

###xxxx修改为你的邮箱地址，password写入你的密码，如果是授权码写入授权码
cat >> /etc/mail.rc << EOF
set from=xxxx@163.com
set smtp="smtps://smtp.163.com:465"
set smtp-auth-user=xxxx@163.com
set smtp-auth-password=你的密码
set smtp-auth=login
set ssl-verify=ignore
set nss-config-dir=/root/.certs
EOF
chmod 600 /etc/mail.rc

4、创建数据库备份用户

##打开/etc/my.cnf文件，没有直接创建
##添加以下内容
[client]
host=数据库服务器IP地址
user=数据库备份用户名
password=数据库备份密码

chmod 400 /etc/my.cnf

##创建备份用户，采用最小化权限原则
CREATE USER backup@'%' IDENTIFIED BY 'backup';
GRANT lock tables,reload,process,replication client,super,select,event,trigger,show view ON *.* TO backup@'%';
FLUSH PRIVILEGES;

使用逻辑备份工具备份时可能需要的权限：

    逻辑备份工具：mysqldump,mysqlpump,mydumper等等

    权限：SELECT

    作用：查询表中数据

    权限：SHOW VIEW

    作用：查看创建视图的语句

    权限：TRIGGER

    作用：备份触发器

    权限：EVENT

    作用：备份事件（定时任务）

    权限：lock tables

    作用：备份时锁表，产生一致性备份

    权限：reload

    作用：show processlist,show engine innodb status,查看线程，查看引擎状态

    权限：replication client

    作用：show master/slave status;查看事务日志执行状态与位置

           show binary logs；查看当前保存的事务日志列表与文件大小

    权限：super

    作用：关闭线程，不受最大连接线程数限制的VIP连接通道，阻断刷新线程的命令，不受离线模式影响

super权限可以防止因为线程满，备份任务无法连接数据库而导致的备份翻车。且阻断刷新线程也是很重要

innobackupex主要以物理文件和备份缓存文件的方式进行，所以不需要show权限与select权限

逻辑备份的基本原理就是数据全部读取，必须select与show权限，查看表定义的权限由select权限提供

login-path的以port+host的方式保存时，会在用户目录下生成.login.cnf文件，拷贝到网络互通的其他主机上，仍然可以登陆，方便的同时也留下祸根

5、备份脚本

touch /root/mysqldump.sh
chmod 700 /root/mysqldump.sh

##打开并输入以下内容

#!/bin/bash
#backup mysql databases
#by:波仔
#url:yisca.cn

#环境判断
[ -s /usr/local/mysql/bin/mysqldump ] && MYSQLDUMP=/usr/local/mysql/bin/mysqldump
[ -s /usr/bin/mysqldump ] && MYSQLDUMP=/usr/bin/mysqldump
[ -s /usr/sbin/mysqldump ] && MYSQLDUMP=/usr/sbin/mysqldump

#配置变量
DAY=`date "+%Y-%m-%d"`
[ -d /root/.MySQLDB/backup/${DAY} ] || mkdir -p /root/.MySQLDB/backup/${DAY}
BACKUP_DIR=/root/.MySQLDB/backup/${DAY}
HOST_IP=xxx.xxx.xxx.xxx
USER=xxxx
DATE=`date "+%Y年%m月%d日-%H时%M分%S秒"`
MAILADRESS="xxxx@qq.com"

#开始备份
${MYSQLDUMP} -u ${USER} --all-databases --triggers --routines --events -h ${HOST_IP} > ${BACKUP_DIR}/MySQL_DATABASES_${HOST_IP}_${DATE}.sql
cd  ${BACKUP_DIR} && tar -cJPf MySQL_DATABASES_${HOST_IP}_${DATE}.sql.tar.xz MySQL_DATABASES_${HOST_IP}_${DATE}.sql --remove-files 

#发送邮件
[ -s /usr/bin/mail ] || yum install mailx -y >> /dev/null
echo "MySQL于${DATE}的备份数据" | mail -s "MySQL备份数据" -a ${BACKUP_DIR}/MySQL_DATABASES_${HOST_IP}_${DATE}.sql.tar.xz ${MAILADRESS}

#自动压缩前一天的数据
cd /root/.MySQLDB/backup
Yesterday_DATA=`find -type d -mtime 1`
[ -n "${Yesterday_DATA}" ] && tar -cJPf ${Yesterday_DATA}.tar.xz  ${Yesterday_DATA} --remove-files

#自动删除10天前的数据
find /root/.MySQLDB/backup/* -mtime 10 -exec rm -rf {} \;

6、添加定时任务

###每日凌晨执行备份任务
###可根据实际情况调整
###每五分钟执行  */5 * * * * ("*/n * * * * "  当n不能被 60 整除的时候，不是每n分钟执行一次,而是每小时的n分钟，2n分钟等时候执行，到了新的1小时又重新开始计算，比如24，就是每小时的24,48分钟执行,参考链接https://my.oschina.net/u/2399303/blog/1809070/)
###每小时执行     0 * * * *
###每天执行        0 0 * * *
###每周执行       0 0 * * 0
### 每月执行        0 0 1 * *
###每年执行       0 0 1 1 *
echo -e "0 0 * * *  /root/mysqldump.sh >> /dev/null 2>&1" >> /var/spool/cron/root
systemctl reload crond

7、自动删除十天前的数据

find /root/.MySQLDB/backup/* -type d  -mtime +10 -exec rm -rf {} \;

发表评论