xtrabackup備份恢復過程

備份

    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)