1. 创建MySQL备份账号
创建账号并分配权限
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, PROCESS, FILE, SUPER, LOCK TABLES ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
注:如果不在同一台机器将localhost改成备份机的IP。
2. 编写备份脚本
#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_FILE="/data/backup/backup_$DATE.sql"
mysqldump -h host -ubackup -ppassword database > $BACKUP_FILE
参数说明:
- host:MySQL主机IP地址
- backup:MySQL备份账号
- password:MySQL备份账号密码
- database:要备份的数据库名称
3. 配置Crontab定时任务
添加定时任务
[root@localhost ~]# crontab -e
0 1 * * * /data/backup/backup.sh
查看定时任务列表
[root@localhost ~]# crontab -l
0 1 * * * /data/backup/backup.sh