alter table コマンドで MyISAM テーブルを InnoDB へ変換できる。


alter table hoge_table_name engine = InnoDB;
InnoDB は、MyISAM ストレージ エンジンがするのと同じように、インデックスを別々に作成する為の特別な最適化を行いません。従って、テーブルをエクスポート、インポートしたり、後でインデックスを作成したりはしません。テーブルを InnoDB に変換する一番早い方法は、InnoDB テーブルに直接挿入する事です。それは、ALTER TABLE ... ENGINE=INNODB を利用する、または同一定義を利用して空の InnoDB テーブルを作成し、INSERT INTO ... SELECT * FROM ... を利用して行を挿入するという事です。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.6.2 MyISAM テーブルを InnoDB に変換する

実際にやってみた

環境は Debian GNU/Linux 6.0 (squeeze) + MySQL 5.1


$ uname -mrsv
Linux 2.6.32-5-amd64 #1 SMP Sun May 6 04:00:17 UTC 2012 x86_64
 
$ dpkg -l | grep mysql-server-5.1
ii  mysql-server-5.1   5.1.63-0+squeeze1   MySQL database server binaries and system database setup

手元には、ミニブログサービス Timelog のデータを収めたテーブル Timelog があるので、これを InnoDB に変換する。

変換前の情報。ストレージエンジンがMyISAMだったり、レコード数が4210だったり。参考までにカラム情報も載せておく。


mysql> show table status where Name = 'Timelog';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| timelog | MyISAM |      10 | Dynamic    | 4210 |            188 |      792516 | 281474976710655 |       398336 |         0 |           NULL | 2012-05-26 09:55:11 | 2012-05-26 09:55:11 | 2012-05-26 09:55:11 | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
 
mysql> show columns from timelog;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | varchar(255) | NO   | PRI |         |       |
| modified | datetime     | YES  | MUL | NULL    |       |
| memo     | text         | NO   |     | NULL    |       |
| toid     | varchar(255) | NO   |     |         |       |
| toname   | text         | NO   |     | NULL    |       |
| replyid  | varchar(255) | NO   |     |         |       |
| tag      | text         | NO   |     | NULL    |       |
| star     | int(11)      | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

alter table コマンドで MyISAM を InnoDB へ変換。処理時間は、1秒もかからない。


mysql> alter table timelog engine = InnoDB;
Query OK, 4210 rows affected (0.27 sec)
Records: 4210  Duplicates: 0  Warnings: 0

変換後の情報。ストレージエンジンがInnoDBだったり、レコード数が変わらず4210だったり。


mysql> show table status where Name = 'Timelog';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| timelog | InnoDB |      10 | Compact    | 4382 |            362 |     1589248 |               0 |       311296 |   9437184 |           NULL | 2012-07-31 21:38:29 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

バックアップ方法も変更

MyISAM のときは mysqlhotcopy コマンドで定期的にバックアップしてた。


/usr/bin/mysqlhotcopy -u root -p __hoge_password__ --addtodest hoge_database./timelog/ /home/hoge/backupspace

InnoDB のテーブルをバックアップするときは mysqldump コマンドを使う。


/usr/bin/mysqldump -uroot -p__hoge_password__ --single-transaction --master-data --flush-logs hoge_database timelog > /home/hoge/backupspace/timelog.sql

実際にやってみたら、これでちゃんと動いてるぽい。

MyISAMではmysqlhotcopyコマンドを使って高速にバックアップをとることができるが、InnoDBでは別の方法を用いる必要がある。InnoDB Hot Backupは高速だが有料である。mysqldumpは時間が掛かってしまう。レプリケーションを用いるとマスターに負荷をかけずにバックアップをとることができるが、サーバーの台数が増えてしまう。悩ましいところであるが、ベストなバックアップ方法を選択しよう。

漢(オトコ)のコンピュータ道: MyISAMからInnoDBへ切り替えるときの注意点

やっぱり、InnoDB だとバックアップがたいへんそうね。

もうひとつのテーブルはやけに時間がかかった

試しに、ちょっとレコード数が多めのテーブルを MyISAM からInnoDB に変換したら40分もかかった。。。


mysql> ALTER TABLE hogehoge ENGINE = InnoDB;
Query OK, 186629 rows affected (41 min 5.95 sec)
Records: 186629  Duplicates: 0  Warnings: 0

Ref.

tags: mysql

Posted by NI-Lab. (@nilab)