How to convert all MyISAM tables to InnoDB in a MySQL Database

Posted In Database - By Tum. On Monday, July 2nd, 2012 With 0 Comments

mysql-logo
วิธีการเปลี่ยน storage engine จาก MyISAM เป็น InnoDB ทีละหลายๆ table สามารถทำได้โดยรันคำสั่ง SELECT CONCAT… เพื่อสร้างคำสั่งในการ convert storage engine จาก MyISAM เป็น InnoDB และเราก็นำผลลัพธ์จากคำสั่งนี้ไปรันต่อที่ MySQL console เพื่อแก้ไข storage engine ได้โดยไม่ต้องพิมพ์ให้เมื่อยและยังครบถ้วนทุก table ที่ต้องการ convert แน่นอน

วิธีที่ #1 – เปลี่ยน storage engine จาก MyISAM เป็น InnoDB
วิธีการนี้เป็นการรันคำสั่งบน MySQL console และ copy ผลลัพธ์ที่ได้มารันทีละคำสั่ง เพื่อที่จะได้รู้ว่ามี table ไหนบ้างที่ไม่สามารถ convert ไปได้และมี error เป็นอย่างไร

คำสั่งที่ใช้แสดง คำสั่งในการแก้ไข storage engine

ผลลัพธ์

[text]
+————————————————————————–+
| CONCAT(‘ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;’) |
+————————————————————————–+
| ALTER TABLE information_schema.COLUMNS engine=InnoDB; |
| ALTER TABLE information_schema.EVENTS engine=InnoDB; |
| ALTER TABLE information_schema.PARAMETERS engine=InnoDB; |

| ALTER TABLE mysql.time_zone_transition engine=InnoDB; |
| ALTER TABLE mysql.time_zone_transition_type engine=InnoDB; |
| ALTER TABLE mysql.user engine=InnoDB; |

| ALTER TABLE DB1.table1 engine=InnoDB; |
| ALTER TABLE DB1.table2 engine=InnoDB; |
| ALTER TABLE DB1.table3 engine=InnoDB; |
| ALTER TABLE DB1.table4 engine=InnoDB; |
| ALTER TABLE DB1.table5 engine=InnoDB; |
| ALTER TABLE DB1.table6 engine=InnoDB; |
+————————————————————————–+
31 rows in set, 1 warning (0.04 sec)
[/text]

หลังจากที่รันคำสั่งแล้วจะได้คำสั่งในการเปลี่ยน storage engine สำหรับ Database ทั้งหมด ให้เราเลือกทุก database ยกเว้น mysql และ information_schema เพราะทั้ง 2 database นี้ไม่รองรับ InnoDB storage engine และรันคำสั่งทั้งหมดใน MySQL console

วิธีที่ #2 – เปลี่ยน storage engine จาก MyISAM เป็น InnoDB
วิธีการนี้เป็นการรันคำสั่งบน MySQL console และเก็บผลลัพธ์เป็น text ไฟล์และใช้คำสั่ง source บน MySQL console เพื่อรันคำสั่งทั้งหมด

หลังจากที่รันคำสั่งข้างบนแล้วจะได้ไฟล์ MyISAM2InnoDB.txt ที่ข้างในมีคำสั่ง alter table อยู่ ให้ลบ คำสั่งที่เกี่ยวข้องกับ mysql และ information_schema database ออก จากนั้นให้เข้า mysql console และรันคำสั่งด้านล่าง เพื่อเปลี่ยน storage engine จาก MyISAM เป็น InnoDB

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*