1.创建备份用户
create user 'db_backup'@'%' identified by 'db_backup_password';
-- 赋zd_setmeal库只读权限
grant select on zd_setmeal.* to db_backup@"%";
grant lock tables on zd_setmeal.* to db_backup@'%';
flush privileges;
2.编写定时脚本
#!/bin/bash
db_name=zd_setmeal
db_host=127.0.0.1
db_port=3307
db_user=db_backup
db_pswd=db_backup_password
db_backup_path=/opt/db_back/
date_one=`date -d "1 days ago" +%Y%m%d`
file_name=$db_backup_path$db_name"_"$date_one".sql"
#删除七天前的备份
find $db_backup_path -name $db_name"*.sql" -type f -mtime +7 -exec rm -f {} \;
#导出数据
mysqldump -h$db_host -P$db_port -u$db_user -p$db_pswd $db_name > $file_name
echo "done"
3.设置定时任务
crontab -e
# 添加一条记录
1 0 * * * /bin/bash /opt/scripts/db_backup.sh > /opt/log/db_backup.log 2>&1