Xtrabackup full backup solution, full Backup Recovery record

Xtrabackup full backup solution, full Backup Recovery record [html] mysql> use inno Database changed mysql> insert into mm select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into mm select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into mm select 3; query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from mm; + ------ + | a | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec) mysql> show create table mm; + ------- + partition + | Table | Create Table | + ------- + partition + | mm | create table 'mm' ('A' int (11) default null) ENGINE = InnoDB default charset = utf8 | + ------- + rows + 1 row in set (0.00 sec) mysql> exit mysql> use inno Database changed mysql> create table t (a int) ->; Query OK, 0 rows affected (0.06 sec) mysql> show create table t; + ------- + tables + | Table | Create Table | + ------- + tables + | t | create table 'T' ('A' int (11) default null) ENGINE = InnoDB default charset = utf8 | + ------- + rows + 1 row in set (0.01 sec) mysql> insert into t select 1; Query OK, 1 row affected (0.00 sec) records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 2; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t select 3; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t; + ------ + | a | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec) mysql> the above is the preparation environment, run the following test: [html] Full backup: [root @ Slave02 back_up] # xtrabackup_55 -- defaults-file =/etc/my. cnf -- backup -- target-dir =/data/back_up/xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined) 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: 10 M: autoextend xtrabackup: innodb_log_group_home_dir =. /xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 5242880 130327 14:11:12 InnoDB: Using Linux native AIO 130327 14:11:12 InnoDB: Warning: allocated tablespace 2, old maximum was 0 & gt;> log scanned up to (893330211) [01] Copying. /ibdata1 to/data/back_up // ibdata1> log scanned up to (893330211)> log scanned up to (893330211) [01]... done [01] Copying. /inno/mm. ibd to/data/back_up // inno/mm. ibd [01]... done [01] Copying. /inno/t. ibd to/data/back_up // inno/t. ibd [01]... done xtrabackup: The latest check point (for incremental): '000000'> log scanned up to (893330211) xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (893330211) to (893330211) was copied. [root @ Slave02 back_up] # [root @ Slave02 back_up] # ls ibdata1 inno using xtrabackup_logfile [root @ Slave02 back_up] # cd inno/[root @ Slave02 inno] # ls mm. ibd t. ibd [root @ Slave02 inno] # ll total 208-rw-r -- 1 root 98304 Mar 27mm. ibd-rw-r -- 1 root 98304 Mar 27 t. ibd [root @ Slave02 inno] # [root @ Slave02 inno] # cp/usr/local/mysql/data/inno /*. frm/data/back_up/inno/-- copy the table structure file to the backup directory; [root @ Slave02 inno] # cd/data/back_up/inno/[root @ Slave02 inno] # ls mm. frm mm. ibd t. frm t. ibd ---- this directory contains the complete IBD data file and frm table structure file; [root @ Slave02 inno] # [root @ Slave02 inno] # cd .. [root @ Slave02 data] # ls book log.1 mysql-bin.000004 prod ibdata1 mysql mysql-bin.index Slave02.err ib_logfile0 mysql-bin.000001 mysqld_multi.log Slave02.pid ib_logfile1 mysql-bin.000002 mysql. pid slow. log inno mysql-bin.000003 performance_schema test --- the recovery process of innodb in the entire database is tested below [root @ Slave02 data] # rm-rf inno/---- delete all files in inno (ibd, from, etc) [root @ Slave02 data] # ls book mysql mysql-bin.index Slave02.err ibdata1 mysql-bin.000001 mysqld_multi.log Slave02.pid ib_logfile0 mysql-bin.000002 mysql. pid slow. log ib_logfile1 mysql-bin.000003 performance_schema test log.1 mysql-bin.000004 prod [root @ Slave02 data] # cp-r/data/back_up/inno/usr/local/mysql/data/-- Use Backup data restore to related directory [root @ Slave02 data] # ls book log.1 mysql-bin.000004 prod ibdata1 mysql mysql-bin.index Slave02.err ib_logfile0 mysql-bin.000001 mysqld_multi.log Slave02.pid ib_logfile1 mysql-bin.000002 mysql. pid slow. log inno mysql-bin.000003 performance_schema test [root @ Slave02 data] # cd inno/[root @ Slave02 inno] # ll --- view the relevant current permission is incorrect, modify total 240-rw-r ----- 1 root 8554 Mar 27mm. frm-rw-r -- 1 root 98304 Mar 27mm. ibd-rw-r ----- 1 root 8554 Mar 27 t. frm-rw-r -- 1 root 98304 Mar 27 t. ibd [root @ Slave02 inno] # [root @ Slave02 inno] # ll total 240-rw-r ----- 1 root 8554 Mar 27mm. frm-rw-r -- 1 root 98304 Mar 27mm. ibd-rw-r ----- 1 root 8554 Mar 27 t. frm-rw-r -- 1 root 98304 Mar 27 t. ibd [root @ Slave02 inno] # chown-R mysql: mysql/usr/local/mysql/data/inno/[root @ Slave02 inno] # ll-la total 256 drwx ------ 2 mysql 4096 Mar 27. drwxr-xr-x 8 mysql 4096 Mar 27 .. -rw-r ----- 1 mysql 8554 Mar 27mm. frm-rw-r -- 1 mysql 98304 Mar 27mm. ibd-rw-r ----- 1 mysql 8554 Mar 27 t. frm-rw-r -- 1 mysql 98304 Mar 27 t. ibd [root @ Slave02 inno] # [root @ Slave02 inno] # service mysqld restart ----- restart msyql to verify the data; 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,201 0, 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; --- restore normal; + ------ + | a | + ------ + | 1 | 2 | 3 | 4 | + ------ + 4 rows in set (0.00 sec) mysql> select * from t; + ------ + | a | + ------ + | 1 | 2 | 3 | + ------ + 3 rows in set (0.00 sec) mysql>