Linux下定时备份数据库

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