InnoDB--------独立表空间平滑迁移

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

1. 背景

   * InnoDB的表空间可以是共享的或独立的。如果是共享表空间,则所有的表空间都放在一个文件里:ibdata1,ibdata2..ibdataN,这种情况下,目前应该还没办法实现表空间的迁移,除非完全迁移。

  * 不管是共享还是独立表空间,InnoDB每个数据表的元数据(metadata)总是保存在 ibdata1 这个共享表空间里,因此该文件必不可少,它还可以用来保存各种数据字典等信息。

   * 独立表空间中数据文件单独存放在.ibd文件中。

   * MySQL 5.6版本开始支持独立表空间导入与导出。


2. 环境 [ 2台DB实例, MySQL 5.6表迁移至MySQL5.7 ]

wKiom1lwyB7iW4nOAABhIqWgD9E628.jpg

   * 源实例 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 |
+ ----+-------+-----+-----+
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 |
+ ----+-------+-----+-----+
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,如需转载请自行联系原作者





相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9月前
|
存储 关系型数据库 MySQL
第9章 【MySQL】InnoDB的表空间
第9章 【MySQL】InnoDB的表空间
52 0
第9章 【MySQL】InnoDB的表空间
|
8天前
|
存储 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
22 7
|
30天前
|
存储 关系型数据库 MySQL
认真学习InnoDB的数据存储结构中的区、段与表空间
认真学习InnoDB的数据存储结构中的区、段与表空间
72 2
|
关系型数据库 数据库
Innodb表空间迁移
有cfg文件时表空间迁移 在源表上显示建表语句:
|
存储 关系型数据库 MySQL
一文带你了解MySQL之InnoDB表空间
通过前边的内容,相信大家都知道了表空间是一个抽象的概念,对于系统表空间来说,对应着文件系统中一个或多个实际文件;对于每个独立表空间来说,对应着文件系统中一个名为表名.ibd的实际文件。 大家可以把表空间想象成被切分为许多个页的池子,当我们想为某个表插入一条记录的时候,就从池子中捞出一个对应的页来把数据写进去。 本章内容会深入到表空间的各个细节中,带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念,虽然这些概念都不难,但是却相互依赖,所以奉劝大家看的时候:不要跳着看
179 0
|
存储 关系型数据库 MySQL
一文带你了解MySQL之InnoDB表空间
通过前边的内容,相信大家都知道了表空间是一个抽象的概念,对于系统表空间来说,对应着文件系统中一个或多个实际文件;对于每个独立表空间来说,对应着文件系统中一个名为表名.ibd的实际文件。 大家可以把表空间想象成被切分为许多个页的池子,当我们想为某个表插入一条记录的时候,就从池子中捞出一个对应的页来把数据写进去。 本章内容会深入到表空间的各个细节中,带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念,虽然这些概念都不难,但是却相互依赖,所以奉劝大家看的时候:不要跳着看
152 0
|
存储 关系型数据库 MySQL
InnoDB 独立表空间与共享表空间
InnoDB 独立表空间与共享表空间
93 0
InnoDB 独立表空间与共享表空间
|
关系型数据库 MySQL
InnoDB系统表空间定义规则
1. InnoDB系统表空间定义规则 2. 并行复制设置不当可能导致主从数据不一致
|
存储 关系型数据库 MySQL
独立表空间&系统表空间总结---innoDB表空间(三十五)
独立表空间&系统表空间总结---innoDB表空间(三十五)
|
1天前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.3-InnoDB中的锁
【MySQL技术内幕】6.3-InnoDB中的锁
136 57