mysql 热备指定库 指定表_innobackupex 热备指定库表操作

项目场景：

一个实例中存在几张大表，该表是开发通过程序进行归档的历史表，但是偶尔需要给业务提供查询使用，由于实例都是定期做全量备份的，未避免对该实例空间及备份造成压力，所以建议将这些归档表迁移到一个单独的静态实例中，提供线下查询。

那么任务来了，即备份一个库中超过50G的大表，并且恢复到另外一个实例上。

解决方案：

1.首先想到的肯定是mysqldump。

优点：可以进行指定表的备份，备份后，直接到指定恢复的实例上执行source命令导入数据，操作简便。

缺点：由于本次操作的表很大，而mysqldump是单线程操作，所以导致备份以及恢复的时间尤为漫长，对于效率为主的操作，不建议采取这种办法。

2.利用热备工具innobackupex方式。

优点：可以进行指定表的备份和恢复，操作时间快，效率高。

优点：备份和恢复操作命令比较复杂。

一切工作皆讲效率，所以下面讲解方案2的具体操作方法。

首先备份指定表的命令为：

/usr/local/percona-xtrabackup-2.4.5-Linux-x8664/bin/innobackupex --defaults-file=/data/mysql/3306/conf/my.cnf --host=127.0.0.1 --port=3306 --tmpdir=/tmp/ --user=root --password='root@123' --databases='test.t1 test.t2' --slave-info --no-timestamp /home/backup/

备份完成后，发现在备份路径下备份出指定的表。

然后进行恢复，指定表的备份，恢复操作要加上指定命令--export

如下：

/usr/local/percona-xtrabackup-2.4.5-Linux-x86_64/bin/innobackupex --apply-log --export /home/backup/

发现新增.cfg和.exp文件。

在新实例上恢复备份的表。

a.从备份的实例上查看建表语句，在新实例上创建相同的表。

mysql> show create table t1\G

1. row

Table: t1

Create Table: CREATE TABLE t1 (

id int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> show create table t2\G

1. row

Table: t2

Create Table: CREATE TABLE t2 (

id int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

b.将新建的表做discard tablespace 操作

mysql> ALTER TABLE test.t1 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE test.t2 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.04 sec)

发现表结构如下变化

c.将备份后，恢复得到的.ibd,.exp文件拷贝到新实例下，并修改属主属组权限。

d.在新实例上将discard的表，进行import操作。

mysql> ALTER TABLE test.t1 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE test.t2 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.04 sec)

e.前后对比，检验是否操作成功。

备份的实例

新实例

OK，至此整个过程完成。

总结，采取热备和恢复的方法，虽然操作复杂，但是相对mysqldump操作时间短效率高，整个过程耗时在copy文件和alter table操作上。还有其他提高备份和恢复效率的工具，如mysqldumper和mysqlpump(5.7版本)，mysqlpump对单表仍是单线程操作，mysqldumper的和innobackupex的效率对比后面再进行验证。