FLUSH TABLES FOR EXPORT

环境:

OS:Centos 7

DB:5.7

1.主库创建表,并尝试写入数据
create table flush_test
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
name varchar(32),
   PRIMARY KEY (`id`)
);

insert into flush_test (name) values('name1');
insert into flush_test (name) values('name2');
insert into flush_test (name) values('name3');
insert into flush_test (name) values('name4');
insert into flush_test (name) values('name5');
insert into flush_test (name) values('name6');
insert into flush_test (name) values('name7');
insert into flush_test (name) values('name8');
insert into flush_test (name) values('name9');
insert into flush_test (name) values('name10');

2.flush表
FLUSH TABLES flush_test FOR EXPORT;

mysql> FLUSH TABLES flush_test FOR EXPORT;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
只能执行一次

尝试写入数据:
insert into flush_test (name) values('name11');

mysql> insert into flush_test (name) values('name11');
ERROR 1099 (HY000): Table 'flush_test' was locked with a READ lock and can't be updated
这个时候是不能写入数据的

3.查看已经生成了cfg文件
[root@localhost db_hxl]# ls -al
total 352
drwx------. 2 mysql mysql    162 Nov 9 09:02 .
drwxr-xr-x. 8 mysql mysql   4096 Jul 10 15:24 ..
-rw-rw----. 1 mysql mysql     61 Jul 10 14:30 db.opt
-rw-r-----. 1 mysql mysql    406 Nov 9 09:02 flush_test.cfg
-rw-r-----. 1 mysql mysql   8594 Nov 9 09:00 flush_test.frm
-rw-r-----. 1 mysql mysql 98304 Nov 9 09:00 flush_test.ibd
-rw-rw----. 1 mysql mysql   8782 Jul 10 14:30 rule_01.frm
-rw-rw----. 1 mysql mysql 114688 Jul 10 14:30 rule_01.ibd
-rw-rw----. 1 mysql mysql   8556 Jul 10 14:30 tb_test.frm
-rw-rw----. 1 mysql mysql 98304 Jul 10 15:29 tb_test.ibd

4.目的库建表，表结构一定要和目标MySQL上的一样，再执行丢弃表空间命令
create table flush_test
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
name varchar(32),
   PRIMARY KEY (`id`)
);

ALTER TABLE flush_test DISCARD TABLESPACE;

5.建源服务器上的cfg和idb文件拷贝到目的机器上
把dummy.cfg和dummy.ibd远程拷备到另一台服务器上

scp flush_test.cfg root@192.168.1.118:/opt/mysql5730/data/db_hxl/
scp flush_test.ibd root@192.168.1.118:/opt/mysql5730/data/db_hxl/

文件传输到目标机器后需要修改相应的权限
[root@localhost db_hxl]# chown -R mysql:mysql flush_test.cfg
[root@localhost db_hxl]# chown -R mysql:mysql flush_test.ibd

然后回到原数据库执行解释操作，再查看，已经没有 flush_test.cfg文件了,所以一定确定scp完了后再解锁：

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

[root@localhost db_hxl]# ls -al
total 348
drwx------. 2 mysql mysql    140 Nov 9 09:14 .
drwxr-xr-x. 8 mysql mysql   4096 Jul 10 15:24 ..
-rw-rw----. 1 mysql mysql     61 Jul 10 14:30 db.opt
-rw-r-----. 1 mysql mysql   8594 Nov 9 09:00 flush_test.frm
-rw-r-----. 1 mysql mysql 98304 Nov 9 09:00 flush_test.ibd
-rw-rw----. 1 mysql mysql   8782 Jul 10 14:30 rule_01.frm
-rw-rw----. 1 mysql mysql 114688 Jul 10 14:30 rule_01.ibd
-rw-rw----. 1 mysql mysql   8556 Jul 10 14:30 tb_test.frm
-rw-rw----. 1 mysql mysql 98304 Jul 10 15:29 tb_test.ibd

6.目的库执行导入表空间命令，然后查看表数据
ALTER TABLE flush_test IMPORT TABLESPACE;

mysql> select * from flush_test;
+----+--------+
| id | name   |
+----+--------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
| 5 | name5 |
| 6 | name6 |
| 7 | name7 |
| 8 | name8 |
| 9 | name9 |
| 10 | name10 |
+----+--------+
10 rows in set (0.00 sec)

7.查看日志
2020-11-09T01:16:14.345927Z 3 [Note] InnoDB: Importing tablespace for table 'db_hxl/flush_test' that was exported from host 'localhost.localdomain'
2020-11-09T01:16:14.346048Z 3 [Note] InnoDB: Phase I - Update all pages
2020-11-09T01:16:14.346709Z 3 [Note] InnoDB: Sync to disk
2020-11-09T01:16:14.348903Z 3 [Note] InnoDB: Sync to disk - done!
2020-11-09T01:16:14.349670Z 3 [Note] InnoDB: Phase III - Flush changes to disk
2020-11-09T01:16:14.356108Z 3 [Note] InnoDB: Phase IV - Flush complete
2020-11-09T01:16:14.356456Z 3 [Note] InnoDB: `db_hxl`.`flush_test` autoinc value set to 11