MySQL表空间传输的应用

原创进击的CJR 2021-12-14

15254

单表的备份恢复

测试用表 test.t

单表备份
xtrabackup -ucjr -pcjr -H1.15.57.253 -P3306 --tables='test.t' --backup --target-dir=/backup/t

恢复备份
xtrabackup --prepare --export --target-dir=/backup/t

在一个测试环境中建表
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) );

卸载新表的表空间
ALTER table t discard tablespace;

将拷贝备份的cfg,idb文件到指定数据目录

cp t.cfg t.ibd /data/3307/data/test

赋权

chown mysql:mysql -R /data/3307/data/test

挂载新表表空间
ALTER TABLE t import tablespace;

查询验证已经恢复的数据

表空间迁移

将表空间迁移到其他实例步骤如下：

1.在目标实例上创建一个相同的表
2.在目标库上执行ALTER TABLE t DISCARD TABLESPACE;
3.在源库上执行FLUSH TABLES t FOR EXPORT;生成.cfg文件
4.将.ibd文件和.cfg文件拷贝到目标实例
5.在源库执行unlock tables;
6.在目标库执行ALTER TABLE t IMPORT TABLESPACE;

测试迁移过程：将实例A上的表迁移到实例B上去

在source server A上，创建一个表
server A> use test;

server A> CREATE TABLE t(c1 INT) engine=InnoDB;
在destination server B上，也创建同样的表结构
server B> use test;

server B> CREATE TABLE t(c1 INT) engine=InnoDB;
在destination server B上， discard 该表（这一步是必须的）
server B> ALTER TABLE t DISCARD TABLESPACE;

1. discard的意思就是从数据库detached,会删除ibd文件，保留frm文件。
2. 也就意味着，你可以对frm文件操作，比如： rename table,drop table ，但是不能对ibd文件操作，比如： dml
在source server A上，执行 FLUSH TABLES ... FOR EXPORT , 该表这时候处于quiesce状态，只读，且创建.cfg metadata文件
server A> use test;

server A> FLUSH TABLES t FOR EXPORT;
此时， .cfg文件在InnoDB的data directory中
flush tables .. for export 会加锁，这时候，千万不能退出终端或session，否则加锁无效且.cfg文件自动删除。
拷贝.ibd & .cfg 从source server A 到在destination server B
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test
修改权限
在source server A，执行unlock tables 来释放FLUSH TABLES ... FOR EXPORT 加的locks
server A> use test;

server A> UNLOCK TABLES;
在destination server B上，导入tablespace
server B> use test;

server B> ALTER TABLE t IMPORT TABLESPACE;

innodb可传输表空间注意事项

必须开启 innodb_file_per_table
当这个表处于quiesced状态,甚至不能被select
两边实例的page size 一致
5.7 版本之前，不支持分区表transport
外键相关的表，必须设置 foreign_key_checks=0 才能成功
ALTER TABLE ... IMPORT TABLESPACE 不需要.cfg metadata file . 但是，这样的话， MySQL就不会对schema进行verificate
5.6以及更高版本， import&export 版本必须在同一个series
在replication环境中， master & slave 都必须开启 innodb_file_per_table
对于InnoDB general tablespace，不支持discard & import tablespace
如果两边服务器的table row_format设置的不一样，会导致schema mismatch error
加密过的InnoDB tablespace 必须要拷贝.cfg 文件复制