欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

xtrabackup备份恢复过程

程序员文章站 2022-09-22 10:43:29
备份 破坏数据 drop table oldboy.test; 恢复 启动数据库 binlog恢复 python 查看最后一次增备的binlog文件及位置 cat /backup/inc2/xtrabackup_binlog_info mysql bin.000001 554 其它数据需要binlo ......

备份

    1.全备
    innobackupex --user=root --password=123456 --no-timestamp /backup/full
    增加数据
        mysql> insert into oldboy.test values(2,'b');
        query ok, 1 row affected (0.00 sec)

        mysql> select * from oldboy.test;
        +------+------+
        | id  | name |
        +------+------+
        |    1 | a    |
        |    2 | b    |
        +------+------+
    2.第一次增备
        innobackupex --user=root --password=123456 --incremental --no-timestamp --incremental-basedir=/backup/full/  /backup/inc1
    增加数据
        mysql> insert into oldboy.test values(3,'c');
        query ok, 1 row affected (0.01 sec)

        mysql> select * from oldboy.test;
        +------+------+
        | id  | name |
        +------+------+
        |    1 | a    |
        |    2 | b    |
        |    3 | c    |
        +------+------+
   3. 第二次增备
        innobackupex --user=root --password=123456 --incremental --no-timestamp --incremental-basedir=/backup/inc1/  /backup/inc2
    增加数据
        mysql> insert into oldboy.test values(4,'d');
        query ok, 1 row affected (0.00 sec)

        mysql> select * from oldboy.test;
        +------+------+
        | id  | name |
        +------+------+
        |    1 | a    |
        |    2 | b    |
        |    3 | c    |
        |    4 | d    |
        +------+------+

破坏数据

drop table oldboy.test;

恢复

   ##应用全备
        innobackupex --apply-log --redo-only /backup/full/
    #第一次增备合并到全备
        innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/inc1/
    #第二次增备合并到全备
        innobackupex --apply-log --redo-only /backup/full/ --incremental-dir=/backup/inc2/
   # 最后应用全备
        innobackupex --apply-log /backup/full/
    #恢复
        innobackupex --copy-back /backup/full/

启动数据库

    /etc/init.d/mysqld start

binlog恢复

  #  查看最后一次增备的binlog文件及位置
    cat /backup/inc2/xtrabackup_binlog_info 
    mysql-bin.000001    554



    #其它数据需要binlog恢复,binlog日志片段

    # at 554
    #171123  5:05:47 server id 1  end_log_pos 631 crc32 0x682f8893    query    thread_id=7    exec_time=0    error_code=0
    set timestamp=1511413547/*!*/;
    begin
    /*!*/;
    # at 631
    #171123  5:05:47 server id 1  end_log_pos 740 crc32 0xc13589a3    query    thread_id=7    exec_time=0    error_code=0
    set timestamp=1511413547/*!*/;
    insert into oldboy.test values(4,'d')
    /*!*/;
    # at 740
    #171123  5:05:47 server id 1  end_log_pos 771 crc32 0x9fe9947a    xid = 62
    commit/*!*/;
    # at 771
    #171123  5:07:01 server id 1  end_log_pos 895 crc32 0xc7671d4f    query    thread_id=7    exec_time=0    error_code=0
    set timestamp=1511413621/*!*/;
    drop table `oldboy`.`test` /* generated by server */
    /*!*/;
    # at 895
    #171123  5:16:08 server id 1  end_log_pos 918 crc32 0x7fc7c108    stop
    delimiter ;
    # end of log file

   # 导出binlog
    mysqlbinlog mysql-bin.000001 --start-position=554 --stop-position=771 > /backup/binlog.sql

  # 导入binlog
    set sql_log_bin=0;
    source /backup/binlog.sql

    #最终数据
    mysql> select * from oldboy.test;
    +------+------+
    | id  | name |
    +------+------+
    |    1 | a    |
    |    2 | b    |
    |    3 | c    |
    |    4 | d    |
    +------+------+
    4 rows in set (0.00 sec)