1. 准备工作
1) 创建用户并授权
grant all on *.* to 'xtrabackup'@'localhost' identified by '123456'
2) 下载XtraBackup
wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz
3) 解压缩XtraBackup
tar –xvf percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz
mv percona-xtrabackup-2.1.9-744-Linux-x86_64 /usr/local/xtrabackup
chown –R mysql.mysql /usr/local/xtrabackup
cd /usr/bin
ln -s /usr/local/xtrabackup/bin/innobackupex
ln -s /usr/local/xtrabackup/bin/xtrabackup_55
ln -s /usr/local/xtrabackup/bin/xtrabackup_56
ln -s /usr/local/xtrabackup/bin/xtrabackup
4)设置/etc/my.cnf
datadir=/usr/local/mysql/data/
5)安装依赖软件
yum -y install perl-Time-HiRes
yum -y install perl-DBD-MySQL
2. 物理备份脚本innobackup.sh
用today=`date +u` 整除7 判断是星期几,如果为0,周日全备,其它增量备份。如果是周一,已全备为基础增量备份,如果是其它,则以上一个增量备份为基础增量备份。同一个日期只保留一个备份,用crontab可实现自动备份。
#!/bin/sh
all_dir=/mysqlbackup/all
inc_dir=/mysqlbackup/inc
USER=xtrabackup
PASSWORD=123456
today=`date +u`
today2=`date +%Y-%m-%d`
today3=`date +%Y%m%d`
yesterday=`expr $today3 - 1`
if [ $(($today%7)) -eq 0 ]; then
rm -rf $all_dir/$today3 2>> $all_dir/all.log
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD $all_dir 2>> $all_dir/all
.log
mv $all_dir/$today2* $all_dir/$today3
elif [ $(($today%7)) -eq 1 ]; then
rm -rf $inc_dir/$today3 2>> $inc_dir/increment.log
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD --incremental $inc_dir --
incremental-basedir=$all_dir/$yesterday 2>> $inc_dir/increment.log
mv $inc_dir/$today2* $inc_dir/$today3
else
rm -rf $inc_dir/$today3 2>> $inc_dir/increment.log
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD --incremental $inc_dir --i
ncremental-basedir=$inc_dir/$yesterday 2>> $inc_dir/increment.log
mv $inc_dir/$today2* $inc_dir/$today3
fi
3. 逻辑备份脚本dumpbackup.sh
用mysqldump实现,删除前15天的备份。
#!/bin/sh
USER=xtrabackup
PASSWORD=123456
dump_dir=/mysqlbackup/dump
today=`date +%Y%m%d`
old_day=`expr $today - 15`
for db in `mysql -u$USER -p$PASSWORD -N -e "show databases"|grep -v -E "(percona|information_schema|performance_schema)"`
do
chattr -i $dump_dir/*
#rm -f $dump_dir/*$old_day.sql* &>/dev/null
filename=${db}_${today}
mysqldump -u$USER -p$PASSWORD --events --routines $db |gzip > $dump_dir/${filename}.sql.gz
chattr +i $dump_dir/*
done