Linux 使用crontab定时备份Mysql数据库

ゝ一世哀愁。 2022-01-27 23:35 397阅读 0赞

**1、在usr/usr\_sh目录新建mysql\_bak.sh**

确保新建/opt/shell/目录、/usr/bin/mysql为mysql启动命令、/data/htdocs/web/WEB-INF/classes/project.properties为项目配置文件、/data/mysql\_bak/为数据库备份路径

**2、编写**mysql\_bak.sh**备份数据库脚本内容**

#!/bin/bash
    #此脚本直接执行即可备份，记得添加计划任务定时备份
    num=$(rpm -qa |grep dos2unix |wc -l)
    if [ $num != 1 ];then
            yum install dos2unix -y
    fi
    /usr/bin/dos2unix -k /data/htdocs/web/WEB-INF/classes/project.properties >/dev/null 2>&1
    #数据库地址用户名密码统一设置为变量
    myip=$(head -n 5  /data/htdocs/web/WEB-INF/classes/project.properties |grep "jdbc:mysql" |awk -F ":" '{print $3}'|awk -F "//" '{print $2}')
    username=$(head -n 5 /data/htdocs/web/WEB-INF/classes/project.properties |grep "jdbc.username"|awk -F "=" '{print $2}')
    pass=`head -n 5  /data/htdocs/web/WEB-INF/classes/project.properties |grep jdbc.pass|awk -F "=" '{print $2}'`
    #备份时间
    b=$(date +%Y_%m_%d_%H:%M)
    #MySQL备份路径
    mkdir -p /data/mysql_bak/$b
    #MySQL保留15备份记录
    find /data/mysql_bak/ -type d -mtime +15 -exec rm -rf {} \; >/dev/null 2>&1
    #远程备份数据库
    /usr/bin/mysql -u$username -p$pass -h $myip -e 'show databases' | grep -v information_schema | grep -v Database |grep -v performance_schema| grep -v mysql > /opt/shell/libname.txt
    for i in `cat /opt/shell/libname.txt`
    do
             /usr/bin/mysqldump -u$username -p$pass -h $myip  --default-character-set=utf8 --skip-lock-tables  ${i} |gzip > /data/mysql_bak/$b/${i}.sql.gz
    done

**3、使用crontab创建定时任务**

[root@rabbitmq2 /]crontab -e

添加0 \*/1 \* \* \*  /usr/usr\_sh/mysql\_bak.sh 每小时一次启动执行备份命令

**service crond restart重新启动定时任务**

**4、相关的crontab可以参考**[https://baijiahao.baidu.com/s?id=1609952845993989858&wfr=spider&for=pc][https_baijiahao.baidu.com_s_id_1609952845993989858_wfr_spider_for_pc]

**5、其中如果写了用户名和密码，linux会发送邮件到/var/spool/mail/root**

> mysql: \[Warning\] Using a password on the command line interface can be insecure.  
> mysqldump: \[Warning\] Using a password on the command line interface can be insecure.

[https_baijiahao.baidu.com_s_id_1609952845993989858_wfr_spider_for_pc]: https://baijiahao.baidu.com/s?id=1609952845993989858&wfr=spider&for=pc