ตรวจสอบและซ่อมตารางด้วย mysqlcheck

ตารางในฐานข้อมูล MySQL หรือ MariaDB อาจเสียหายได้เมื่อเครื่องดับกะทันหัน ดิสก์เต็มระหว่างเขียนข้อมูล หรือโปรเซสฐานข้อมูลถูกหยุดแบบไม่ปกติ อาการที่พบบ่อยคือข้อความ Table './wordpress/wp_options' is marked as crashed and should be repaired หน้าเว็บบางส่วนหายไป หรือ Query บางตารางได้ Error แทนผลลัพธ์

mysqlcheck เป็นเครื่องมือ Command Line ที่มากับ MySQL และ MariaDB ใช้ตรวจสอบ (CHECK), ซ่อม (REPAIR), วิเคราะห์สถิติ (ANALYZE) และจัดระเบียบ (OPTIMIZE) ตารางได้ทั้งทีละตารางหรือทั้งเซิร์ฟเวอร์ บทความนี้อธิบายการใช้งาน และที่สำคัญคือข้อจำกัดของมันกับตาราง InnoDB ซึ่งผู้ใช้จำนวนมากเข้าใจผิด

สิ่งที่ต้องเตรียม

• สิทธิ์ root บนเซิร์ฟเวอร์ หรือผู้ใช้ฐานข้อมูลที่มีสิทธิ์ SELECT และ INSERT บนตารางที่จะซ่อม
• MySQL 8.0/8.4 หรือ MariaDB 10.x/11.x (บน MariaDB ชื่อคำสั่งใหม่คือ mariadb-check และยังมี mysqlcheck ให้ใช้ในแพ็กเกจส่วนใหญ่)
• ไฟล์สำรองล่าสุดของฐานข้อมูล ก่อนสั่งซ่อมทุกครั้ง ดู สำรองและกู้คืนฐานข้อมูล MySQL/MariaDB ด้วย mysqldump
• พื้นที่ดิสก์ว่างมากกว่าขนาดของตารางที่ใหญ่ที่สุด เพราะการซ่อมและ Optimize สร้างสำเนาของตารางชั่วคราว

หมายเหตุ: การซ่อมตารางที่เสียหายอาจทำให้แถวที่อ่านไม่ได้หายไปอย่างถาวร สำรองข้อมูลก่อนเสมอ ถ้าสำรองด้วย mysqldump ไม่ผ่านเพราะตารางเสีย ให้คัดลอกไฟล์ในโฟลเดอร์ข้อมูลของฐานข้อมูลนั้นเก็บไว้ขณะหยุดบริการ

ขั้นตอนที่ 1: ดูว่าตารางใช้ Engine อะไร

วิธีซ่อมขึ้นกับ Storage Engine ของตาราง ตรวจก่อนด้วย

sudo mysql -e "SELECT table_schema, engine, COUNT(*) AS tables
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY table_schema, engine;"

+--------------+--------+--------+
| table_schema | engine | tables |
+--------------+--------+--------+
| wordpress    | InnoDB |     12 |
| legacyapp    | MyISAM |     31 |
+--------------+--------+--------+

Engine	CHECK	REPAIR	OPTIMIZE
MyISAM	ได้	ได้	ได้
Aria (MariaDB)	ได้	ได้	ได้
InnoDB	ได้	ไม่รองรับ	ทำโดยสร้างตารางใหม่ (recreate)

อาการ "marked as crashed" เกิดกับ MyISAM และ Aria เป็นหลัก ส่วน InnoDB มีระบบกู้คืนตัวเองอัตโนมัติ (Crash Recovery) จาก Redo Log ทุกครั้งที่เริ่มบริการ จึงไม่ค่อยเสียหายแบบเดียวกัน และเมื่อเสียจริงต้องใช้วิธีอื่น (ดูขั้นตอนที่ 5)

ขั้นตอนที่ 2: ตรวจสอบตาราง

