mysql xbk恢复操作指南

Posted 2023-02-01 by wukaiqiang; tagged with none

恢复过程说明：
1）解压数据备份

安装qpress软件
    rpm -ivh qpress-11.1.el7.x86_64.rpm
解压备份
    Cd /backup/Data/backup/xtrabackup/202042
    tar -xvf 192.168.138.2_41667_20201021_full.tar
    tar -xvf 192.168.138.2_41667_20201022_incre.tar
qpress解压数据文件
    cd /backup/Data/backup/xtrabackup/202042/41667/full
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)
    cd /backup/Data/backup/xtrabackup/202042/41667/incre
        (for bf in `find . -iname "*\.qp"` ; do echo $bf && qpress -d $bf $(dirname $bf) && rm -rf $bf ; done)

2）合并备份

perpare全备
    innobackupex --apply-log --redo-only /backup/Data/backup/xtrabackup/202042/41667/full/
合并incre到全备
    innobackupex --apply-log --incremental-dir=/backup/Data/backup/xtrabackup/202042/41667/incre //backup/Data/backup/xtrabackup/202042/41667/full
perpare整个备份
    innobackupex --apply-log /backup/Data/backup/xtrabackup/202042/41667/full

3）恢复数据

停止数据库
    systemctl stop mysqld
清除数据文件目录数据
    cd /Data/mysql/
    rm -rf *
应用备份
    mv /backup/Data/backup/xtrabackup/202042/41667/full/* /Data/mysql
更改目录权限
    chown -R mysql.mysql /Data/mysql
重启数据库
    systemctl start mysqld

4）截取binlog日志

获得position起点
    cat /backup/Data/backup/xtrabackup/202042/41667/incre/xtrabackup_binlog_info 
    mysql-bin.002244    194    230f7b03-e281-11e9-a531-0894ef952262:1-7
登录源数据库服务器获取bin-log日志，拷贝日志至恢复测试服务器/home/pmoopr/目录
获得position终点
    mysqlbinlog mysql-bin.002244
    mysqlbinlog mysql-bin.002245
    mysqlbinlog mysql-bin.002246
    mysqlbinlog mysql-bin.002247
截取日志
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002244 >/backup/Data/backup/xtrabackup/202042/2244.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002245 >/backup/Data/backup/xtrabackup/202042/2245.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002246 >/backup/Data/backup/xtrabackup/202042/2246.sql
    mysqlbinlog --skip-gtids --start-position=194 /home/pmoopr/mysql-bin.002247 >/backup/Data/backup/xtrabackup/202042/2247.sql

5）应用日志

mysql -uroot -p
停止记录binlog日志
    set sql_log_bin=0;
应用日志
    source /backup/Data/backup/xtrabackup/202042/2244.sql
    source /backup/Data/backup/xtrabackup/202042/2245.sql
    source /backup/Data/backup/xtrabackup/202042/2246.sql
    source /backup/Data/backup/xtrabackup/202042/2247.sql
开启记录binlog日志
    set sql_log_bin=1;