MySQL转换表的引擎

有很多种方法可以将表的存储引擎转换成另一种引擎。每种方法都有其优缺点,在这里介绍四种方法:

选择优先级(pt-online-schema-change > 创建与查询 > 导出和导入 > ALTER TABLE)。
 
•ALTER TABLE

 将表从一个引擎修改为另一个引擎最简单的方法是使用ALTER TABLE语句。下面的语句将TB表的引擎修改为InnoDB:

MySQL> ALTER TABLE TB ENGINE = InnoDB;

  PS:该方法适用于任何存储引擎。但有一个问题:需要很长的执行时间。

  MySQL会按行将数据库从原表复制到一张新的表中,在复制期间可能会消耗系统所有的I/O能力,同时原表上会加上读锁。
 
•导出和导入(mysqldump)

  为了更好的控制转换的过程,可以使用mysqldump工具将数据导出到文件,然后修改文件中CREATE TABLE语句的存储引擎选项,注意同时修改表名,因为同一个数据库中不能存在相同的表名,即使他们使用的是不同的存储引擎。

  PS:mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE语句,不注意这一点可能会导致数据丢失。

  1、下面的语句将DB库中的TB表数据导出到tb.sql文件中:

[root@desktop]# mysqldump -u root -p DB TB > tb.sql

  2、修改tb.sql文件(如果需要备份TB表,请更改表名):

CREATE TABLE `TB` (
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  3、下面的语句将tb.sql文件导入到DB库中:

[root@desktop]# mysql -u root -p DB < tb.sql


•创建与查询(CREATE 和 SELECT)

  这种方法综合第一种方法的高效和第二种方法的安全。不需要导出整个表的数据,而是首先创建一个新的存储引擎的表,然后利用INSERT...SELECT语法来导数据:

  数据量不大使用以下语句:

mysql > CREATE TABLE TB2 like TB1;
mysql > ALTER TABLE TB2 ENGINE=InnoDB;
mysql > INSERT INTO TB2 SELECT * FROM TB1;


 数据量大使用以下语句:

mysql > CREATE TABLE TB2 like TB1;
mysql > ALTER TABLE TB2 ENGINE=InnoDB;
mysql > START TRANSACTION;
mysql > INSERT INTO TB2 SELECT * FROM TB1 WHERE id BETWEEN x AND y;
mysql > COMMIT;

  PS:这样的操作完成后,新表是原表的一个全量复制,原表还在,如需要可以删除原表。

  如果有必要,可以在执行的过程中对原表进行加锁,以确保新表和原表的数据一致。

•使用Percona Toolkit提供的pt-online-schema-change工具:

  使用以下命令可将DB库中的TB1表转换成InnoDB:

[root@desktop]# pt-online-schema-change -u root -h 127.0.0.1 -p 123456 --alter='ENGINE=Innodb' --execute A=utf8,D=DB,t=TB1

  执行成功后会有如下提示:

Successfully altered `DB`.`TB1`.

  PS:原理为创建临时表->修改修改结构->记录数据->删掉原表->重命名临时表 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/5fafe54e3377ad7c55001f9aaae40ce1.html