1. 背景
* InnoDB的表空间可以是共享的或独立的。如果是共享表空间，则所有的表空间都放在一个文件里：ibdata1,ibdata2..ibdataN，这种情况下，目前应该还没办法实现表空间的迁移，除非完全迁移。
* 不管是共享还是独立表空间，InnoDB每个数据表的元数据(metadata)总是保存在 ibdata1 这个共享表空间里，因此该文件必不可少，它还可以用来保存各种数据字典等信息。
* 独立表空间中数据文件单独存放在.ibd文件中。
* MySQL 5.6版本开始支持独立表空间导入与导出。
2. 环境 [ 2台DB实例, MySQL 5.6表迁移至MySQL5.7 ]
* 源实例 MySQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> show variables
like
'innodb%version'
;
+
----------------+--------+
| Variable_name | Value |
+
----------------+--------+
| innodb_version | 5.6.36 |
+
----------------+--------+
1 row
in
set
(0.01 sec)
mysql> show variables
like
'datadir'
;
+
---------------+--------------------+
| Variable_name | Value |
+
---------------+--------------------+
| datadir | /data/mysql_data6/ |
+
---------------+--------------------+
1 row
in
set
(0.00 sec)
|
* 目的实例 MySQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> show variables
like
'innodb%version'
;
+
----------------+--------+
| Variable_name | Value |
+
----------------+--------+
| innodb_version | 5.7.18 |
+
----------------+--------+
1 row
in
set
(0.00 sec)
mysql> show variables
like
'datadir'
;
+
---------------+--------------------+
| Variable_name | Value |
+
---------------+--------------------+
| datadir | /data/mysql_data7/ |
+
---------------+--------------------+
1 row
in
set
(0.01 sec)
|
* 源实例 MySQL 迁移的数据库与表信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
mysql>
select
database
();
+
------------+
|
database
() |
+
------------+
| mytest |
+
------------+
1 row
in
set
(0.00 sec)
mysql> show
create
table
users;
+
-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users |
CREATE
TABLE
`users` (
`id`
bigint
(20)
NOT
NULL
AUTO_INCREMENT,
`
name
`
varchar
(255)
NOT
NULL
,
`sex` enum(
'M'
,
'F'
)
NOT
NULL
DEFAULT
'M'
,
`age`
int
(11)
NOT
NULL
DEFAULT
'0'
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5
DEFAULT
CHARSET=utf8mb4 |
+
-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.01 sec)
mysql>
select
*
from
users;
+
----+-------+-----+-----+
| id |
name
| sex | age |
+
----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 38 |
| 3 | sea | M | 43 |
| 4 | lisea | M | 36 |
+
----+-------+-----+-----+
4
rows
in
set
(0.00 sec)
|
3. 平滑迁移实战 [ 迁移mytest数据库下users表 ]
* 目的MySQL实例创建相同的数据库与表 [ MySQL 5.7中创建表需要指定row_format=compact ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql>
create
database
mytest
character
set
utf8mb4;
Query OK, 1 row affected (0.03 sec)
mysql> use mytest;
Database
changed
mysql>
CREATE
TABLE
`users` (
-> `id`
bigint
(20)
NOT
NULL
AUTO_INCREMENT,
-> `
name
`
varchar
(255)
NOT
NULL
,
-> `sex` enum(
'M'
,
'F'
)
NOT
NULL
DEFAULT
'M'
,
-> `age`
int
(11)
NOT
NULL
DEFAULT
'0'
,
->
PRIMARY
KEY
(`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5
DEFAULT
CHARSET=utf8mb4 row_format=compact;
Query OK, 0
rows
affected (0.59 sec)
mysql> system ls -l /data/mysql_data7/mytest/
total 64
-rw-r
----- 1 mysql mysql 67 Jul 18 05:21 db.opt
-rw-r
----- 1 mysql mysql 8648 Jul 18 05:21 users.frm
-rw-r
----- 1 mysql mysql 49152 Jul 18 05:21 users.ibd
|
* 目的MySQL实例丢弃表空间
|
1
2
3
4
5
6
7
|
mysql>
alter
table
users discard tablespace;
Query OK, 0
rows
affected (0.01 sec)
mysql> system ls -l /data/mysql_data7/mytest/
total 16
-rw-r
----- 1 mysql mysql 67 Jul 18 05:21 db.opt
-rw-r
----- 1 mysql mysql 8648 Jul 18 05:21 users.frm
|
* 源MySQL实例刷新表至磁盘并加lock,并且当前表quiesce状态，只读，且创建.cfg metadata文件
|
1
2
|
mysql> flush tables users
for
export;
Query OK, 0
rows
affected (0.00 sec)
|
* 从源MySQL实例服务止拷贝表文件users.ibd, users.cfg文件至目的MySQL实例中
|
1
2
3
|
[root@MySQL ~]
# cp -v /data/mysql_data6/mytest/users.{cfg,ibd} /data/mysql_data7/mytest/
`
/data/mysql_data6/mytest/users
.cfg
' -> `/data/mysql_data7/mytest/users.cfg'
`
/data/mysql_data6/mytest/users
.ibd
' -> `/data/mysql_data7/mytest/users.ibd'
|
* 修改目的MySQL实例数据文件下拷贝文件的所有者与所有组
|
1
2
3
|
[root@MySQL ~]
# chown -v mysql.mysql /data/mysql_data7/mytest/users.{cfg,ibd}
changed ownership of `
/data/mysql_data7/mytest/users
.cfg' to mysql:mysql
changed ownership of `
/data/mysql_data7/mytest/users
.ibd' to mysql:mysql
|
* 源MySQL实例释放lock
|
1
2
|
mysql> unlock tables;
Query OK, 0
rows
affected (0.00 sec)
|
* 目的MySQL实例加载表空间
|
1
2
|
mysql>
alter
table
users import tablespace;
Query OK, 0
rows
affected (0.04 sec)
|
* 查看目的MySQL实例表数据 [ MySQL5.6数据成功迁移过来 ]
|
1
2
3
4
5
6
7
8
9
10
|
mysql>
select
*
from
users;
+
----+-------+-----+-----+
| id |
name
| sex | age |
+
----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 38 |
| 3 | sea | M | 43 |
| 4 | lisea | M | 36 |
+
----+-------+-----+-----+
4
rows
in
set
(0.00 sec)
|
4. 注意问题
* MySQL 5.6数据迁移到MySQL5.7时，如果创建目的表时不指定row_format，import表数据时会报错，原因在于MySQL 5.6中是Antelope，在MySQL 5.7中是Barracuda，主要是在表压缩和行的动态格式上有所改变。
5. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1949559，如需转载请自行联系原作者
