MySql 之定时备份数据库

1. 创建 .sh 文件 bak.sh 

#!/bin/bash
# 设置mysql的登录用户名和密码
source /etc/profile
mysql_user="账号"
mysql_password="密码"
mysql_host="localhost"
mysql_port="3408"
mysql_charset="utf8"
mysql_database="备份的数据库"
# 备份文件存放地址(根据实际情况填写)
backup_location=/sjgr2020/mysql-backup/
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=1
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
	echo "ERROR:MySQL is not running! backup stop!"
	exit
else
	echo $welcome_msg
fi
# 备份指定数据库中数据
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $mysql_database> $backup_dir/$mysql_database-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
	echo "database $mysql_database success backup to $backup_dir/$mysql_database-$backup_time.sql.gz"
else
	echo "database $mysql_database backup fail!"
fi
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
	`find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
	echo "Expired backup data delete complete!"
fi

2. 创建定时任务

2.1 新增定时任务

vi /etc/crontab   # 编辑新增定时任务

00 03 * * * /sjgr2020/info/bak.sh   # 添加 每天凌晨3点执行备份，进行 wq! 保存退出

crontab /etc/crontab  # 使之生效

crontab -l   # 查看是否存在

2.2 重启 刷新配置  在查看状态

service crond stop //关闭服务
service crond start //启动服务
service crond restart //重启服务
service crond reload //重新载入配置
service crond status // crontab服务状态