How to convert all MyISAM tables to InnoDB in a MySQL Database
วิธีการเปลี่ยน 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
|
1 2 3 |
SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM'; |
ผลลัพธ์
[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 เพื่อรันคำสั่งทั้งหมด
|
1 2 3 |
SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',table_name,'` engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' into outfile '/tmp/MyISAM2InnoDB.txt'; |
หลังจากที่รันคำสั่งข้างบนแล้วจะได้ไฟล์ MyISAM2InnoDB.txt ที่ข้างในมีคำสั่ง alter table อยู่ ให้ลบ คำสั่งที่เกี่ยวข้องกับ mysql และ information_schema database ออก จากนั้นให้เข้า mysql console และรันคำสั่งด้านล่าง เพื่อเปลี่ยน storage engine จาก MyISAM เป็น InnoDB
|
1 2 3 |
source /tmp/MyISAM2InnoDB.txt; |