CentOS7定时任务cron与数据库备份

crond基本命令

# 查询crond状态
systemctl status crond
# 启动crond
systemctl start crond
# 重启crond
systemctl restart crond
# 查看定时任务执行日志
tail /var/log/cron
创建任务

新建shell脚本，如输出日期到一个文件test.sh

#!/bin/bash
date >> /usr/local/zyq/dbback/time.log # 一定要绝对路径

然后保存退出，改变一下权限

chmod +x test.sh

接着执行 vi /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
# 每分钟执行
* * * * * root sh /usr/local/zyq/dbback/test.sh
# 每天凌晨1点执行
0 1 * * * root nohup sh /usr/local/zyq_data/mysql/mysql_database.sh >> /usr/local/zyq_data/mysql/mysql_database.log 2>&1&
0 1 * * * root nohup sh /usr/local/zyq_data/mysql/mysql_table.sh >> /usr/local/zyq_data/mysql/mysql_table.log 2>&1&

然后保存退出，重启 crond 即可查看日志已经运行任务了。
这里提供一下备份MYSQL 库与表的shell脚本

整个库备份 mysql_database.sh
#!/bin/bash

#用户名
mysql_user="xxxx"
#密码
mysql_password="xxxx"
mysql_host="xxxx.xx.xx.xx"
#端口号 
mysql_port="xxxx"
#将要备份的数据库
database_name="xxxx"
#编码格式
mysql_charset="utf8mb4"

#保存备份文件最多个数
count=3
#备份保存路径
backup_path=/usr/local/zyq_data/mysql/bakup
#日期
date_time=`date +%Y-%m-%d-%H_%M_%S`

#文件名称
file_name=$database_name$date_time"_database"

#日志文件名称
log_name=$database_name$date_time"_database_dump.log"


#如果文件夹不存在则创建
if [ ! -d $backup_path ]; 
then     
    mkdir -p $backup_path; 
fi
#查看MySQL是否在运行
mysql_ps=`ps -ef |grep mysql |wc -l` 
echo "$(date "+%Y-%m-%d %H:%M:%S") 查看MySQL是否在运行：$mysql_ps" >> $backup_path/$log_name
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "$(date "+%Y-%m-%d %H:%M:%S") ERROR:MySQL is not running! backup stop!" >> $backup_path/$log_name
        exit
else
        echo "$(date "+%Y-%m-%d %H:%M:%S") Welcome to use MySQL backup tools!" >> $backup_path/$log_name

echo "$(date "+%Y-%m-%d %H:%M:%S") MySQL connect ok! Please wait......" >> $backup_path/$log_name
fi
#开始备份
echo "$(date "+%Y-%m-%d %H:%M:%S") $database_name 开始备份...$backup_path" >> $backup_path/$log_name
/usr/local/mysql/bin/mysqlpump --single-transaction -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database_name --default-character-set=$mysql_charset > $backup_path/$file_name.sql 2>> $backup_path/$log_name
echo "$(date "+%Y-%m-%d %H:%M:%S") 备份完成...$backup_path" >> $backup_path/$log_name

#开始压缩
cd $backup_path
tar -zcvf $file_name.tar.gz $file_name.sql
#更新备份日志
echo  "$(date "+%Y-%m-%d %H:%M:%S") 压缩原文件 create $backup_path/$file_name.tar.gz" >> $backup_path/$log_name

#删除源文件
rm -rf $backup_path/$file_name.sql

echo "$(date "+%Y-%m-%d %H:%M:%S") 压缩后删除原文件 delete $backup_path/$file_name.sql" >> $backup_path/$log_name

