MySQLテーブルのストレージエンジン変更方法

MySQLではテーブルを作成時にストレージエンジンを指定することができますが、利用途中でストレージエンジンを変更することも可能です。
今回は3つの方法を紹介します。

ALTER TABLEを利用する方法
最も簡単な方法はALTER TABLEを利用することです。

mysql> ALTER TABLE test_table ENGINE = InnoDB;

この方法は全てのストレージエンジンで利用できますが、実行にかなり時間がかかる可能性があります。
MySQLは古いテーブルから新しいテーブルへ行単位のコピーを実行します。その間はサーバのディスクI/Oはすべて使用され、
変換中はテーブルに読み取りロックが発生します。アクセスの多いテーブルで利用する場合は注意が必要です。

mysqldumpを利用する方法
mysqldumpを利用してテーブルをダンプする方法です。ダンプしたファイルでは、既存のテーブルのDROP TABLE文と新規に作り直すCREATE TABLE文、データを挿入するINSERT文が含まれているので、
CREATE TABLE文を直接編集してENGINE=の部分を利用したいストレージエンジン名に変更します。

mysqldump -uroot -p testdb test_table > /tmp/dump.sql

vi /tmp/dump.sql

-- MySQL dump 10.13  Distrib 5.5.15, for Linux (x86_64)
--
-- Host: localhost    Database: testdb
-- ------------------------------------------------------
-- Server version       5.5.15-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test_table`
--

DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_table` (
  `id` int(11) DEFAULT NULL,
  `name` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_table`
--

LOCK TABLES `test_table` WRITE;
/*!40000 ALTER TABLE `test_table` DISABLE KEYS */;
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-09-14 13:51:10

ストレージエンジン変更前のデータも保持しておきたい場合は、ダンプファイルのDROP文を削除し、CREATE文のテーブル名を変更しましょう。

SELECTを用いた方法
今利用しているテーブルと同じテーブル定義の新テーブルを作成して、新テーブルのエンジンを変更してからデータを流し込む方法です。

mysql> CREATE TABLE new_table LIKE test_table;
Query OK, 0 rows affected (0.06 sec)

同じ構造になっています。

mysql> desc new_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | text    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

新テーブルのストレージエンジンを変更します。
データはまだいれていないのですぐに完了します。

mysql> ALTER TABLE new_table ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

変更前のテーブルからデータをインサートします。

mysql> INSERT INTO new_table SELECT * FROM test_table;
Query OK, 100000 rows affected (0.35 sec)
Records: 100000  Duplicates: 0  Warnings: 0

利用する場面によって、以上の3つの方法を使い分けましょう。

FAT47の底辺インフラ議事録

学んだことのメモ帳です

MySQLテーブルのストレージエンジン変更方法