MySQL定时备份数据库shell脚本

Myth丶恋晨 2023-02-21 11:51 4阅读 0赞

### MySQL定时备份数据库shell脚本 ###

*  mysqldump命令备份数据
 *  整个shell脚本
 *  使用crontab定期执行备份脚本

# mysqldump命令备份数据 #

数据库导出，主要用到mysqldump 命令

mysqldump  -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sql

# 整个shell脚本 #

具体脚本如下：

DB_HOST='localhost'
    DB_USER='root'
    DB_USER_PASSWD='***'
    DB_NAME='/home/dbbackup/dbnames.txt'
    BACKUP_PATH='/home/dbbackup/mysql/'
    
    printf "createing backup folder!\n"
    DATETIME=$(date +%Y%m%d_%H%M)
    TODAYBACKUPPATH="$BACKUP_PATH$DATETIME"
    #创建备份文件夹
    if [ ! -d "$TODAYBACKUPPATH" ];
    then
      printf "create dir $TODAYBACKUPPATH \n"
      mkdir -pv "$TODAYBACKUPPATH"
    fi
    printf "checking for databases names file\n"
    #定义执行备份脚本，读取文件中的数据库名称，注意按行读写，不校验是否存在该库
    run_backup (){ 
      #读取 DB_NAME文件中的内容
      for dbname in $(<$DB_NAME);do 
      #遍历循环执行备份sql
        printf "now starting backup database %s\n" $dbname
        printf "mysqldump -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sql \n"
        mysqldump  -h$DB_HOST -u$DB_USER -p$DB_USER_PASSWD $dbname > $TODAYBACKUPPATH/$dbname.sql
      done
    }
    
    #执行压缩的函数
    run_tar(){ 
      # 将今日的备份文件打包成压缩文件
      compress_file="$TODAYBACKUPPATH.tar.gz"
      cd $BACKUP_PATH
      pwd
      tar -czvf $compress_file $DATETIME
      printf "compress complete!\n"
      #删除备份文件夹
      rm -rf $TODAYBACKUPPATH
    }
    #删除30天前的文件
    del_30day_backup_file(){ 
      find ${ BACKUP_PATH} -name "*.tar.gz" -mtime +30 -exec rm -rfv { } \;
    }
    _main(){ 
     printf "starting backup of all db listed in file $DB_NAME \n"
     printf "time is $DATETIME\n"
     run_backup
     run_tar
     del_30day_backup_file
     exit 0
    }
    _main

dbnames.txt中内容  
![在这里插入图片描述][20200630142341998.png]

> 脚本中的DB\_HOST ，DB\_USER\_PASSWD，DB\_NAME，BACKUP\_PATH 根据情况自己更改。dbnames.txt 中一行只能有一个数据库名

# 使用crontab定期执行备份脚本 #

[crontab 使用详解][crontab]

crontab -e
    #将下面这条脚本意思是 每天晚上23点50分钟时调用 /home/firm-pms/39.dump.sh 脚本
    50 23 * * * /home/firm-pms/39.dump.sh >> /home/firm-pms/back_39_log.log

![在这里插入图片描述][20200630150831839.png]  
crontab -l 查看定时任务  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n_size_16_color_FFFFFF_t_70]

[20200630142341998.png]: https://img-blog.csdnimg.cn/20200630142341998.png
[crontab]: https://www.runoob.com/w3cnote/linux-crontab-tasks.html
[20200630150831839.png]: https://img-blog.csdnimg.cn/20200630150831839.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200630150854371.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n,size_16,color_FFFFFF,t_70