มีเคสแบบนี้เกิดขึ้นเป็นระยะ เลยขอนำวิธีการจัดการกับไฟล์ ibdata1 ของ InnoDB ที่ขนาดใหญ่เกินทน มาแชร์กันครับ
ถ้าเป็นกรณี MySQL ที่เพิ่งเริ่มใช้งาน และมี default storage engine เป็น InnoDB ให้เช็ค my.cnf ว่ามีบรรทัดต่อไปนี้อยู่หรือไม่ ถ้ามีอยู่ ก็ไม่ต้องทำอะไร เพราะ table แต่ละอันที่สร้างขึ้นหลังจากนี้จะแยกอยู่ใน directory ของใครของมัน ไฟล์ ibdata1 จะมีขนาดไม่ใหญ่มาก
[mysqld]
innodb_file_per_table
ถ้าเป็นกรณี MySQL ที่ใช้งานมาแล้ว และมีไฟล์ ibdata1 ใหญ่มาก มีการรวมข้อมูลใน table ต่างๆ ไว้ที่นั่น แสดงว่าไม่ได้ตั้งค่า innodb_file_per_table เอาไว้ วิธีการแก้ไขเพื่อแยกข้อมูลของแต่ละ db.table ออกมา ให้ทำตามขั้นตอนต่อไปนี้
- เพิ่มเติมลงไปใน my.cnf ค่า innodb_file_per_table นี้ ตามตัวอย่างข้างบน
- ทำการ dump db ออกมาให้หมดทุก db ยกเว้น mysql และ performance_schema ให้เก็บไว้ที่ที่ปลอดภัย
- drop database ต่างๆ ทิ้งให้หมด ยกเว้น 2 db ในข้อ 2
- stop mysql
- ลบ ไฟล์ ibdata1, ib_log… ออก
- start mysql – ไฟล์ที่ถูกลบใน 5. จะถูกสร้างขึ้นมาใหม่ ขนาดไม่ใหญ่นัก ขึ้นกับ config ที่ตั้งไว้
- restore db ต่างๆ จากที่เซฟไว้ ก็จะถูกสร้างเป็นไฟล์เล็กๆ ใน directory ของใครของมันตามที่เราต้องการ
ไม่มีทางอื่นที่จะลดขนาด ibdata1 ยกเว้นที่ลิสต์ไว้ด้านบนครับ หากทำการแก้ไขโดยเพียงแต่ alter table … จะไม่ทำให้ ibdata1 เล็กลง และการ optimize table ก็ยิ่งทำให้ ibdata1 ใหญ่ขึ้นด้วยครับ
ส่วนเทคนิคการ dump/restore db ขนาดใหญ่ ให้เร็วๆ วันหลังมีโอกาสจะแวะมาเขียนอีกครับ
หวังว่าจะมีประโยชน์กับท่านอื่นๆ นะครับ
