Xtrabackup full backup solution, full Backup Recovery record

mysql> use innoDatabase changedmysql> insert into mm select 1;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into mm select 2;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into mm select 3;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from mm;+------+| a    |+------+|    1 ||    2 ||    3 |+------+3 rows in set (0.00 sec)mysql> show create table mm;+-------+-------------------------------------------------------------------------------------+| Table | Create Table                                                                        |+-------+-------------------------------------------------------------------------------------+| mm    | CREATE TABLE `mm` (  `a` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> exitmysql> use innoDatabase changedmysql> create table t(a int)    -> ;Query OK, 0 rows affected (0.06 sec)mysql> show create table t;+-------+------------------------------------------------------------------------------------+| Table | Create Table                                                                       |+-------+------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `a` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------+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: 0mysql> insert into t select 2;Query OK, 1 row affected (0.01 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into t select 3;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> select * from t;+------+| a    |+------+|    1 ||    2 ||    3 |+------+3 rows in set (0.00 sec)mysql>

The above is the preparation environment. Start the test below;

 

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/dataxtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir =. /xtrabackup: innodb_data_file_path = ibdata1: 10 m: autoextendxtrabackup: innodb_log_group_home_dir =. /xtrabackup: scheduled = 2 xtrabackup: scheduled = 5242880130327 14:11:12 InnoDB: Using Linux native aio130327 14:11:12 InnoDB: Warning: allocated tablespace 2, old maximum was 0> 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]... donextrabackup: 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] # lsibdata1 Inno too many [root @ slave02 back_up] # cd Inno/[root @ slave02 Inno] # lsmm. ibd t. IBD [root @ slave02 Inno] # lltotal 208-rw-r -- 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] # lsmm. 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] # lsbook log.1 mysql-bin.000004 prodibdata1 MySQL mysql-bin.index slave02.errib _ logfile0 mysql-bin.000001 mysqld_multi.log slave02.pidib _ logfile1 mysql-bin.000002 MySQL. PID slow. loginno mysql-bin.000003 performance_schema test --- start testing the restoration process of InnoDB in the full database below [root @ slave02 data] # rm-RF Inno/---- delete all files in Inno (IBD, from, etc) [root @ slave02 data] # lsbook MySQL mysql-bin.index slave02.erribdata1 mysql-bin.000001 mysqld_multi.log slave02.pidib _ logfile0 mysql-bin.000002 MySQL. PID slow. logib_logfile1 mysql-bin.000003 performance_schema testlog.1 mysql-bin.000004 prod [root @ slave02 data] # cp-r/data/back_up/Inno/usr/local/MySQL/data/-- restore related directory [root @ slave02 data] # lsbook log.1 mysql-bin.000004 prodibdata1 MySQL mysql-bin.index slave02.errib _ logfile0 mysql-bin.000001 mysqld_multi.log slave02.pidib _ logfile1 mysql-bin.000002 MySQL. PID slow. loginno 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] # lltotal 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-latotal 256drwx ------ 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-prootwelcome to the MySQL monitor. commands end with; or \ G. your MySQL connection ID is 1 server version: 5.5.13-log source distributioncopyright (c) 2000,201 0, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> Use Inno; database changedmysql> 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>