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

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

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

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

    
    
    
    
    
     
     
     
     
  1. root@[test] 11:49:19>create database test2 default charset utf8; 
    2. 
     
     
     
     
  2. Query OK, 1 row affected (0.00 sec)
    3. 
     
     
     
     
    4. 
     
     
     
     
  4. root@[test] 11:49:24>use test
    5. 
     
     
     
     
  5. Database changed
    6. 
     
     
     
     
  6. root@[test] 11:49:36>create table t6 (id int,name varchar(4));
    7. 
     
     
     
     
  7. Query OK, 0 rows affected (0.01 sec)
    8.

3、 1 

    
    
    
    
    
     
     
     
     
  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/
    2.

4、 

    
    
    
    
    
     
     
     
     
  1. root@[test] 11:49:54>alter table t5 add column (b2 int);
    2. 
     
     
     
     
  2. Query OK, 1 row affected (0.01 sec)
    3. 
     
     
     
     
  3. Records: 1  Duplicates: 0  Warnings: 0
    4. 
     
     
     
     
    5. 
     
     
     
     
  5. root@[test] 11:53:08>alter table t6 drop column name;
    6. 
     
     
     
     
  6. Query OK, 0 rows affected (0.00 sec)
    7. 
     
     
     
     
  7. Records: 0  Duplicates: 0  Warnings: 0
    8.

5、 2 

    
    
    
    
    
     
     
     
     
  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/inc/2014-08-10_11-51-50/
    2.

6、 

    
    
    
    
    
     
     
     
     
  1. root@[test] 11:55:44>insert into t6 values (1),(2);
    2. 
     
     
     
     
  2. Query OK, 2 rows affected (0.00 sec)
    3. 
     
     
     
     
  3. Records: 2  Duplicates: 0  Warnings: 0
    4. 
     
     
     
     
    5. 
     
     
     
     
  5. root@[test] 11:55:50>select * from t5;
    6. 
     
     
     
     
  6. +------+------+------+
    7. 
     
     
     
     
  7. | id   | b1   | b2   |
    8. 
     
     
     
     
  8. +------+------+------+
    9. 
     
     
     
     
  9. |    1 |    1 | NULL |
    10. 
     
     
     
     
  10. +------+------+------+
    11. 
     
     
     
     
  11. 1 row in set (0.00 sec)
    12. 
     
     
     
     
    13. 
     
     
     
     
  13. root@[test] 11:56:03>insert into t5 values (2,2,2),(3,3,3);
    14. 
     
     
     
     
  14. Query OK, 2 rows affected (0.00 sec)
    15. 
     
     
     
     
  15. Records: 2  Duplicates: 0  Warnings: 0
    16. 
     
     
     
     
    17. 
     
     
     
     
  17. root@[test] 11:56:17>select * from t5;                     
    18. 
     
     
     
     
  18. +------+------+------+
    19. 
     
     
     
     
  19. | id   | b1   | b2   |
    20. 
     
     
     
     
  20. +------+------+------+
    21. 
     
     
     
     
  21. |    1 |    1 | NULL |
    22. 
     
     
     
     
  22. |    2 |    2 |    2 |
    23. 
     
     
     
     
  23. |    3 |    3 |    3 |
    24. 
     
     
     
     
  24. +------+------+------+
    25. 
     
     
     
     
  25. 3 rows in set (0.00 sec)
    26.

7、 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/inc/2014-08-10_11-54-06/
    2.

、
1、 test2， t6， t5 ， ， 3 

    
    
    
    
    
     
     
     
     
  1. root@[test] 12:02:11>drop database test2; 
    2. 
     
     
     
     
  2. root@[test] 12:02:33>drop table t6;
    3. 
     
     
     
     
  3. Query OK, 0 rows affected (0.00 sec)
    4. 
     
     
     
     
    5. 
     
     
     
     
  5. root@[test] 12:02:41>show tables;  
    6. 
     
     
     
     
  6. +----------------+
    7. 
     
     
     
     
  7. | Tables_in_test |
    8. 
     
     
     
     
  8. +----------------+
    9. 
     
     
     
     
  9. | sbtest1        |
    10. 
     
     
     
     
  10. | t1             |
    11. 
     
     
     
     
  11. | t2             |
    12. 
     
     
     
     
  12. | t3             |
    13. 
     
     
     
     
  13. | t4             |
    14. 
     
     
     
     
  14. | t5             |
    15. 
     
     
     
     
  15. +----------------+
    16. 
     
     
     
     
  16. 6 rows in set (0.00 sec)
    17. 
     
     
     
     
    18. 
     
     
     
     
  18. root@[test] 12:02:45>delete from t5 where id=2 or id =3;
    19. 
     
     
     
     
  19. Query OK, 2 rows affected (0.00 sec)
    20. 
     
     
     
     
    21. 
     
     
     
     
  21. root@[test] 12:03:02>select * from t5;
    22. 
     
     
     
     
  22. +------+------+------+
    23. 
     
     
     
     
  23. | id   | b1   | b2   |
    24. 
     
     
     
     
  24. +------+------+------+
    25. 
     
     
     
     
  25. |    1 |    1 | NULL |
    26. 
     
     
     
     
  26. +------+------+------+
    27. 
     
     
     
     
  27. 1 row in set (0.00 sec)
    28.

