innobackupex热备指定库表操作
项目场景:
一个实例中存在几张大表,该表是开发通过程序进行归档的历史表,但是偶尔需要给业务提供查询使用,由于实例都是定期做全量备份的,未避免对该实例空间及备份造成压力,所以建议将这些归档表迁移到一个单独的静态实例中,提供线下查询。
那么任务来了,即备份一个库中超过50G的大表,并且恢复到另外一个实例上。
镇坪ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联建站的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:028-86922220(备注:SSL证书合作)期待与您的合作!
解决方案:
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 TABLEt1
(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 TABLEt2
(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的效率对比后面再进行验证。
文章名称:innobackupex热备指定库表操作
路径分享:http://ybzwz.com/article/jjgdjs.html