ตรวจทุกตารางในทุกฐานข้อมูล (--check เป็นค่าเริ่มต้นจึงไม่ต้องใส่)

sudo mysqlcheck --all-databases

บน Ubuntu ที่ root ใช้การยืนยันตัวตนผ่าน Socket คำสั่งนี้ทำงานได้ทันที ถ้า root ใช้รหัสผ่าน ให้เพิ่ม -u root -p ตรวจเฉพาะฐานข้อมูลเดียว หรือเฉพาะบางตาราง

sudo mysqlcheck wordpress
sudo mysqlcheck wordpress wp_options wp_posts

ผลลัพธ์ปกติแสดง OK ท้ายชื่อตาราง

wordpress.wp_options                               OK
wordpress.wp_posts                                 OK
legacyapp.orders
warning  : Table is marked as crashed
error    : Size of datafile is: 4194304         Should be: 4194816
error    : Corrupt

ตาราง legacyapp.orders ในตัวอย่างนี้เสียหายและต้องซ่อม การตรวจทั้งเซิร์ฟเวอร์ใช้ I/O สูงและ Lock ตาราง MyISAM ระหว่างตรวจ ควรทำในช่วงที่มีผู้ใช้น้อย ถ้าต้องการตรวจแบบเร็ว ใช้ --fast (ตรวจเฉพาะตารางที่ปิดไม่เรียบร้อย) หรือ --medium-check

ขั้นตอนที่ 3: ซ่อมตาราง MyISAM และ Aria

ซ่อมเฉพาะตารางที่เสีย

sudo mysqlcheck --repair legacyapp orders

หรือให้ตรวจแล้วซ่อมอัตโนมัติเฉพาะตารางที่พบปัญหา

sudo mysqlcheck --auto-repair legacyapp

ผลลัพธ์ที่สำเร็จ

legacyapp.orders
warning  : Number of rows changed from 10522 to 10519
status   : OK

บรรทัด Number of rows changed บอกว่ามีแถวที่กู้ไม่ได้และถูกตัดทิ้ง ตัวอย่างนี้หายไป 3 แถว ให้ตรวจข้อมูลสำคัญเทียบกับไฟล์สำรอง ถ้าการซ่อมปกติไม่สำเร็จ ลองแบบละเอียดขึ้นด้วย --extended ซึ่งช้ากว่ามากสำหรับตารางใหญ่

sudo mysqlcheck --repair --extended legacyapp orders

ขั้นตอนที่ 4: ANALYZE และ OPTIMIZE

--analyze ปรับปรุงสถิติการกระจายของ Index ช่วยให้ตัววางแผน Query เลือก Index ได้ถูก ใช้ทรัพยากรน้อย เหมาะหลังจากนำเข้าหรือลบข้อมูลจำนวนมาก

sudo mysqlcheck --analyze wordpress

--optimize จัดเรียงข้อมูลใหม่และคืนพื้นที่ว่างที่เกิดจากการลบแถวจำนวนมาก สำหรับ InnoDB จะเห็นข้อความนี้ ซึ่ง เป็นเรื่องปกติ ไม่ใช่ Error

sudo mysqlcheck --optimize wordpress wp_postmeta

wordpress.wp_postmeta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

การ Optimize ตาราง InnoDB คือการสร้างตารางใหม่ทั้งตาราง ใช้เวลาและพื้นที่ดิสก์เท่าขนาดตาราง ไม่จำเป็นต้องทำเป็นประจำ ทำเฉพาะเมื่อลบข้อมูลไปจำนวนมากและต้องการคืนพื้นที่ดิสก์ ห้ามรวม --optimize กับ --all-databases บนเครื่องจริงระหว่างวัน

[mysqld]
innodb_force_recovery = 1

sudo crontab -e

0 4 * * 0 /usr/bin/mysqlcheck --all-databases --auto-repair --silent >> /var/log/mysqlcheck.log 2>&1

[client]
user=root
password=รหัสผ่าน

sudo mysqlcheck legacyapp