Shulou(Shulou.com)06/01 Report--

1. Background

* InnoDB tablespaces can be shared or independent. In the case of shared tablespaces, all tablespaces are placed in one file: ibdata1,ibdata2..ibdataN, in which case there should be no way to migrate tablespaces at this time unless fully migrated.

* whether it is a shared or independent tablespace, the metadata of each data table in InnoDB is always stored in the shared tablespace ibdata1, so this file is essential, and it can also be used to store information such as data dictionaries.

* data files in separate tablespaces are stored separately in .ibd files.

* MySQL version 5.6 supports import and export of independent tablespaces.

two。 Environment [2 DB instances, MySQL 5.6Table Migration to MySQL5.7]

* Source instance 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)

* destination instance 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)

* Database and table information migrated by source instance 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 ('Maureen') NOT NULL DEFAULT 'int, `age` int (11) NOT NULL DEFAULT' 0' PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 | +-+- - -- + 1 row in set (0.01sec) 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. Smooth migration practice [migrate users table under mytest database]

* the destination MySQL instance creates the same database and table [you need to specify row_format=compact to create a table in MySQL 5.7]

Mysql > create database mytest character set utf8mb4;Query OK, 1 row affected (0.03 sec) mysql > use mytest;Database changedmysql > CREATE TABLE `users` (- > `id` bigint (20) NOT NULL AUTO_INCREMENT,-> `name` varchar (255,255) NOT NULL,-> `sex` enum ('Maure.') NOT NULL DEFAULT' NOT NULL,-> `age`int (11) NOT NULL DEFAULT '0mm,-> PRIMARY KEY (`id`)->) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 row_format=compact Query OK, 0 rows affected (.59 sec) mysql > system ls-l / data/mysql_data7/mytest/total 64 RW users.frm-rw-r- 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

* the destination MySQL instance discards the tablespace

Mysql > alter table users discard tablespace;Query OK, 0 rows affected (.01 sec) mysql > system ls-l / data/mysql_data7/mytest/total 16murr RW mysql mysql r-1 mysql mysql 67 Jul 18 05:21 db.opt-rw-r- 1 mysql mysql 8648 Jul 18 05:21 users.frm

* the source MySQL instance refreshes the table to disk and adds lock, and the current table quiesce status is read-only, and the. cfg metadata file is created

Mysql > flush tables users for export;Query OK, 0 rows affected (0.00 sec)

* copy table files users.ibd and users.cfg files from the source MySQL instance service to the destination MySQL instance

[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'

* modify the owner and all groups of the copied file under the destination MySQL instance data file

[root@MySQL ~] # chown-v mysql.mysql / data/mysql_data7/mytest/users. {cfg,ibd} changed ownership of `/ data/mysql_data7/mytest/users.cfg' to mysql:mysqlchanged ownership of` / data/mysql_data7/mytest/users.ibd' to mysql:mysql

* release lock from the source MySQL instance

Mysql > unlock tables;Query OK, 0 rows affected (0.00 sec)

* load tablespaces to the destination MySQL instance

Mysql > alter table users import tablespace;Query OK, 0 rows affected (0.04 sec)

* View the destination MySQL instance table data [MySQL5.6 data is migrated successfully]

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. Pay attention to the problem

* when MySQL 5.6 data is migrated to MySQL5.7, an error will be reported if the row_format,import table data is not specified when creating the destination table, because it is Antelope in MySQL 5.6 and Barracuda in MySQL 5.7, mainly due to changes in table compression and the dynamic format of rows.

5. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.