xtrabackup备份恢复

最新推荐文章于 2024-10-14 22:42:36 发布

weixin_34362991

最新推荐文章于 2024-10-14 22:42:36 发布

阅读量68

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/y920895/1786412

版权

#xtrabackup备份脚本，放在crontab每天执行。策略是每周进行一次全备，接下来6天执行增量备份，并保留上一周的一套备份。

#备份目录会存在全备full和rec0-5 六个增量备份目录。

#!/bin/bash

BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"`

format_time=`date +"%Y-%m-%d_%H:%M:%S"`

week=`date +%w`

port=3306

ip=192.168.1.222

backdir=/data/xtrabackup

file_cnf=/usr/local/mysql1/my.cnf

user_name=root

password=ffffff

out_log=$backdir/Xtrabackup.log

time_cost=$backdir/Xtrabackup_time.txt

if [ -d "$backdir/rec5" ];then

rm $backdir-bak -rf

mv $backdir $backdir-bak

if [ ! -f "$backdir/Xtrabackup_time.txt" ];then

mkdir $backdir

touch $backdir/Xtrabackup_time.txt

#full

if [ ! -d "$backdir/full" ];then

echo "#####start full backup at $BEGINTIME to directory full" >>$time_cost

mkdir $backdir/full

innobackupex --defaults-file=$file_cnf --no-timestamp --parallel=4 --throttle=400 --user=$user_name --password=$password --host=$ip --port=$port $backdir/full 1> $out_log 2>&1

elif [ ! -d "$backdir/rec0" ];then

echo "#####start 0 incremental backup at $BEGINTIME to directory rec0" >>$time_cost

mkdir $backdir/rec0

innobackupex --defaults-file=$file_cnf --no-timestamp --parallel=4 --throttle=400 --user=$user_name --password=$password --host=$ip --port=$port --incremental --incremental-basedir=$backdir/full $backdir/rec0 1> $out_log 2>&1

elif [ ! -d "$backdir/rec1" ];then

echo "#####start 1 incremental backup at $BEGINTIME to directory rec1" >>$time_cost

mkdir $backdir/rec1

elif [ ! -d "$backdir/rec2" ];then

echo "#####start 2 incremental backup at $BEGINTIME to directory rec2" >>$time_cost

mkdir $backdir/rec2

elif [ ! -d "$backdir/rec3" ];then

echo "#####start 3 incremental backup at $BEGINTIME to directory rec3" >>$time_cost

mkdir $backdir/rec3

elif [ ! -d "$backdir/rec4" ];then

echo "#####start 4 incremental backup at $BEGINTIME to directory rec4" >>$time_cost

mkdir $backdir/rec4

elif [ ! -d "$backdir/rec5" ];then

echo "#####start 5 incremental backup at $BEGINTIME to directory rec5" >>$time_cost

mkdir $backdir/rec5

ENDTIME=`date +"%Y-%m-%d %H:%M:%S"`

begin_data=`date -d "$BEGINTIME" +%s`

echo "Complete at $ENDTIME" >>$time_cost

恢复：

#prepare

innobackupex --defaults-file=/usr/local/mysql1/my.cnf --apply-log --redo-only --use-memory=4GB --user=root --password=fffffff --host=192.168.1.222 --port=3306 /tmp/full

innobackupex --defaults-file=/usr/local/mysql1/my.cnf --copy-back --user=root --password=fffffff --host=192.168.1.222 --port=3306 /tmp/full

#修改增量备份目录，逐个apply-log，到第五天的增量rec4都是输出都显示innobackupex-1.5.1: completed OK!

innobackupex-1.5.1 --apply-log --redo-only --defaults-file=$file_cnf --user=$user_name --password=$password --host=$ip --port=$port $backdir/full --incremental-dir=$backdir/rec5

chown -R mysql.mysql data/

[root@newbidb script]# ./mysql_install_db --basedir=/u01/mysql --no-defaults --skip-name-resolve --user=mysql --datadir=/u01/mysql/data

touch mysql.sock 并修改文件权限。

先做基于全备的apply，注意，此时使用了--redo-only

innobackupex --apply-log --redo-only --user=robin -password=xxx --port=3606 \ > --defaults-file=/etc/my3606.cnf /hotbak/full

基于增备的apply， --此时没有--redo-only，如果有多个增备，仅仅最后一个增备无需指定--redo-only

innobackupex --apply-log --user=robin -password=xxx --port=3606 --defaults-file=/etc/my3606.cnf \ > /hotbak/full --incremental-dir=/hotbak/inc

进行copy back

mv /data/inst3606/data3606 /data/inst3606/data3606bk

mkdir -p /data/inst3606/data3606

innobackupex --user=robin -password=xxx --port=3606 --copy-back /hotbak/full --defaults-file=/etc/my3606.cnf

chown -R mysql:mysql /data/inst3606/data3606