Mysql-Xtrabackup备份恢复

恢复准备

1、安装Xtrabackup

2、数据备份和拷贝

 恢复步骤

1、恢复全量备份
2、恢复增量备份到全量备份
注意：开始恢复的增量备份要添加--redo-only参数，最后一次增量备份去掉--redo-only参数
3、对整体的完全备份进行恢复：停库——恢复——启动库

恢复场景

 

全量备份恢复

nohup innobackupex --decompress $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-decompress.log &
nohup innobackupex --apply-log $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-apply-log.log &

systemctl status mysql.service
systemctl stop mysql.service
rm -rf $MYSQL_HOME/data/*

###--move-back不拷贝文件，而是移动文件到目的地,使用场景：没有足够的磁盘空间同时保留数据文件和Backup副本，并且数据文件和backup副本在同一块盘
###--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。
###根据不同场景使用不同参数，此处使用--move-back
nohup innobackupex --defaults-file=$MYSQL_HOME/my.cnf --move-back $RESTORE_PATH/XBK_FULL_XXXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-move-back.log &

chown -R mysql:mysql $MYSQL_HOME/data/*
systemctl start mysql.service

全量+1增量备份恢复

#全量
nohup innobackupex  --decompress --parallel=6  $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-decompress.log &
nohup innobackupex  --apply-log   --redo-only  $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-apply-log.log &

#第一次增量
nohup innobackupex  --decompress --parallel=6 $RESTORE_PATH/XBK_INCR_XXXX1 > $RESTORE_PATH/log/XBK_INCR_XXXX1-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX'  --redo-only --apply-log  $RESTORE_PATH/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX1 >  $RESTORE_PATH/log/XBK_INCR_XXXX1-redo-only.log&

#停止mysql和清理数据
systemctl status mysql3306.service
systemctl stop mysql3306.service
rm -rf /data/app/mysql3306/data/*

#恢复
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --copy-back $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-copy-back.log &

#启动mysql
chown -R mysql:mysql $MYSQL_HOME/data/*
systemctl start mysql.service

 

全量+2增量备份恢复

#全量解压
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --decompress  --parallel=8  $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --apply-log   --redo-only   $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-redo-only.log &

#第一次增量
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --decompress --parallel=8 $RESTORE_PATH/XBK_INCR_XXXX1 > $RESTORE_PATH/log/XBK_INCR_XXXX1-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX' --redo-only --apply-log  $RESTORE_PATH/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX1 > $RESTORE_PATH/log/XBK_INCR_XXXX1-redo-only.log &


#第二次增量
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --decompress --parallel=8 $RESTORE_PATH/XBK_INCR_XXXX2  > $RESTORE_PATH/log/XBK_INCR_XXXX2-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX'   --apply-log  $RESTORE_PATH/log/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX2 > $RESTORE_PATH/log/XBK_INCR_XXXX2-apply.log &

#停止mysql和清理数据文件
systemctl status mysql.service
systemctl stop mysql.service
rm -rf $MYSQL_HOME/data/*

#恢复
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --copy-back $RESTORE_PATH/XBK_FULL_XXXX  > $RESTORE_PATH/log/XBK_FULL_XXXX-copy-back.log &

#启动mysql
chown -R mysql:mysql $MYSQL_HOME/data/*
systemctl start mysql.service

 

全量+3增量备份恢复

　

#全量解压
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --decompress  --parallel=8  $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --apply-log   --redo-only   $RESTORE_PATH/XBK_FULL_XXXX > $RESTORE_PATH/log/XBK_FULL_XXXX-redo-only.log &

#第一次增量
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --decompress --parallel=8 $RESTORE_PATH/XBK_INCR_XXXX1 > $RESTORE_PATH/log/XBK_INCR_XXXX1-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX' --redo-only --apply-log  $RESTORE_PATH/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX1 > $RESTORE_PATH/log/XBK_INCR_XXXX1-redo-only.log &

#第二次增量
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --decompress --parallel=8 $RESTORE_PATH/XBK_INCR_XXXX2 > $RESTORE_PATH/log/XBK_INCR_XXXX2-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX' --redo-only --apply-log  $RESTORE_PATH/log/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX2 > $RESTORE_PATH/log/XBK_INCR_XXXX2-redo-only.log &

#第三次增量
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --decompress --parallel=8 $RESTORE_PATH/XBK_INCR_XXXX3  > $RESTORE_PATH/log/XBK_INCR_XXXX3-decompress.log &
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf --user=username --password='XXXX'   --apply-log  $RESTORE_PATH/log/XBK_FULL_XXXX --incremental-dir=$RESTORE_PATH/XBK_INCR_XXXX3 > $RESTORE_PATH/log/XBK_INCR_XXXX3-apply.log &

#停止mysql和清理数据文件
systemctl status mysql.service
systemctl stop mysql.service
rm -rf $MYSQL_HOME/data/*

#恢复
nohup innobackupex  --defaults-file=$MYSQL_HOME/my.cnf  --copy-back $RESTORE_PATH/XBK_FULL_XXXX  > $RESTORE_PATH/log/XBK_FULL_XXXX-copy-back.log &

#启动mysql
chown -R mysql:mysql $MYSQL_HOME/data/*
systemctl start mysql.service