定时备份docker部署的mysql数据

「爱情、让人受尽委屈。」 2022-12-31 06:26 138阅读 0赞

本文使用的是阿里云服务器测试

1.新建脚本  vi mysql\_backup.sh

添加脚本内容

#!/bin/bash
    #设置mysql的登录用户名和密码
    mysql_user="root"
    mysql_password="123456"
    mysql_host="localhost"
    mysql_port="3306"
    
    # 需要备份的数据库
    database="ry-vue"
    # mysql容器名称
    docker_name="mysql"
    # 备份文件存放地址
    backup_location=/home
    
    # 是否删除过期数据
    expire_backup_delete="ON"
    expire_days=7
    backup_time=`date +%Y%m%d%H%M`
    backup_dir=$backup_location
    
    # 备份指定数据库中数据(数据库为ry-vue)
    docker exec -it ${docker_name} mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $database > $backup_dir/$backup_time.sql
    
    
    # 删除过期数据
    if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
            `find $backup_location -name "*.sql" -type f -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1`
            echo "Expired backup data delete complete!"
    fi

#删除七天之前的备份
    find $backup_location -name "*.sql" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
    #删除一分钟之前的备份
    find $backup_location -name "*.sql" -type f -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1

2.添加执行权限 chmod +x mysql\_backup.sh

3.添加定时任务 crontab -e     (测试每分钟备份1次)

*/1 * * * * /hoem/mysql_backup.sh

4. 重启定时任务：

service crond restart

![20201229190704572.png][]

5.恢复数据.在mysql命令行执行sql语句操作导入

mysql -uusername -ppassword databasename < backupfile.sql

### 将备份sql发送到指定邮箱 ###

1、yum -y install sendmail

2、yum -y install mailx

3、修改配置文件,在末尾添加  vi /etc/mail.rc

set ssl-verify=ignore
    set nss-config-dir=/root/.certs
    set from=jl313401@163.com
    set smtp=smtps://smtp.163.com:465
    set smtp-auth-user=jl313401@163.com
    set smtp-auth-password=RYKLZAKTLZWSBUIP0
    set smtp-auth=login

为了防止垃圾邮件泛滥，阿里云服务器已不再开放smtp邮件的25端口，现在需要使用465端口进行smtp邮件发送。  
4、生成密钥   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 163.crt
    
    certutil -L -d /root/.certs

5.测试  mail -s '测试主题' 31346337@qq.com < /home/test.txt

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70][]

6.发送附件  -a后面是要发送的附件,填写绝对路径

echo $(date +%F)"数据库备份"|mail -s $(date +%F)"数据库备份" -a /home/test.txt 31346337@qq.com

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1][]

7.定时发送

可以压缩后发送 tar zcf /home/databackup.sql.tar.gz /home/

最终备份加发送的脚本如下

#!/bin/bash
    #设置mysql的登录用户名和密码
    mysql_user="root"
    mysql_password="123456"
    mysql_host="localhost"
    mysql_port="3306"
    # 需要备份的数据库
    database="ry-vue"
    # mysql容器名称
    docker_name=mysql
    # 备份文件存放地址
    backup_location=/home
    
    # 是否删除过期数据
    expire_backup_delete="ON"
    expire_days=7
    backup_time=`date +%Y%m%d%H%M`
    backup_dir=$backup_location
    
    # 备份容器中的数据库
    docker exec -i ${docker_name} /usr/bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $database > $backup_dir/$backup_time.sql
    
    # 删除过期数据
    if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
            `find $backup_location -name "*.sql" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1`
    fi
    
    tar zcf /home/databackup.sql.tar.gz /home/
    echo $(date +%F)"数据库备份"|mail -s $(date +%F)"数据库备份" -a /home/databackup.sql.tar.gz 31346337@qq.com

使用终端远程Linux服务器登陆后, 经常会看到 You have new mail in /var/spool/mail/root 这样的提示.一般这种情况mail的内容就只是一些正常的系统信息或者是比较重要的错误报告。如果你安装了mutt的话直接用这个命令就可以查看mail的内容，没有装的话用 cat 命令查看。(前提是要使用root账号登陆)

cat /var/spool/mail/root

关闭提示

echo "unset MAILCHECK">> /etc/profile
    source /etc/profile

清空

cat /dev/null > /var/spool/mail/root

[20201229190704572.png]: /images/20221120/324482c559b9494eb9b8d14e44b88af9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70]: /images/20221120/1b70d974279343c6a1a05fdabec4c9e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5OTQwMjA1_size_16_color_FFFFFF_t_70 1]: /images/20221120/e41d0277b58d4497bc20a521cd58e11a.png