2、
： 

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

1， ： 

    
    
    
    
    
     
     
     
     
  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.

2， ： 

    
    
    
    
    
     
     
     
     
  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-54-06/
    2.

3， ， --redo-only： 

    
    
    
    
    
     
     
     
     
  1. 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/
    2.

： 

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

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、 ( )， ， 

    
    
    
    
    
     
     
     
     
  1. rm -rf /data/mysql55/*
    2. 
     
     
     
     
  2. 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/
    3. 
     
     
     
     
    4. 
     
     
     
     
  4. cd /data/mysql55/
    5. 
     
     
     
     
  5. chown mysql.mysql -R *
    6. 
     
     
     
     
  6. chmod 770 -R *
    7.

4、 

    
    
    
    
    
     
     
     
     
  1. root@[(none)] 12:16:14>show databases;
    2. 
     
     
     
     
  2. +--------------------+
    3. 
     
     
     
     
  3. | Database           |
    4. 
     
     
     
     
  4. +--------------------+
    5. 
     
     
     
     
  5. | information_schema |
    6. 
     
     
     
     
  6. | employees          |
    7. 
     
     
     
     
  7. | mysql              |
    8. 
     
     
     
     
  8. | percona            |
    9. 
     
     
     
     
  9. | performance_schema |
    10. 
     
     
     
     
  10. | sakila             |
    11. 
     
     
     
     
  11. | test               |
    12. 
     
     
     
     
  12. | test2              |
    13. 
     
     
     
     
  13. +--------------------+
    14. 
     
     
     
     
  14. 8 rows in set (0.00 sec)
    15. 
     
     
     
     
    16. 
     
     
     
     
  16. root@[(none)] 12:16:18>use test 
    17. 
     
     
     
     
  17. Database changed
    18. 
     
     
     
     
  18. root@[test] 12:16:50>select * from t5;
    19. 
     
     
     
     
  19. +------+------+------+
    20. 
     
     
     
     
  20. | id   | b1   | b2   |
    21. 
     
     
     
     
  21. +------+------+------+
    22. 
     
     
     
     
  22. |    1 |    1 | NULL |
    23. 
     
     
     
     
  23. |    2 |    2 |    2 |
    24. 
     
     
     
     
  24. |    3 |    3 |    3 |
    25. 
     
     
     
     
  25. +------+------+------+
    26. 
     
     
     
     
  26. 3 rows in set (0.01 sec)
    27. 
     
     
     
     
    28. 
     
     
     
     
  28. root@[test] 12:16:59>select * from t6;
    29. 
     
     
     
     
  29. +------+
    30. 
     
     
     
     
  30. | id   |
    31. 
     
     
     
     
  31. +------+
    32. 
     
     
     
     
  32. |    1 |
    33. 
     
     
     
     
  33. |    2 |
    34. 
     
     
     
     
  34. +------+
    35. 
     
     
     
     
  35. 2 rows in set (0.02 sec)
    36.

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

     
     
     
     
    
      
      
      
      
  1. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_14-25-17/
    2. 
      
      
      
      
  2. 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/
    3. 
      
      
      
      
  3. # 2 --redo-only ， 
    4. 
      
      
      
      
  4. 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/
    5. 
      
      
      
      
  5. innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/
    6. 
      
      
      
      
    7. 
      
      
      
      
  7. # 3 ， ， lsn
    8. 
      
      
      
      
  8. 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/
    9.
：
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を使用して無停止配置を設定する