xtrabackupインクリメンタルバックアップスキーム、バックアップリカバリ全プロセスレコード

11721 ワード

mysql> select * from mm;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)



mysql> show tables;

+----------------+

| Tables_in_inno |

+----------------+

| mm             |

| t              |

+----------------+

2 rows in set (0.01 sec)



mysql> insert into mm select 9999;

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0



mysql> insert into t select 9999;

Query OK, 1 row affected (0.16 sec)

Records: 1  Duplicates: 0  Warnings: 0



mysql>

mysql> select * from t;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)



mysql> select * from mm;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

| 9999 |

+------+

5 rows in set (0.00 sec)



mysql> select * from t;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

| 9999 |

+------+

4 rows in set (0.00 sec)



mysql> 


以上が準備に関する環境であり、以下からバックアップ・リカバリに関する記録を開始する.
 
           :

        ,            

[root@Slave02 inno]# mkdir -p /data/back_up/delta    ---         

[root@Slave02 inno]# cd /data/back_up/

[root@Slave02 back_up]# ls

delta  ibdata1  inno  xtrabackup_checkpoints  xtrabackup_logfile

[root@Slave02 back_up]# cd delta/

[root@Slave02 delta]# ls

[root@Slave02 delta]# 

[root@Slave02 delta]# ls -la

total 16

drwxr-xr-x 2 root root 4096 Mar 27 14:35 .

drwxr-xr-x 4 root root 4096 Mar 27 14:35 ..

[root@Slave02 delta]# 

[root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf  --backup  --tar

get-dir=/data/back_up/delta  --incremental-basedir=/data/back_up     

xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined)

incremental backup from 893330211 is enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /usr/local/mysql/data

xtrabackup: Target instance is assumed as followings.

xtrabackup:   innodb_data_home_dir = ./

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 5242880

130327 14:38:04 InnoDB: Using Linux native AIO

130327 14:38:04  InnoDB: Warning: allocated tablespace 2, old maximum was 0

>> log scanned up to (893330821)

[01] Copying ./ibdata1 

     to /data/back_up/delta/ibdata1.delta

[01]        ...done

[01] Copying ./inno/mm.ibd 

     to /data/back_up/delta/inno/mm.ibd.delta

[01]        ...done

[01] Copying ./inno/t.ibd 

     to /data/back_up/delta/inno/t.ibd.delta

[01]        ...done

>> log scanned up to (893330821)

xtrabackup: The latest check point (for incremental): '893330821'

>> log scanned up to (893330821)

xtrabackup: Stopping log copying thread.

xtrabackup: Transaction log of lsn (893330821) to (893330821) was copied.

[root@Slave02 delta]# 

[root@Slave02 delta]# ls /data/back_up/delta/    -           

ibdata1.delta  ibdata1.meta  inno  xtrabackup_checkpoints  xtrabackup_logfile

[root@Slave02 delta]# 

[root@Slave02 delta]# cd inno/

[root@Slave02 inno]# ls

mm.ibd.delta  mm.ibd.meta  t.ibd.delta  t.ibd.meta

[root@Slave02 inno]# ll

total 88

-rw-r--r-- 1 root root 32768 Mar 27 14:38 mm.ibd.delta

-rw-r--r-- 1 root root    18 Mar 27 14:38 mm.ibd.meta

-rw-r--r-- 1 root root 32768 Mar 27 14:38 t.ibd.delta

-rw-r--r-- 1 root root    18 Mar 27 14:38 t.ibd.meta

[root@Slave02 inno]#

 :        ,       .delta   。                    page,               。                 。             。          /data/back_up/delta2/) 

      ,          prepare  ;

   prepare;

[root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf  --prepare  --target-dir=/data/back_up

xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined)

xtrabackup: cd to /data/back_up

xtrabackup: This target seems to be not prepared yet.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(893330211)

xtrabackup: Temporary instance for recovery is set as followings.

xtrabackup:   innodb_data_home_dir = ./

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 1

xtrabackup:   innodb_log_file_size = 2097152

130327 14:40:53 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

130327 14:40:53 InnoDB: The InnoDB memory heap is disabled

130327 14:40:53 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

130327 14:40:53 InnoDB: Compressed tables use zlib 1.2.3

130327 14:40:53 InnoDB: Using Linux native AIO

130327 14:40:53 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

130327 14:40:53 InnoDB: Initializing buffer pool, size = 100.0M

130327 14:40:53 InnoDB: Completed initialization of buffer pool

130327 14:40:53 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

130327 14:40:53  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Last MySQL binlog file position 0 1747, file name ./mysql-bin.000004

130327 14:40:54  InnoDB: Waiting for the background threads to start

130327 14:40:55 InnoDB: 1.1.5 started; log sequence number 893330211



[notice (again)]

  If you use binary log and don't use any hack of group commit,

  the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 1747, file name ./mysql-bin.000004



xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130327 14:40:55  InnoDB: Starting shutdown...

130327 14:40:56  InnoDB: Shutdown completed; log sequence number 893330449

