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>