如何从一个XtraBackup完整备份中恢复一个InnoDB表
如何从一个XtraBackup完整备份中恢复一个InnoDB表
有时候我们从一个完整的数据库备份中不需要整库恢复,只需要恢复其中的一个或者几个表,如果使用Percona的XtraBackup备份在恢复的时候如何只恢复部分数据呢?
下面用例子来演示
首先为了能恢复表需要做以下操作:
1、InnoDB_FAST_SHUTDOWN = 0 --此参数MySQL在关闭的时候,需要完成所有的full purge和merge insert buffer操作.
2、InnoDB_File_Per_Table = ON --此参数修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间.
开始使用XtraBackup备份,这里用到--export参数.
[mysql@localhost mysql]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --export /mysql/backup/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140312 16:06:45 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:06:45 innobackupex-1.5.1: Connected to MySQL server
140312 16:06:45 innobackupex-1.5.1: Executing a version check against the server...
140312 16:06:51 innobackupex-1.5.1: Done.
IMPORTANT: Please check that the backup run completes successfully.
......
......
......
备份完成之后应用日志使备份保持一致性
[mysql@localhost backup]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --apply-log --export /mysql/backup/2014-03-12_16-06-52/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140312 16:11:47 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:11:47 innobackupex-1.5.1: Connected to MySQL server
Connected successfully
140312 16:11:47 innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex-1.5.1
prints "completed OK!".
......
......
......
现在我们登录MySQL删除一些t表的数据
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+----+
11 rows in set (0.06 sec)
mysql> delete from t where id between 7 and 10;
Query OK, 4 rows affected (0.18 sec)
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 11 |
+----+
7 rows in set (0.00 sec)
这时我们Discard表
mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.12 sec)
从备份目录复制文件到数据库目录
[mysql@localhost test]$ cp -p -r t.cfg t.ibd ../../../data/test/
我们进入到MySQL之后Import表
mysql> ALTER TABLE t import TABLESPACE;
Query OK, 0 rows affected (0.07 sec)
查看表t
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+----+
11 rows in set (0.00 sec)
注意:
在复制备份文件的时候一定要复制后缀cfg文件,否则在Import的时候就会报Warning.例如如下的信息:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t.cfg', will attempt to import without schema verification |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
cfg文件的用处主要是在MySQL5.6执行"Flush Table xxx Export;"之后使.ibd文件保持一致性,同时这个文件会生成一个.cfg文件,在做Import的时候会对导入过程进行校验,但是在MySQL5.6.8版本之后也不是必须要有.cfg文件.如果真没有,在导入的时候有可能就会报出上面的错误,所以为了安全还是复制它.
如果表有外键在Discard的时候执行如下命令:
set FOREIGN_KEY_CHECKS=0;
在Import表之后执行以下命令恢复外键检查
set FOREIGN_KEY_CHECKS=1;
参数--export的英文解释如下:
This option is passed directly to xtrabackup's --export option. It
enables exporting individual tables for import into another server.
下面用例子来演示
首先为了能恢复表需要做以下操作:
1、InnoDB_FAST_SHUTDOWN = 0 --此参数MySQL在关闭的时候,需要完成所有的full purge和merge insert buffer操作.
2、InnoDB_File_Per_Table = ON --此参数修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间.
开始使用XtraBackup备份,这里用到--export参数.
[mysql@localhost mysql]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --export /mysql/backup/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140312 16:06:45 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:06:45 innobackupex-1.5.1: Connected to MySQL server
140312 16:06:45 innobackupex-1.5.1: Executing a version check against the server...
140312 16:06:51 innobackupex-1.5.1: Done.
IMPORTANT: Please check that the backup run completes successfully.
......
......
......
备份完成之后应用日志使备份保持一致性
[mysql@localhost backup]$ innobackupex-1.5.1 --defaults-file=/etc/my.cnf --apply-log --export /mysql/backup/2014-03-12_16-06-52/
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
140312 16:11:47 innobackupex-1.5.1: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
140312 16:11:47 innobackupex-1.5.1: Connected to MySQL server
Connected successfully
140312 16:11:47 innobackupex-1.5.1: Connection to database server closed
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex-1.5.1
prints "completed OK!".
......
......
......
现在我们登录MySQL删除一些t表的数据
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+----+
11 rows in set (0.06 sec)
mysql> delete from t where id between 7 and 10;
Query OK, 4 rows affected (0.18 sec)
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 11 |
+----+
7 rows in set (0.00 sec)
这时我们Discard表
mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.12 sec)
从备份目录复制文件到数据库目录
[mysql@localhost test]$ cp -p -r t.cfg t.ibd ../../../data/test/
我们进入到MySQL之后Import表
mysql> ALTER TABLE t import TABLESPACE;
Query OK, 0 rows affected (0.07 sec)
查看表t
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+----+
11 rows in set (0.00 sec)
注意:
在复制备份文件的时候一定要复制后缀cfg文件,否则在Import的时候就会报Warning.例如如下的信息:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t.cfg', will attempt to import without schema verification |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
cfg文件的用处主要是在MySQL5.6执行"Flush Table xxx Export;"之后使.ibd文件保持一致性,同时这个文件会生成一个.cfg文件,在做Import的时候会对导入过程进行校验,但是在MySQL5.6.8版本之后也不是必须要有.cfg文件.如果真没有,在导入的时候有可能就会报出上面的错误,所以为了安全还是复制它.
如果表有外键在Discard的时候执行如下命令:
set FOREIGN_KEY_CHECKS=0;
在Import表之后执行以下命令恢复外键检查
set FOREIGN_KEY_CHECKS=1;
参数--export的英文解释如下:
This option is passed directly to xtrabackup's --export option. It
enables exporting individual tables for import into another server.
OK了.我们的数据又回来了,今天先到此吧.^_^
原博客地址:http://blog.chinaunix.net/uid-10661836-id-4144677.html
转载于:https://blog.51cto.com/sparehot/1376587