xtrabackup全备方案，备份恢复全过程记录

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;
+-------+-------------------------------------------------------------------------------------+
| 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> 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;
+-------+------------------------------------------------------------------------------------+
| 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: 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>

以上是准备环境，以下开始进行测试；

全备：
[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:10M: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
>> 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): '893330211'
>> 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  xtrabackup_checkpoints  xtrabackup_logfile
[root@Slave02 back_up]# cd inno/
[root@Slave02 inno]# ls
mm.ibd  t.ibd
[root@Slave02 inno]# ll
total 208
-rw-r--r-- 1 root root 98304 Mar 27 14:11 mm.ibd
-rw-r--r-- 1 root root 98304 Mar 27 14:11 t.ibd
[root@Slave02 inno]# 
[root@Slave02 inno]# cp /usr/local/mysql/data/inno/*.frm  /data/back_up/inno/   --复制表结构文件进行到备份目录内；
[root@Slave02 inno]# cd /data/back_up/inno/
[root@Slave02 inno]# ls
mm.frm  mm.ibd  t.frm  t.ibd   ----此目录内有完整的IBD数据文件和frm表结构文件；
[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
---以下开始测试全库innodb的恢复过程
[root@Slave02 data]# rm -rf inno/      ----删除inno里的所有文件（ibd,from等）
[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/   --使用备份的数据进行还原到相关目录
[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                                             ---查看到相关的目前权限不对，进行修改
total 240
-rw-r----- 1 root root  8554 Mar 27 14:20 mm.frm
-rw-r--r-- 1 root root 98304 Mar 27 14:20 mm.ibd
-rw-r----- 1 root root  8554 Mar 27 14:20 t.frm
-rw-r--r-- 1 root root 98304 Mar 27 14:20 t.ibd
[root@Slave02 inno]# 
[root@Slave02 inno]# ll
total 240
-rw-r----- 1 root root  8554 Mar 27 14:20 mm.frm
-rw-r--r-- 1 root root 98304 Mar 27 14:20 mm.ibd
-rw-r----- 1 root root  8554 Mar 27 14:20 t.frm
-rw-r--r-- 1 root root 98304 Mar 27 14:20 t.ibd
[root@Slave02 inno]# chown -R mysql:mysql /usr/local/mysql/data/inno/
[root@Slave02 inno]# ll -la
total 256
drwx------ 2 mysql mysql  4096 Mar 27 14:20 .
drwxr-xr-x 8 mysql mysql  4096 Mar 27 14:20 ..
-rw-r----- 1 mysql mysql  8554 Mar 27 14:20 mm.frm
-rw-r--r-- 1 mysql mysql 98304 Mar 27 14:20 mm.ibd
-rw-r----- 1 mysql mysql  8554 Mar 27 14:20 t.frm
-rw-r--r-- 1 mysql mysql 98304 Mar 27 14:20 t.ibd
[root@Slave02 inno]# 
[root@Slave02 inno]# service mysqld restart        -----重启msyql，进行校验下数据；
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 |
+------+
4 rows in set (0.00 sec)

mysql> 
mysql> select * from t;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>