วิธีลดขนาดไฟล์ ibdata1 ของ mysql

วิธีลดขนาดไฟล์ ibdata1 ของ mysql

พอดี ผมไปเห็นใน folder ของ mysql พบว่าไฟล์ ibdata1 มีขนาดใหญ่มาก ใหญ่ผิดปกติเลยล่ะ ผมก็เลยหาวิธีลดขนาดของมัน พร้อมทั้งทำความเข้าใจมันด้วย ว่ามันคืออะไร ไม่ใช่ทำมั่วๆ database จะพังเอานะเออ

ไฟล์ ibdata1 นี้ ตามคำกล่าวของ MySQL เค้าบอกเอาไว้ว่า มันเป็นดัวที่ทำหน้าที่หลายอย่าง สำหรับ engine InnoDB (การทำเว็บโดยปกติ และตามบทความโดยทั่วๆไป เค้าจะใช้ engine MyISAM จะมีเฉพาะกรณีที่ต้องการทำ transaction record เท่านั้นเอง จึงละเลือกใช้ InnoDB) ซึ่งคนที่ไม่ได้ใช้งาน InnoDB จะไม่พบปัญหานี้เลย เพราะว่า MyISAM เก็บ database เป็น raw file ใน folder ของใครของมันแบ่งตาม database อยู่แล้ว แต่เจ้า InnoDB เจ้ากรรม มันดันเก็บของทุก database เอาไว้รวมกันไฟล์เดียว และปัญหาก็คือ database ถึงจะถูกลบไปแล้ว แต่ก็ยังมีข้อมูลต่างๆอยู่ใน ibdata1 อยู่ดี นั่นจะทำให้ ibdata1 จะขยายใหญ่ขึ้นไปเรื่อยเมื่อวันเวลาผ่านไป....

สำหรับการลดขนาดไฟล์ ibdata1 เราจะลบออกตรงๆเลยไม่ได้ ถึงแม้ว่า การ restart MySQL มันจะสร้างไฟล์ ibdata1 ใหม่โดยอัตโนมัติก็ตาม แต่หากลบไฟเฉยๆ สิ่งที่จะเจอก็คือ ข้อมูล ที่เก็บใน InnoDB engine หายเกลี้ยงเลย (ทดสอบลบมาแล้ว ถึงได้รู้ชัด โดยผม backup เอาไว้ก่อน) สำหรับขั้นตอนนั้นมีไม่เยอะ ก็จะมีดังนี้

  • ใช้ mysql dump เพื่อ backup ข้อมูลออกมาจากทุก database ก่อน
  • ลบ database ออกทั้งหมด ยกเว้น mysql และ information_schema
  • shut down mysql service
  • ลบไฟล์ ibdata1 และ ib_logfile0 ออก
  • start mysql service ขั้นตอนนี้ มันจะสร้าง ibdata1 มาใหม่ แต่ขนาดเล็กนิดเดียว (เมื่อเทียบกับของเดิม)
  • restore mysql จากไฟล์ .sql เมื่อกี้กลับเข้าไป

เสร็จแล้วก็จะพบว่า ขนาดมันเล็กลงไปเยอะ ยิ่งถ้าใครที่มีข้อมูลเก็บอยู่ในปัจจุบันน้อยๆ ขนาดก็ยิ่งเล็กลงไปอีก อย่างของเครื่องผมเอง ก่อนทำ 1.64GB ซึ่งมั่นใจเหลือเกินว่าผมไม่มีข้อมูลเยอะขนาดนั้นอย่างแน่นอน หลังทำเสร็จ เหลือ 18MB เท่านั้น สาเหตุที่มันเยอะ เพราะว่าก่อนนี้เอา database ขนาด 1GB มา restore แล้วทดสอบใช้งาน จากนั้นก็ลบไป เลยเป็นต้นเหตุของขนาดที่ใหญ่โต

สำหรับ mysqldump ผมเองใช้ xampp ในรูปแบบ portable บน windows ดังนั้นเวลาใช้งาน ก็ใช้คำสั่ง mysqldump.exe --all-database --user=username --password=password > path/to/sqlfile.sql โดยต้องเข้าไปใน path ที่เก็บโปรแกรม mysqldump.exe ก่อนนะครับ ควรจะอยู่ใน path/mysql/bin/ ครับ

Create: Modify : 2013-01-23 14:42:42 Read : 4044 URL :