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

1. 背景

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

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

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

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

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

wKiom1lwyB7iW4nOAABhIqWgD9E628.jpg

   * 源实例 MySQL

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 

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 迁移的数据库与表信息

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 ]

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实例丢弃表空间

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文件

mysql> flush tables users for export;

Query OK, 0 rows affected (0.00 sec)

   * 从源MySQL实例服务止拷贝表文件users.ibd, users.cfg文件至目的MySQL实例中

[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实例数据文件下拷贝文件的所有者与所有组

[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

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

　 * 目的MySQL实例加载表空间

mysql> alter table users import tablespace;

Query OK, 0 rows affected (0.04 sec)

   * 查看目的MySQL实例表数据　[ MySQL5.6数据成功迁移过来 ]

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. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

©著作权归作者所有：来自51CTO博客作者asd1123509133的原创作品，如需转载，请注明出处，否则将追究法律责任

MySQLdata DBAMySQL