MySQL数据库表数据迁移--ibd的使用

需求:

        现在线上mysql5.7 +生产库有一张10多个G的表数据,需要迁移到其他库中,现使用ibd的方式 进行数据的迁移;

步骤:

• demo

#1. 创建相同结构的表,并添加几条测试数据

	mysql> create table test3 like test;
	mysql> select * from test3;
	+------+------+----+---------------------+--------+------------+
	| age  | name | id | create_time         | is_man | salary_amt |
	+------+------+----+---------------------+--------+------------+
	| 11   | 11   |  1 | 2021-01-28 11:15:11 |      1 |      11.00 |
	| 22   | 22   |  2 | 2021-01-28 11:15:11 |      1 |      11.00 |
	+------+------+----+---------------------+--------+------------+


#2. 查看当前数据库数据存储位置,备份 test3.idb 

	mysql> select  @@datadir;
		+------------------------+
		| @@datadir              |
		+------------------------+
		| /usr/local/mysql/data/ |
		+------------------------+
	[root@hadoop001 test_db]# pwd
		/usr/local/mysql/data/test_db
	[root@hadoop001 test_db]# ll
		total 220
		-rw-r----- 1 mysqladmin dba    61 Jan 16 18:12 db.opt
		-rw-r----- 1 mysqladmin dba  8734 Jan 28 11:14 test3.frm
		-rw-r----- 1 mysqladmin dba 98304 Jan 28 11:21 test3.ibd
	
#3. 删除表空间 注: show global variables like 'innodb_file_per_table'; 是否 on 状态
	mysql> alter table test3 discard tablespace;
		Query OK, 0 rows affected (0.01 sec)
	# test3.idb 被删除
		[root@hadoop001 test_db]# ll  
		total 124
		-rw-r----- 1 mysqladmin dba    61 Jan 16 18:12 db.opt
		-rw-r----- 1 mysqladmin dba  8734 Jan 28 11:14 test3.frm
		
#4. 恢复表空间 
	#4.1 修改文件权限
		-rw-r----- 1 mysqladmin dba     61 Jan 16 18:12 db.opt
		-rw-r----- 1 mysqladmin dba   8734 Jan 28 11:14 test3.frm
		-rw-r----- 1 root       root 98304 Jan 28 13:47 test3.ibd
		-rw-r----- 1 mysqladmin dba   8734 Jan 26 16:31 test.frm
		-rw-r----- 1 mysqladmin dba  98304 Jan 26 16:32 test.ibd
		[root@hadoop001 test_db]# chown -R mysqladmin:dba test3.ibd 
		[root@hadoop001 test_db]# ll
			total 220
			-rw-r----- 1 mysqladmin dba    61 Jan 16 18:12 db.opt
			-rw-r----- 1 mysqladmin dba  8734 Jan 28 11:14 test3.frm
			-rw-r----- 1 mysqladmin dba 98304 Jan 28 13:47 test3.ibd
			-rw-r----- 1 mysqladmin dba  8734 Jan 26 16:31 test.frm
			-rw-r----- 1 mysqladmin dba 98304 Jan 26 16:32 test.ibd
	#4.2 执行mysql命令,idb与test3表空间绑定
		mysql> alter table test3 import tablespace;
			Query OK, 0 rows affected, 1 warning (0.08 sec)
	
#5. 验证结果
	mysql> select * from test3;
+------+------+----+---------------------+--------+------------+
| age  | name | id | create_time         | is_man | salary_amt |
+------+------+----+---------------------+--------+------------+
| 11   | 11   |  1 | 2021-01-28 11:15:11 |      1 |      11.00 |
| 22   | 22   |  2 | 2021-01-28 11:15:11 |      1 |      11.00 |
+------+------+----+---------------------+--------+------------+
2 rows in set (0.00 sec)