ตรวจสอบและซ่อมตารางด้วย 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 |
+--------------+--------+--------+

อาการ "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 บนเครื่องจริงระหว่างวัน

ขั้นตอนที่ 5: เมื่อตาราง InnoDB เสียหาย

ถ้า mysqlcheck รายงานว่าตาราง InnoDB เสีย หรือ Error Log มีข้อความเกี่ยวกับ Page เสียหาย (corruption) จนบริการเริ่มไม่ขึ้น --repair จะตอบว่า The storage engine for the table doesn't support repair แนวทางที่ถูกต้องคือ

1. ลองกู้จากไฟล์สำรอง ซึ่งปลอดภัยที่สุด
2. ถ้าบริการยังทำงานได้ ให้ dump ตารางที่เสียออกมาด้วย mysqldump แล้วลบและนำเข้าใหม่ การสร้างตารางใหม่จากข้อมูลที่อ่านได้มักแก้ปัญหาได้ หรือลอง ALTER TABLE ชื่อตาราง ENGINE=InnoDB; ซึ่งสร้างตารางใหม่ในที่เดิม
3. ถ้าบริการเริ่มไม่ขึ้น ใช้ innodb_force_recovery เพื่อให้เริ่มได้ในโหมดอ่านอย่างเดียว แล้ว dump ข้อมูลออกมา

สำหรับข้อ 3 ให้เพิ่มในไฟล์คอนฟิกใต้ [mysqld] โดยเริ่มจากค่า 1

[mysqld]
innodb_force_recovery = 1

รีสตาร์ตบริการ ถ้ายังเริ่มไม่ขึ้นให้เพิ่มทีละ 1 ห้ามข้ามขั้น ค่าตั้งแต่ 4 ขึ้นไปอาจทำให้ไฟล์ข้อมูลเสียหายถาวรเพิ่ม เมื่อเริ่มได้แล้ว dump ข้อมูลทั้งหมดออกมาทันที จากนั้น ลบบรรทัดนี้ออก ก่อนใช้งานตามปกติ ขั้นตอนนี้มีความเสี่ยงสูง ถ้าข้อมูลสำคัญและไม่มีไฟล์สำรอง แนะนำให้ปรึกษาผู้เชี่ยวชาญก่อนลงมือ

ขั้นตอนที่ 6: ตั้งตรวจสอบอัตโนมัติ

สำหรับเครื่องที่ยังมีตาราง MyISAM ตั้ง Cronjob ให้ตรวจและซ่อมอัตโนมัติสัปดาห์ละครั้งในช่วงกลางคืน

sudo crontab -e

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

--silent แสดงเฉพาะตารางที่มีปัญหา ไฟล์ Log จึงว่างถ้าทุกอย่างปกติ ถ้า root ใช้รหัสผ่าน ให้เก็บรหัสผ่านในไฟล์ /root/.my.cnf (สิทธิ์ 600) แทนการใส่ไว้ในคำสั่ง Cron

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

แนวทางเขียน Cron เพิ่มเติม ดู วิธีตั้ง Cronjob หรือ Crontab เพื่อให้ Script ทำงานอัตโนมัติบน Linux ในระยะยาว การแปลงตาราง MyISAM เป็น InnoDB ช่วยลดปัญหาตารางเสียได้มาก แต่ควรทดสอบกับแอปก่อน

ตรวจสอบผลลัพธ์

sudo mysqlcheck legacyapp

ทุกตารางต้องแสดง OK ไม่มี warning หรือ error จากนั้นเปิดเว็บไซต์หรือแอปที่ใช้ตารางนั้น ตรวจว่าข้อมูลแสดงครบ และดู Error Log ของฐานข้อมูลว่าไม่มีข้อความเกี่ยวกับตารางเสียซ้ำ

ปัญหาที่พบบ่อย

ซ่อมแล้ว ไม่นานตารางเสียอีก

ต้นเหตุยังอยู่ เช่น ดิสก์เต็มบ่อย เครื่องถูกบังคับปิด หรือฐานข้อมูลถูก OOM Killer หยุด ตรวจพื้นที่ดิสก์ตาม ดิสก์เต็ม: ตรวจสอบพื้นที่และหาไฟล์ขนาดใหญ่บน Linux และดู sudo journalctl -k | grep -i "out of memory"

Access denied เมื่อรัน mysqlcheck

ผู้ใช้ไม่มีสิทธิ์ หรือ root ใช้รหัสผ่านแต่ไม่ได้ใส่ -p ใช้ sudo mysqlcheck -u root -p ... หรือตั้งไฟล์ /root/.my.cnf ตามขั้นตอนที่ 6

Got error 28 from storage engine

Error 28 คือพื้นที่ดิสก์ไม่พอ การซ่อมและ Optimize ต้องใช้พื้นที่ชั่วคราว ต้องคืนพื้นที่ก่อนแล้วค่อยสั่งใหม่ ตรวจด้วย df -h ทั้งพาร์ติชันข้อมูลและ /tmp

ตารางถูก Lock นานจนเว็บค้าง

การตรวจและซ่อม MyISAM Lock ตารางตลอดการทำงาน ให้ทำในช่วงที่มีผู้ใช้น้อย หรือระบุเฉพาะตารางที่มีปัญหาแทนการสั่งทั้งเซิร์ฟเวอร์

ถ้าตารางยังซ่อมไม่ได้หรือข้อมูลสำคัญหายไป ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/ และหากใช้บริการสำรองข้อมูลทั้งเครื่อง ดู การ Backup Server บน Cloud ของ TDC