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
-- The End --
