ทำไมไฟล์ ibdata1 ของ mysql ถึงมีขนาดใหญ่และวิธีแก้ไข

May 6, 2015 11:53 am Freeware, Work

ทำไมถึงมีไฟล์ ibdata1

เป็นเพราะ engine ของระบบ InnoDB ของ MySQL นั่นได้สร้างขึ้นมาเอง โดย engine InnoDB นั่นจะเป็น engine ที่เก็บ transaction logs ในการใช้งาน พูดง่ายๆก็คือเก็บรายละเอียดทุกอย่างในการใช้งาน Database เช่น เพิ่ม/ลบ/แก้ไข ซึ่งมันเป็น engine default ที่ MySQL เลือกมาให้
20150424102837

ที่นี้ที่ผมเจอคือมันใหญ่โตมโหฬาร ขนาดใหญ่ถึง 33 gb ซึ่งไม่รู้มันเก็บ transaction อะไรนักหนา แต่มันทำให้ Disk เต็มเลยทีเดียว!!
แล้วอยู่ๆจะไปลบซุ่มสี่สุ่มห้าก็ไม่ได้ เพราะมันจะทำให้ Database พังเลย ลองหาข้อมูลดูพบว่ามันสามารถแก้ไขได้

20150423142030

ลบไฟล์ ibdata1 อย่างถูกวิธี

อย่างที่บอกมันไม่สามารถลบไฟล์ ibdata1 ตรงๆได้เนื่องจากมันเก็บ transaction logs ของ Database อยู่ ทางเดียวที่จะลบได้คือต้อง Drop Database ทั้งหมดไปเลย
1. เริ่มจาก export Database ออกมาทั้งหมดก่อน จะใช้ command หรือ phpmyadmin ก็แล้วแต่ความสะดวก
2. จากนั้นก็ Drop Database ทั้งหมด ยกเว้น mysql และ information_schema
3. service mysqld stop
4. ลบไฟล์ ibdata1 และ ib_logfile0 ออก

แต่ก่อนที่เราจะ start MySQL มาอีกครั้ง ลองมาเปลี่ยน default engine ให้ใช้งาน MyISAM ดีกว่าเพราะ MyISAM ไม่ทำการเก็บ transaction logs โดยทำการแก้ไขไฟล์ /etc/my.cnf โดยเพิ่ม ” default-storage-engine = MyISAM ” เข้าไปในส่วนของ [mysqld]

5. service mysqld start

เปลี่ยน engine มาเป็น MySAIM แล้ว
20150424103213

แล้วก็ Import ข้อมูลที่ Export เข้ามาใน MySQL Database เป็นอันเรียบร้อย
20150427104642

มาถึงตรงนี้ หากเราจะใช้งาน engine เป็น InnoDB ไปเลยก็ได้ แต่ต้องคอย monitor ขนาดของ ibdata1 เรื่อยๆ เพราะขนาดมันจะใหญ่ขึ้นทุกครั้งที่มีการใช้งาน database

แต่หากเราลองเปลี่ยน engine ก่อนที่จะ Import เขา MySQL ด้วยการเปิดไฟล์ .sql ด้วย notepad++ แล้วเปลี่ยนจาก ENGINE=InnoDB เป็น ENGINE=MyISAM แล้วก็ save จากนั้นค่อย Import เข้าไปใน MySQL พอดูใน phpmyadmin พบว่า Engine เปลี่ยนเป็น MyISAM แล้ว เมื่อทำการ เพิ่ม/ลบ/แก้ไข database ขนาดของไฟล์ ibdata1 ก็ไม่เพิ่มขึ้นอีกเลย

20150424105405

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.