[root@Slave02 delta]# 

   prepare;

[root@Slave02 delta]# xtrabackup_55 --defaults-file=/etc/my.cnf  --prepare  --target-dir=/data/back_up  --incremental-dir=/data/back_up/delta/

xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined)

incremental backup from 893330211 is enabled.

xtrabackup: cd to /data/back_up

xtrabackup: This target seems to be already prepared.

xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(893330821)

xtrabackup: page size for /data/back_up/delta//ibdata1.delta is 16384 bytes

Applying /data/back_up/delta//ibdata1.delta ...

xtrabackup: page size for /data/back_up/delta//inno/mm.ibd.delta is 16384 bytes

Applying /data/back_up/delta//inno/mm.ibd.delta ...

xtrabackup: page size for /data/back_up/delta//inno/t.ibd.delta is 16384 bytes

Applying /data/back_up/delta//inno/t.ibd.delta ...

xtrabackup: Temporary instance for recovery is set as followings.

xtrabackup:   innodb_data_home_dir = ./

xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend

xtrabackup:   innodb_log_group_home_dir = /data/back_up/delta/

xtrabackup:   innodb_log_files_in_group = 1

xtrabackup:   innodb_log_file_size = 2097152

130327 14:42:49 InnoDB: Using Linux native AIO

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

130327 14:42:49 InnoDB: The InnoDB memory heap is disabled

130327 14:42:49 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

130327 14:42:49 InnoDB: Compressed tables use zlib 1.2.3

130327 14:42:49 InnoDB: Using Linux native AIO

130327 14:42:49 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead

130327 14:42:49 InnoDB: Initializing buffer pool, size = 100.0M

130327 14:42:49 InnoDB: Completed initialization of buffer pool

130327 14:42:49 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

130327 14:42:49  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Last MySQL binlog file position 0 474, file name ./mysql-bin.000005

130327 14:42:49  InnoDB: Waiting for the background threads to start

130327 14:42:50 InnoDB: 1.1.5 started; log sequence number 893330821



[notice (again)]

  If you use binary log and don't use any hack of group commit,

  the binary log position seems to be:

InnoDB: Last MySQL binlog file position 0 474, file name ./mysql-bin.000005



xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130327 14:42:50  InnoDB: Starting shutdown...

130327 14:42:51  InnoDB: Shutdown completed; log sequence number 893330821

[root@Slave02 delta]#

[root@Slave02 data]# rm -rf inno/                ----        inno      

[root@Slave02 data]# ls

book         mysql             mysql-bin.000005    prod

ibdata1      mysql-bin.000001  mysql-bin.index     Slave02.err

ib_logfile0  mysql-bin.000002  mysqld_multi.log    Slave02.pid

ib_logfile1  mysql-bin.000003  mysql.pid           slow.log

log.1        mysql-bin.000004  performance_schema  test

[root@Slave02 data]# cp -r /data/back_up/inno/  /usr/local/mysql/data/       ---                       

[root@Slave02 data]# cd inno/

[root@Slave02 inno]# ll

total 2096

-rw-r----- 1 root root    8554 Mar 27 14:49 mm.frm

-rw-r--r-- 1 root root 1048576 Mar 27 14:49 mm.ibd

-rw-r----- 1 root root    8554 Mar 27 14:49 t.frm

-rw-r--r-- 1 root root 1048576 Mar 27 14:49 t.ibd

[root@Slave02 inno]# chown -R mysql:mysql /usr/local/mysql/data/inno/         ----              

[root@Slave02 inno]# ls  -la

total 2112

drwx------ 2 mysql mysql    4096 Mar 27 14:49 .

drwxr-xr-x 8 mysql mysql    4096 Mar 27 14:49 ..

-rw-r----- 1 mysql mysql    8554 Mar 27 14:49 mm.frm

-rw-r--r-- 1 mysql mysql 1048576 Mar 27 14:49 mm.ibd

-rw-r----- 1 mysql mysql    8554 Mar 27 14:49 t.frm

-rw-r--r-- 1 mysql mysql 1048576 Mar 27 14:49 t.ibd

[root@Slave02 inno]# service mysqld restart                                -----         

Shutting down MySQL...                                     [  OK  ]

Starting MySQL..                                           [  OK  ]

[root@Slave02 inno]# mysql -uroot -proot

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.13-log Source distribution



Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql> use inno

Database changed

mysql> show tables;           ---             

+----------------+

| Tables_in_inno |

+----------------+

| mm             |

| t              |

+----------------+

2 rows in set (0.00 sec)



mysql> select * from mm;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

| 9999 |

+------+

5 rows in set (0.01 sec)

mysql> select * from t;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

| 9999 |

+------+

4 rows in set (0.00 sec)



mysql>   -------     ,      ;





      : 

  1、                ,           ,    。 

  2、     。       ,      ,       。 

  3、           ,         。      。 

  4、          ,            。 

  5、      ,      。

    6、                            ,      ,    ,     12       ,  G     ,