mysql 备库,【MySQL】数据库备库策略与脚本

全量备份脚本

[mysql@wallet01 ~]$ vim full_backup.sh

#!/bin/bash

user='xtrabackup'

passwd='xtrabackup'

backup_dir='/home/mysql/backup'

/usr/bin/xtrabackup --backup --user=$user --password=$passwd --target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`

[mysql@wallet01 ~]$ chmod a+x full_backup.sh

增量备份脚本

[mysql@wallet01 ~]$ vi incr_backup.sh

#!/bin/bash

user='xtrabackup'

passwd='xtrabackup'

backup_dir='/home/mysql/backup'

last_day=$(date -d "1 days ago" +%Y-%m-%d)

filename=$(find $backup_dir -name "$last_day*" -print|awk -F / '{print $NF}')

/usr/bin/xtrabackup --backup --user=$user --password=$passwd --incremental-basedir=$backup_dir/$filename

--target-dir=$backup_dir/`date '+%Y-%m-%d_%H-%M-%S'`

[mysql@wallet01 ~]$ chmod a+x incr_backup.sh

备份策略：周日全量备份，周一至周六增量备份

[mysql@wallet01 ~]$ crontab -e

00 3 * * 0 /home/mysql/full_backup.sh>/home/mysql/backup/full_backup.log 2>&1

00 3 * * 1-6 /home/mysql/incr_backup.sh>/home/mysql/backup/incr_backup.log 2>&1

保留最近两周的备份文件

[mysql@wallet01 ~]$ find /home/mysql/backup/ -type d -mtime +14 -exec rm -rf {} \;