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.
- MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.6.2 MyISAM テーブルを InnoDB に変換する
- MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.27 SHOW TABLE STATUS 構文
tags: mysql
Posted by NI-Lab. (@nilab)