xtrabackupインクリメンタルバックアップとリカバリテスト

53572 ワード

テストバージョン:xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux(x 86_64)(revision id:)
xtrabackupインクリメンタルバックアップとリカバリテスト
一、増分バックアップの作成
1、全備を作成する


   
   
   
   
    
    
    
    innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 /data/backup/physical/full/

2、新しいデータベースとテーブルの作成


    
    
    
    
     
     
     
     root@[test] 11:49:19>create database test2 default charset utf8; 
     
     
     
     Query OK, 1 row affected (0.00 sec)
     
     
     
     

     
     
     
     root@[test] 11:49:24>use test
     
     
     
     Database changed
     
     
     
     root@[test] 11:49:36>create table t6 (id int,name varchar(4));
     
     
     
     Query OK, 0 rows affected (0.01 sec)







3、 1




    
    
    
    
     
     
     
     innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/full/2014-08-10_11-47-14/
    
    
    
    







4、




    
    
    
    
     
     
     
     root@[test] 11:49:54>alter table t5 add column (b2 int);
     
     
     
     Query OK, 1 row affected (0.01 sec)
     
     
     
     Records: 1  Duplicates: 0  Warnings: 0
     
     
     
     

     
     
     
     root@[test] 11:53:08>alter table t6 drop column name;
     
     
     
     Query OK, 0 rows affected (0.00 sec)
     
     
     
     Records: 0  Duplicates: 0  Warnings: 0
    
    
    
    
 
 
 





5、 2




    
    
    
    
     
     
     
     innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/inc/2014-08-10_11-51-50/
    
    
    
    







6、




    
    
    
    
     
     
     
     root@[test] 11:55:44>insert into t6 values (1),(2);
     
     
     
     Query OK, 2 rows affected (0.00 sec)
     
     
     
     Records: 2  Duplicates: 0  Warnings: 0
     
     
     
     

     
     
     
     root@[test] 11:55:50>select * from t5;
     
     
     
     +------+------+------+
     
     
     
     | id   | b1   | b2   |
     
     
     
     +------+------+------+
     
     
     
     |    1 |    1 | NULL |
     
     
     
     +------+------+------+
     
     
     
     1 row in set (0.00 sec)
     
     
     
     

     
     
     
     root@[test] 11:56:03>insert into t5 values (2,2,2),(3,3,3);
     
     
     
     Query OK, 2 rows affected (0.00 sec)
     
     
     
     Records: 2  Duplicates: 0  Warnings: 0
     
     
     
     

     
     
     
     root@[test] 11:56:17>select * from t5;                     
     
     
     
     +------+------+------+
     
     
     
     | id   | b1   | b2   |
     
     
     
     +------+------+------+
     
     
     
     |    1 |    1 | NULL |
     
     
     
     |    2 |    2 |    2 |
     
     
     
     |    3 |    3 |    3 |
     
     
     
     +------+------+------+
     
     
     
     3 rows in set (0.00 sec)
    
    
    
    
 
 
 





7、 3




    
    
    
    
     
     
     
     innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/inc/2014-08-10_11-54-06/
    
    
    
    







 、 


1、 test2， t6， t5 ， ， 3




    
    
    
    
     
     
     
     root@[test] 12:02:11>drop database test2; 
     
     
     
     root@[test] 12:02:33>drop table t6;
     
     
     
     Query OK, 0 rows affected (0.00 sec)
     
     
     
     

     
     
     
     root@[test] 12:02:41>show tables;  
     
     
     
     +----------------+
     
     
     
     | Tables_in_test |
     
     
     
     +----------------+
     
     
     
     | sbtest1        |
     
     
     
     | t1             |
     
     
     
     | t2             |
     
     
     
     | t3             |
     
     
     
     | t4             |
     
     
     
     | t5             |
     
     
     
     +----------------+
     
     
     
     6 rows in set (0.00 sec)
     
     
     
     

     
     
     
     root@[test] 12:02:45>delete from t5 where id=2 or id =3;
     
     
     
     Query OK, 2 rows affected (0.00 sec)
     
     
     
     

     
     
     
     root@[test] 12:03:02>select * from t5;
     
     
     
     +------+------+------+
     
     
     
     | id   | b1   | b2   |
     
     
     
     +------+------+------+
     
     
     
     |    1 |    1 | NULL |
     
     
     
     +------+------+------+
     
     
     
     1 row in set (0.00 sec)
    
    
    
    
 
 




 

2、


：




    
    
    
    
     
     
     
     innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/
    
    
    
    







1， ：




    
    
    
    
     
     
     
     innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-51-50/
    
    
    
    







