Linux shell脚本、定时备份mysql数据库、sendMail邮件发送

数据库备份

定时任务

邮件发送

一、数据库备份

vim /sqlbackup/mysql_backup.sh

#!/bin/sh
###定义变量####
date=`date "+%Y-%m-%d"`
ago10date=`date +%Y-%m-%d --date="-10 day"`
backuser=root
db_host=localhost
backupdir=/sqlbackup

###判断存放目录是否存在####
if [ ! -d $backupdir ];then
mkdir -p $backupdir
fi

###判断第二级存放目录是否存在####
if [ ! -d $backupdir/$date ];then
mkdir -p $backupdir/$date
fi

echo $backupdir/$ago10date
##########执行备份命令#############
mysqldump -u$backuser -h $db_host -p'password' --single-transaction jane_fun > "$backupdir/$date/website.sql"

##########删除10天前的备份#########
if [ -d $backupdir/$ago10date ]
then
rm -rf $backupdir/$ago10date
fi

二、添加定时任务

-- 安装cron定时任务
yum -y install vixie-cron
yum -y install crontabs

-- 查看crond.serivce服务的自启动状态
systemctl is-enabled crond.service

-- 开启crond.serivce服务自启动
 systemctl enable crond.service

vim /etc/crontab

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed
# 每天15:05启动
  05 15 * * * root /sqlbackup/mysql_backup.sh

cron表达式参考

三、邮件通知

服务安装

yum install sendmail -y

systemctl status sendmail # 查看sendmail运行状态
systemctl start sendmail # 启动
systemctl enable sendmail # 设置开机自启
systemctl is-enabled sendmail # 查看是否设置开机自启

yum install mailx -y

邮件设置

#vim /etc/mail.rc
#在文件末尾加上
set from=xxxxxx@qq.com #发送人
set smtp=smtps://smtp.qq.com:465 #qq邮箱的smtp server和端口
set smtp-auth-user=xxxxx@qq.com #发送人的用户名
set smtp-auth-password=etsxxxxxxceiebig #授权码 具体可以登录自己的QQ邮箱开启smtp并且获取到授权码
set smtp-auth=login #默认
set smtp-use=starttls
set ssl-verify=ignore
#存放QQ邮箱SSL证书的位置
set nss-config-dir=/root/.certs/

获取证书

mkdir -p /root/.certs/
echo -n | openssl s_client -connect smtp.qq.com:465 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ~/.certs/qq.crt
certutil -A -n "GeoTrust SSL CA" -t "C,," -d ~/.certs -i ~/.certs/qq.crt
certutil -A -n "GeoTrust Global CA" -t "C,," -d ~/.certs -i ~/.certs/qq.crt
certutil -L -d /root/.certs
cd /root/.certs/
certutil -A -n "GeoTrust SSL CA - G3" -t "Pu,Pu,Pu" -d ./ -i qq.crt

测试

echo 'test' | mail -s 'test' xxxx@qq.com

脚本

#!/bin/bash

#设置你的qq邮箱
EMAIL_FROM=xxxx@qq.com
#设置126邮箱
EMAIL_TO=xxxx@qq.com,xxx@qq.com

echo -e "`date "+%Y-%m-%d %H:%M:%S"` : Please to check the fail sql attachement." \
| mailx \
-r "From: alertAdmin <${EMAIL_FROM}>" \
-a /private/application/t.sql \
-s "数据库备份" ${EMAIL_TO}

四、完整脚本

#!/bin/sh
###定义变量####
date=`date "+%Y-%m-%d"`
ago10date=`date +%Y-%m-%d --date="-10 day"`
backuser=root
db_host=localhost
backupdir=/sqlbackup
EMAIL_FROM=xxx@qq.com
#设置qq邮箱 多个收件人，隔开
EMAIL_TO=xxx@qq.com,xxx@qq.com,xxx@qq.com


###判断存放目录是否存在####
if [ ! -d $backupdir ];then
mkdir -p $backupdir
fi

###判断第二级存放目录是否存在####
if [ ! -d $backupdir/$date ];then
mkdir -p $backupdir/$date
fi

echo $backupdir/$ago10date
##########执行备份命令############# 多个数据库
mysqldump -u$backuser -h $db_host -p'password' --single-transaction --databases  app_band_aiyge app_band_version band_official bo_sen c_work cloud_atlas color_home cyys_official evi_jia fei_luo_ke hui_xin jane_fun jin_yi li_zhan luo_man my_blog shao_pai syys_app web_admin yang_admin yang_erp zuo_yi  > "$backupdir/$date/website.sql"

##########删除10天前的备份#########
if [ -d $backupdir/$ago10date ]
then
rm -rf $backupdir/$ago10date
fi

if [ ! -f /$backupdir/$date/website.sql ];then
echo -e "`date "+%Y-%m-%d %H:%M:%S"` : 数据库备份失败" \
| mailx \
-r "From: alertAdmin <${EMAIL_FROM}>" \
-s "数据库备份" ${EMAIL_TO}
else
  echo -e "`date "+%Y-%m-%d %H:%M:%S"` :数据库备份 成功" \
| mailx \
-r "From: alertAdmin <${EMAIL_FROM}>" \
-a /$backupdir/$date/website.sql \
-s "数据库备份" ${EMAIL_TO}
fi