创建备份用户
create user dumper@'127.0.0.1' identified by '12345678';
赋权限
grant select on *.* to dumper@'127.0.0.1';
grant lock tables on *.* to dumper@'127.0.0.1';
备份脚本
#!/bin/bash
mkdir /tmp/`date +%y%m%d`
mysqldump -h127.0.0.1 -u dumper -p12345678 dbname > /tmp/`date +%y%m%d`/db.sql
crontab定时任务
凌晨三点执行脚本。
0 3 * * * /opt/software/backup_db.sh >/dev/null 2>&1