2， ：




    
    
    
    
     
     
     
     innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-54-06/
    
    
    
    







3， ， --redo-only：




    
    
    
    
     
     
     
     innobackupex --apply-log /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-56-44/
    
    
    
    







：




    
    
    
    
     
     
     
     innobackupex --apply-log /data/backup/physical/full/2014-08-10_11-47-14/
    
    
    
    







xtrabackup mysql ， ：
xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: ) mysql 5.5.38 


2014-08-10 12:10:37 7f00603a8700 InnoDB: Error: page 7561 log sequence number 2875887409


InnoDB: is in the future! Current system log sequence number 2395543093.


InnoDB: Your database may be corrupt or you may have copied the InnoDB


InnoDB: tablespace but not the InnoDB log files. See


InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html


InnoDB: for more information.






3、 ( )， ，




    
    
    
    
     
     
     
     rm -rf /data/mysql55/*
     
     
     
     innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --copy-back /data/backup/physical/full/2014-08-10_11-47-14/
     
     
     
     

     
     
     
     cd /data/mysql55/
     
     
     
     chown mysql.mysql -R *
     
     
     
     chmod 770 -R *
    
    
    
    
 
 
 





4、




    
    
    
    
     
     
     
     root@[(none)] 12:16:14>show databases;
     
     
     
     +--------------------+
     
     
     
     | Database           |
     
     
     
     +--------------------+
     
     
     
     | information_schema |
     
     
     
     | employees          |
     
     
     
     | mysql              |
     
     
     
     | percona            |
     
     
     
     | performance_schema |
     
     
     
     | sakila             |
     
     
     
     | test               |
     
     
     
     | test2              |
     
     
     
     +--------------------+
     
     
     
     8 rows in set (0.00 sec)
     
     
     
     

     
     
     
     root@[(none)] 12:16:18>use test 
     
     
     
     Database changed
     
     
     
     root@[test] 12:16:50>select * from t5;
     
     
     
     +------+------+------+
     
     
     
     | id   | b1   | b2   |
     
     
     
     +------+------+------+
     
     
     
     |    1 |    1 | NULL |
     
     
     
     |    2 |    2 |    2 |
     
     
     
     |    3 |    3 |    3 |
     
     
     
     +------+------+------+
     
     
     
     3 rows in set (0.01 sec)
     
     
     
     

     
     
     
     root@[test] 12:16:59>select * from t6;
     
     
     
     +------+
     
     
     
     | id   |
     
     
     
     +------+
     
     
     
     |    1 |
     
     
     
     |    2 |
     
     
     
     +------+
     
     
     
     2 rows in set (0.02 sec)
    
    
    
    
 
 




 

2 ， --redo-only， 3 ， ： ， 3





     
     
     
     
      
      
      
      innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_14-25-17/
      
      
      
      innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-30-19/
      
      
      
      # 2 --redo-only ， 
      
      
      
      innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-31-21/
      
      
      
      innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/
      
      
      
      

      
      
      
      # 3 ， ， lsn
      
      
      
      innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-32-19/
     
     
     
     
 
 
 

：


InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy


and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.






This software is published under


the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.






Get the latest version of Percona XtraBackup, documentation, and help resources:


http://www.percona.com/xb/p






IMPORTANT: Please check that the apply-log run completes successfully.


           At the end of a successful apply-log run innobackupex


           prints "completed OK!".














140810 14:46:21  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/backup/physical/full/2014-08-10_14-25-17/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/physical/full/2014-08-10_14-25-17 --incremental-dir=/data/backup/physical/inc/2014-08-10_14-32-19/ --tmpdir=/tmp






xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )


incremental backup from 2875985858 is enabled.


xtrabackup: cd to /data/backup/physical/full/2014-08-10_14-25-17


xtrabackup: This target seems to be already prepared.


xtrabackup: error: This incremental backup seems not to be proper for the target.


xtrabackup:  Check 'to_lsn' of the target and 'from_lsn' of the incremental.


innobackupex: Error: 


innobackupex: ibbackup failed at /usr/bin/innobackupex line 2610.

 













 ：


1、 ， master ， slave ， master ， ，slave master ， slave ；


2、 xtrabackup mysql ( xtrabackup)， ， --ibbackup=IBBACKUP-BINARY xtrabackup ；


3、 ， ， ， ， ；


4、 --redo-only ， ；


5、 ， 。






：


1、 http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/incremental_backups_innobackupex.html














(Wiz)

[スタンダード/python]DFS/BFS-6603号宝くじ

nginx+dockerを使用して無停止配置を設定する