#找出需要删除的备份
delfile=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于阈值
number=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`

if [ $number -gt $count ]
then
  #删除最早生成的备份，只保留count数量的备份
  rm $delfile
  #更新删除文件日志
  echo "$(date "+%Y-%m-%d %H:%M:%S") 删除 $count 前的文件 delete $delfile" >> $backup_path/$log_name
  echo "$database_name $(date "+%Y-%m-%d %H:%M:%S") 备份成功" >> $backup_path/$log_name
fi
库的所有表单独备份 mysql_table.sh
#!/bin/bash

#用户名
mysql_user="xxxx"
#密码
mysql_password="xxxx"
mysql_host="xxx.xx.xx.xx"
#端口号 
mysql_port="xxxx"
#将要备份的数据库
database_name="xxxx"
#编码格式
mysql_charset="utf8mb4"

#保存备份文件最多个数
count=3
#备份保存路径
backup_tar_path=/usr/local/zyq_data/mysql/bakup
#日期
date_time=`date +%Y-%m-%d-%H_%M_%S`

#文件夹名称
folder_name=$database_name$date_time"_table"
backup_path=$backup_tar_path/$folder_name

#日志文件名称
log_name=$database_name$date_time"_table_dump.log"

#如果文件夹不存在则创建
if [ ! -d $backup_path ]; 
then     
    mkdir -p $backup_path; 
fi
#查看MySQL是否在运行
mysql_ps=`ps -ef |grep mysql |wc -l` 
echo "$date_time 查看MySQL是否在运行：$mysql_ps" >> $backup_tar_path/$log_name
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "$date_time ERROR:MySQL is not running! backup stop!" >> $backup_tar_path/$log_name
        exit
else
        echo "$date_time Welcome to use MySQL backup tools!" >> $backup_tar_path/$log_name

echo "$date_time MySQL connect ok! Please wait......" >> $backup_tar_path/$log_name
fi

echo "$database_name 开始备份:$(date "+%Y-%m-%d %H:%M:%S")"
echo "$database_name 开始备份:$(date "+%Y-%m-%d %H:%M:%S")" >> $backup_tar_path/$log_name
#查询所有表
TABLES=$(mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database_name -s -e "SHOW TABLES")
for table in $TABLES; do
    #去掉系统默认表    
    if [ "$table" != "information_schema" ] && [ "$table" != "mysql" ] && [ "$table" != "sys" ] && [ "$table" != "performance_schema" ]; then
        #开始备份
        echo "$(date "+%Y-%m-%d %H:%M:%S") ...$database_name-$table-$date_time.sql"
        echo "$(date "+%Y-%m-%d %H:%M:%S") 开始备份...$backup_path/$database_name-$table-$date_time.sql" >> $backup_tar_path/$log_name
        /usr/local/mysql/bin/mysqlpump --single-transaction -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database_name $table --default-character-set=$mysql_charset > $backup_path/$database_name-$table-$date_time.sql 2>> $backup_tar_path/$log_name
        echo "$(date "+%Y-%m-%d %H:%M:%S") 备份完成...$backup_path/$database_name-$table-$date_time.sql" >> $backup_tar_path/$log_name
    fi
done

#开始压缩
echo "$(date "+%Y-%m-%d %H:%M:%S") $folder_name 开始压缩" >> $backup_tar_path/$log_name
cd $backup_tar_path
#tar -zcvf $backup_path.tar.gz $backup_path
tar -zcvf $folder_name.tar.gz $folder_name
echo "$(date "+%Y-%m-%d %H:%M:%S") $folder_name 完成压缩" >> $backup_tar_path/$log_name
#更新备份日志
echo  "$date_time 压缩原文件 create $backup_path.tar.gz" >> $backup_tar_path/$log_name

#删除源文件
#rm -rf $backup_path/$database_name-$table-$date_time.sql

#echo "$date_time 压缩后删除原文件 delete $backup_path/$database_name-$table-$date_time.sql" >> $backup_tar_path/$log_name

#找出需要删除的备份
delfile=`ls -l -crt  $backup_tar_path/*.tar.gz | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于阈值
number=`ls -l -crt  $backup_tar_path/*.tar.gz | awk '{print $9 }' | wc -l`

if [ $number -gt $count ];then
  #删除最早生成的备份，只保留count数量的备份
  rm $delfile
  #更新删除文件日志
  echo "$date_time 删除 $count 前的文件 delete $delfile" >> $backup_path/$log_name
  echo "$date_time 备份成功" >> $backup_path/$log_name
fi
echo "$database_name 结束备份:$(date "+%Y-%m-%d %H:%M:%S")" >> $backup_tar_path/$log_name
echo "$database_name 结束备份:$(date "+%Y-%m-%d %H:%M:%S")"

复制即可执行。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容