mysql备份脚本

1. 写个功能简单的备份脚本，省的忘了

#!/bin/bash

DATE=`date +%Y%m%d%H`

function backup() {
        /usr/local/mysql/bin/mysqldump --defaults-file=/usr/local/mysql/data/.pass -B cmdb -q --single-transaction > /opt/backup/mysql/${DATE}.sql 2>&1 >> /opt/backup/mysql/log
}

function clean() {
        find /opt/backup/mysql/ -name "*.sql" -mtime +7 -delete
}

clean
backup

2. 创建一个用于备份的账号

# 登录mysql -u root -p

# 授权
mysql> GRANT PROCESS, SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, TRIGGER ON *.* TO 'backup'@'127.0.0.1' identified by 'xxxx@backup';
mysql> flush privileges;

3. 认证文件/usr/local/mysql/data/.pass（位置任意，名字任意）

[mysqldump]
host=127.0.0.1
user=backup
password=xxxx@backup

4. 加入计划任务

# for dump mysql daily
0 8,12,18 * * * /opt/script/backup_mysql.sh

5. save to remote

rsync -aPv /opt/backup/mysql/ user@ip:/backup/