-- 环境描述: CentOS 7.6 MySQL 5.7.33
MySQL Transportable Tablespace
将大的InnoDB表从一个实例,移动或者复制到另一个实例,有很多的方法,在5.6之前常用的是通过物理或者逻辑备份来实现。
在5.6.6+的版本中,用到了一种基于表空间迁移的快速方法,即类似Oracle TTS。
因为用到,故整理记录至此。
实验用到两台机器,单机单实例,MySQL 5.6.30。
并将通过vm1> mysql1> vm2> mysql2> 区分两台shell环境和mysql client环境。
〇 过程:
① 先在mysql1上创建测试数据:
- mysql> \R mysql1>
- PROMPT set to 'mysql1> '
- mysql1> USE test;
- Database changed
- mysql1> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
- Query OK, 0 rows affected (0.01 sec)
- mysql1> INSERT INTO tts(name) VALUES(REPEAT('a',128));
- Query OK, 1 row affected (0.00 sec)
- mysql1> INSERT INTO tts(name) SELECT name FROM tts;
- Query OK, 1 row affected (0.00 sec)
- Records: 1 Duplicates: 0 Warnings: 0
- mysql1> INSERT INTO tts(name) SELECT name FROM tts;
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- ………………………………
- mysql1> INSERT INTO tts(name) SELECT name FROM tts;
- Query OK, 131072 rows affected (0.79 sec)
- Records: 131072 Duplicates: 0 Warnings: 0
- mysql1> INSERT INTO tts(name) SELECT name FROM tts;
- Query OK, 262144 rows affected (2.15 sec)
- Records: 262144 Duplicates: 0 Warnings: 0
- mysql1> \! du -sh /data/mysql/test/tts*
- 12K /data/mysql/test/tts.frm
- 92M /data/mysql/test/tts.ibd
② 再保证mysql2上有相同的库表结构,此处为新建,并将mysql2上新建的test.tts表discard掉ibd文件:
- mysql> \R mysql2>
- PROMPT set to 'mysql2> '
- mysql2> USE test;
- Database changed
- mysql2> CREATE TABLE tts(id int PRIMARY KEY AUTO_INCREMENT, name char(128));
- Query OK, 0 rows affected (0.01 sec)
- mysql2> \! du -sh /data/mysql/test/tts*
- 12K /data/mysql/test/tts.frm
- 96K /data/mysql/test/tts.ibd
- 注意!该alter table ... discard tablespace操作会记录binlog并影响复制结构,慎用,或set sql_log_bin=0;
- mysql2> ALTER TABLE tts DISCARD TABLESPACE;
- Query OK, 0 rows affected (0.01 sec)
- mysql2> \! du -sh /data/mysql/test/tts*
- 12K /data/mysql/test/tts.frm
③ 对mysql1的test.tts表做FLUSH TABLES操作,此时会多了一个cfg文件:
- mysql1> FLUSH TABLE tts FOR EXPORT;
- Query OK, 0 rows affected (0.05 sec)
- mysql1> \! du -sh /data/mysql/test/tts*
- 4.0K /data/mysql/test/tts.cfg
- 12K /data/mysql/test/tts.frm
- 92M /data/mysql/test/tts.ibd
④ 开多一个终端,在vm1上将ibd和cfg文件scp到vm2上:
- vm1> scp /data/mysql/test/tts.{ibd,cfg} user@vm2:/data/mysql/test
- user@vm2's password:
- tts.ibd 100% 92MB 46.0MB/s 00:02
- tts.cfg 100% 380 0.4KB/s 00:00
⑤ 将mysql1的test.tts表做UNLOCK操作(此时可发现cfg文件已被删除):
- mysql1> UNLOCK TABLES;
- Query OK, 0 rows affected (0.00 sec)
- mysql1> \! du -sh /data/mysql/test/tts*
- 12K /data/mysql/test/tts.frm
- 92M /data/mysql/test/tts.ibd
⑥ 在vm2上将传过来的ibd和cfg文件修改权限:
- vm2> chown mysql:mysql /data/mysql/test/tts.{ibd,cfg}
⑦ 将上述ibd文件IMPORT到tts表中:
- mysql2> ALTER TABLE tts IMPORT TABLESPACE;
- Query OK, 0 rows affected (0.93 sec)
- mysql2> SELECT count(*) FROM tts;
- +----------+
- | count(*) |
- +----------+
- | 524288 |
- +----------+
- 1 row in set (0.94 sec)
至此,已经将mysql1实例上的tts表中数据快速地迁移到mysql2实例上了。
〇 上述几个步骤的解释:
操作②中的discard tablespace会在表上加上MDL锁,删除change buffer所有相关的缓存项,设置表元数据信息,标志tablespace为删除状态,重新生成表的id,保证基于表id的操作后续均会失败,再将idb文件干掉,在②中的两次du可以看到.idb文件已经被删除了。这是一个十分危险的操作,慎重;此操作也会被记录到binlog中,若在复制结构可能会有很大的影响,切记先临时关闭binlog。
操作③中的flush table ... for export会给test.tts表加上共享锁,并将purge coordinator thread(在并行复制中类似sql thread)停止,并且将脏页强制同步到磁盘,创建并将test.tts表的元数据写入.cfg文件;
FLUSH TABLES ... FOR EXPORT在error log中体现了这个过程:
[Note] InnoDB: Sync to disk of '"test"."tts"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/tts.cfg'
[Note] InnoDB: Table '"test"."tts"' flushed to disk
操作⑤执行unlock tables将③中的锁解除,此时.cfg文件被删掉,purge coordinator thread也会重新启动;(在做flush table ... for export时不能关闭session,避免锁释放造成.cfg文件删除)
UNLOCK TABLES在error log中记录为:
[Note] InnoDB: Deleting the meta-data file './test/tts.cfg'
[Note] InnoDB: Resuming purge
操作⑦则是通过import tablespace操作,将从vm1上传输过来的.ibd文件和导入到tts表中,此时.cfg文件也必须存在;
ALTER TABLE ... IMPORT TABLESPACE在error log中记录为:
[Note] InnoDB: Importing tablespace for table 'test/tts' that was exported from host 'vm01'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete
[Note] InnoDB: "test"."tts" autoinc value set to 786406
过程为读取cfg文件:表定义,索引定义,索引RootPage,列定义等等。再读取import文件每一个page,检查完整性,根据读取到的cfg文件,重新设置当前表的元数据信息。
总结一下整个过程就是:
create table $new_table ...
alter table $new_table discard tablespace;(删除新表的tablespace文件,保留frm文件)
flush table $old_table for export;(关闭该表,并且生成cfg文件)
拷贝ibd文件,已经对应的cfg文件。
unlock tables;
将ibd文件和cfg文件copy到新地址,修改好权限
alter table $new_table import tablespace;
〇 限制:
两个实例都必须开启独立表空间,innodb_file_per_table
迁移的两个实例的innodb_page_size必须一致,并且mysql server版本建议一致
不支持在分区表上执行discard tablespace
不支持在有主外键关系的表上执行discard tablespace,除非设置foregin_key_checks=0
+++++++++++++++++++
测试案例:
[BEGIN] 21/4/5 13:39:06
flush table quickspintransactions for export;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) malta>>show variables like 'innodb_file%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 5
Current database: malta
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.27 sec)
(root@localhost) malta>>show create table quickspintransactions\G;
*************************** 1. row ***************************
Table: quickspintransactions
Create Table: CREATE TABLE `quickspintransactions` (
`qsid` int(11) NOT NULL AUTO_INCREMENT,
`createdate` datetime NOT NULL,
`userid` int(11) NOT NULL,
`qsgameref` varchar(16) NOT NULL,
`qsroundid` varchar(40) NOT NULL DEFAULT '',
`qstransactionid` bigint(20) unsigned NOT NULL,
`qstransactiontype` varchar(20) NOT NULL,
`bet` decimal(10,2) NOT NULL DEFAULT '0.00',
`win` decimal(10,2) NOT NULL DEFAULT '0.00',
`currency` varchar(3) NOT NULL,
`cancelled` tinyint(4) NOT NULL DEFAULT '0',
`cancelled_by` bigint(20) DEFAULT NULL,
`transactionid_bet` int(11) DEFAULT NULL,
`bonustransactionid_bet` int(11) DEFAULT NULL,
`transactionid_win` int(11) NOT NULL DEFAULT '0',
`bonustransactionid_win` int(11) NOT NULL DEFAULT '0',
`balanceaftertransaction` decimal(12,2) DEFAULT NULL,
`bonusbalanceaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
`turnoverreqaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`qsid`),
UNIQUE KEY `qstransactionid` (`qstransactionid`),
KEY `userid` (`userid`),
KEY `qsroundid` (`qsroundid`)
) ENGINE=InnoDB AUTO_INCREMENT=23865698 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
(root@localhost) (none)>>use martin
Database changed
(root@localhost) martin>>show tables;
Empty set (0.00 sec)
(root@localhost) martin>>CREATE TABLE `quickspintransactions` (
-> `qsid` int(11) NOT NULL AUTO_INCREMENT,
-> `createdate` datetime NOT NULL,
-> `userid` int(11) NOT NULL,
-> `qsgameref` varchar(16) NOT NULL,
-> `qsroundid` varchar(40) NOT NULL DEFAULT '',
-> `qstransactionid` bigint(20) unsigned NOT NULL,
-> `qstransactiontype` varchar(20) NOT NULL,
-> `bet` decimal(10,2) NOT NULL DEFAULT '0.00',
-> `win` decimal(10,2) NOT NULL DEFAULT '0.00',
-> `currency` varchar(3) NOT NULL,
-> `cancelled` tinyint(4) NOT NULL DEFAULT '0',
-> `cancelled_by` bigint(20) DEFAULT NULL,
-> `transactionid_bet` int(11) DEFAULT NULL,
-> `bonustransactionid_bet` int(11) DEFAULT NULL,
-> `transactionid_win` int(11) NOT NULL DEFAULT '0',
-> `bonustransactionid_win` int(11) NOT NULL DEFAULT '0',
-> `balanceaftertransaction` decimal(12,2) DEFAULT NULL,
-> `bonusbalanceaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
-> `turnoverreqaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
-> PRIMARY KEY (`qsid`),
-> UNIQUE KEY `qstransactionid` (`qstransactionid`),
-> KEY `userid` (`userid`),
-> KEY `qsroundid` (`qsroundid`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=23865698 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
(root@localhost) martin>>show create table quickspintransactions\G;
*************************** 1. row ***************************
Table: quickspintransactions
Create Table: CREATE TABLE `quickspintransactions` (
`qsid` int(11) NOT NULL AUTO_INCREMENT,
`createdate` datetime NOT NULL,
`userid` int(11) NOT NULL,
`qsgameref` varchar(16) NOT NULL,
`qsroundid` varchar(40) NOT NULL DEFAULT '',
`qstransactionid` bigint(20) unsigned NOT NULL,
`qstransactiontype` varchar(20) NOT NULL,
`bet` decimal(10,2) NOT NULL DEFAULT '0.00',
`win` decimal(10,2) NOT NULL DEFAULT '0.00',
`currency` varchar(3) NOT NULL,
`cancelled` tinyint(4) NOT NULL DEFAULT '0',
`cancelled_by` bigint(20) DEFAULT NULL,
`transactionid_bet` int(11) DEFAULT NULL,
`bonustransactionid_bet` int(11) DEFAULT NULL,
`transactionid_win` int(11) NOT NULL DEFAULT '0',
`bonustransactionid_win` int(11) NOT NULL DEFAULT '0',
`balanceaftertransaction` decimal(12,2) DEFAULT NULL,
`bonusbalanceaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
`turnoverreqaftertransaction` decimal(12,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`qsid`),
UNIQUE KEY `qstransactionid` (`qstransactionid`),
KEY `userid` (`userid`),
KEY `qsroundid` (`qsroundid`)
) ENGINE=InnoDB AUTO_INCREMENT=23865698 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[root@newmachine martin]# ll
total 164
-rwxr-xr-x 1 mysql mysql 67 Apr 2 13:17 db.opt
-rw-r----- 1 mysql mysql 9470 Apr 5 13:40 quickspintransactions.frm
-rw-r----- 1 mysql mysql 147456 Apr 5 13:40 quickspintransactions.ibd
(root@localhost) martin>>alter table quickspintransactions discard tablespace;
Query OK, 0 rows affected (0.15 sec)
[root@newmachine martin]# ll
total 16
-rwxr-xr-x 1 mysql mysql 67 Apr 2 13:17 db.opt
-rw-r----- 1 mysql mysql 9470 Apr 5 13:40 quickspintransactions.frm
[root@newmachine malta]# ll quickspintransactions*
-rw-r----- 1 mysql mysql 1982 Apr 5 13:53 quickspintransactions.cfg
-rw-r----- 1 mysql mysql 9470 Apr 2 21:19 quickspintransactions.frm
-rw-r----- 1 mysql mysql 1908408320 Apr 2 21:33 quickspintransactions.ibd
[root@newmachine malta]# cp quickspintransactions.cfg quickspintransactions.ibd ../martin/
[root@newmachine martin]# ll
total 1863704
-rwxr-xr-x 1 mysql mysql 67 Apr 2 13:17 db.opt
-rw-r----- 1 root root 1982 Apr 5 13:58 quickspintransactions.cfg
-rw-r----- 1 mysql mysql 9470 Apr 5 13:40 quickspintransactions.frm
-rw-r----- 1 root root 1908408320 Apr 5 13:58 quickspintransactions.ibd
[root@newmachine martin]# chown mysql:mysql quickspintransactions.*
[root@newmachine martin]# ll
total 1863704
-rwxr-xr-x 1 mysql mysql 67 Apr 2 13:17 db.opt
-rw-r----- 1 mysql mysql 1982 Apr 5 13:58 quickspintransactions.cfg
-rw-r----- 1 mysql mysql 9470 Apr 5 13:40 quickspintransactions.frm
-rw-r----- 1 mysql mysql 1908408320 Apr 5 13:58 quickspintransactions.ibd
(root@localhost) malta>>show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+----------+------------------+
| 5 | root | localhost | malta | Query | 0 | starting | show processlist |
| 6 | root | localhost | martin | Sleep | 761 | | NULL |
+----+------+-----------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
(root@localhost) malta-3306>>show table status like 'quickspintransactions'\G;
*************************** 1. row ***************************
Name: quickspintransactions
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8413279
Avg_row_length: 134
Data_length: 1132462080
Max_data_length: 0
Index_length: 705183744
Data_free: 5242880
Auto_increment: 23865698
Create_time: 2021-04-02 21:19:54
Update_time: 2021-04-02 21:32:55
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
(root@localhost) malta>>unlock tables;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) malta>>unlock tables;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) martin>>alter table quickspintransactions import tablespace;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: martin
Query OK, 0 rows affected (20.30 sec)
(root@localhost) martin>>show tables;
+-----------------------+
| Tables_in_martin |
+-----------------------+
| quickspintransactions |
+-----------------------+
1 row in set (0.00 sec)
(root@localhost) martin>>select count(*) from quickspintransactions\G;
*************************** 1. row ***************************
count(*): 8865698
1 row in set (43.87 sec)
(root@localhost) malta>>
[END] 21/4/5 14:44:57
以上为正常的传输表空间测试:
++++++++++++++++++++