MySQL 自动备份

2019-10-16 必备技能阅读量

定时备份数据库是一件必须做的事情

备份脚本

#!/bin/sh
# DB_USERNAME
db_username='root'
# DB_PASSWORD
db_password='110119'
# DB_HOST
db_host='127.0.0.1'
# Backup DB_NAME
db_name='test'
# Backup Dir
backup_dir="test"
# Date Formate (dd-mm-yyyy)
time="$(date +"%Y_%m_%d_%H_%M_%S")"
# MYSQL CLI
MYSQL="/usr/local/mysql/bin/mysql"
# MySQLDump CLI
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
# Gzip CLI
GZIP="/bin/gzip"

$MYSQLDUMP -u $db_username -h $db_host -p$db_password $db_name | $GZIP -9 > "/data/backup/$time"_"$db_name.gz"

创建定时器

crontab -e

// 或者
vim /etc/crontab

添加定时器

1 2	# 每天凌晨 1 点 30 分进行数据备份 30 1 * * * root /root/mysql-backup.sh

注意: 需要生命脚本执行用户: 如root

重启定时器

1	[root@caoxl ~]# service crond restart

查看crontab状态

[root@caoxl ~]# service crond status
Redirecting to /bin/systemctl status crond.service
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2019-10-16 10:50:48 CST; 51s ago
 Main PID: 15813 (crond)
    Tasks: 1
   Memory: 620.0K
   CGroup: /system.slice/crond.service
           └─15813 /usr/sbin/crond -n

结果

[root@caoxl backup]# ll -s
total 12K
4.0K -rw-rw-r-- 1 root root 440 Oct 16 11:30 2019_10_16_11_30_38_test.gz
4.0K -rw-r--r-- 1 root root 440 Oct 16 11:38 2019_10_16_11_38_01_test.gz
4.0K -rw-r--r-- 1 root root 441 Oct 16 11:39 2019_10_16_11_39_01_test.gz