InnoDB Storage Engine Data Recovery สำหรับ MySQL database

ปัญหาของการลืม password ของ root ของ MySQL ไม่ได้เป็นปัญหาเดียวที่ผมเจอ ในเรื่องของการใช้งาน MySQL Database Server ถึงแม้จะไม่บ่อยนัก แต่ก็บ่อยมากพอที่จะทำให้ ใน 2-3 ครั้งให้หลัง ผม “จำ” วิธีการแก้ปัญหาได้ โดยไม่ต้องพึ่งพา google อีกต่อไป

ถ้าจะว่าไป ถ้าปัญหานี้ เกิดขึ้น “บ่อย” กว่าเดิมอีกซักหน่อย ผมก็คง “จำ” root password ได้ (เพราะต้องใช้บ่อยขึ้น) และส่งผลในทางกลับ ทำให้ ไม่ลืม root password และทำให้ปัญหานี้ไม่ใช่ปัญหาอีกต่อไป — ฟังดูย้อนแย้ง ชวนขยักขย่อน หรือเปล่าครับ 🙂

แต่ไม่ว่าอย่างไร … การลืม password ในระดับสร้างความหงุดหงิดรำคาญใจแค่นั้นเอง ความรู้สึกอย่างมากก็คือ “ลืมอีกแล้ว ต้องรีเซ็ทพาสเวิร์ดอีกรอบ งี่เง่าจริงๆ!”  แต่มีปัญหาอีกอย่างที่ … ไม่ได้เกิดขึ้นบ่อย … นานๆเกิดที แต่พอเกิดขึ้นมาแล้ว ทำให้ระดับความเครียดของ sysadmin พุ่งขึ้นสูงอย่างฉับพลัน

ปัญหาแบบที่ว่า เกิดขึ้น ในลักษณะนี้ ครับ นี่คือข้อความทีี่มีอยู่ในไฟล์ “/var/log/mysql/error.log”

InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7 3581528990
InnoDB: Error: page 4 log sequence number 7 3581552218
InnoDB: is in the future! Current system log sequence number 7 3581528990.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.

ก่อนที่จะมาถึงที่จุดนี้ อาการของตัว mysql server ก็คือว่า load ของ mysqld ตัว daemon ทำงานอยู่แกว่งอยู่ในช่วง 90-100% อยู่ตลอดเวลา หลังจาก boot เครื่องขึ้นมา และถึงแม้จะไม่มีการใช้งาน mysql server จาก process อื่นๆเลย (ผมทดสอบโดยการ shutdown ตัว web server ซึ่งเป็น client เพียงตัวเดียวที่ใช้งาน mysql server อยู่) ก็ไม่ได้ทำให้ load ของตัว mysqld ลดลง

หลังจากลองตรวจสอบจาก system log ของระบบ จนไปเจอข้อความข้างบนใน error log ของ mysql เองก็เลยรู้ว่าปัญหาอยู่ที่ ไฟล์ที่บันทึกข้อมูล/transaction ของ MySQL มีปัญหา

ปัญหานั้นมาจากใหน? ปัญหาเกิดขึ้นจากข้อมูลของ MySQL database ที่ผมได้มานั้น ได้มาจากการ copy (อันที่จริง rsync — แต่นั่นไม่ใช่ประเด็น)  ไฟล์ข้อมูลของ MySQL database ในขณะที่ตัว database server ยังเปิดใช้งานอยู่

เมื่อมาคิดอีกทีในตอนหลัง วิธีการที่ดีกว่าสำหรับการ copy database มาครั้งนั้น สิ่งที่ควรจะทำก็คือการ dump database โดยใช้คำสั่ง mysqldump แล้วเอาไฟล์ sql ที่ได้จากการ dump อันนั้นมา import กลับเข้า  database อีกทีในตอนหลัง

แต่จะว่าไปชีวิตของ admin ก็ไม่ง่ายอย่างนั้น ข้อแรกการที่ผมจะ dump ข้อมูลจาก database server ออกมาทั้งหมดได้ ผมจะต้องรู้ root password ของ database server อันนั้น … ถึงแม้ผมจะสามารถใช้สิทธิของ root บนระบบนั้นๆได้ แต่ … ผมไม่รู้ทั้ง root password ของ system และ ไม่รู้ root password ของ mysql ด้วยเหมือนกัน … อ้าว งั้นก็ reset root password ของ mysql เสียก่อนสิ โดยใช้วิธีการที่ผมได้อธิบายไปแล้ว ในบันทึกที่แล้ว … จะมีปัญหาอะไรล่ะ? มีครับ ระบบที่ว่ามีการใช้งานอยู่ตลอดเวลา database server เป็นองค์ประกอบสำคัญ ในขณะนั้นผมไมแน่ใจว่า การที่จะ shutdown และ restart mysql server จะส่งผลกระทบต่อการทำงานของส่วนอื่นๆของระบบอย่างไรบ้าง ถ้า restart ตัว database server แล้ว การทำงานส่วนอื่นๆที่เหลือ จะกลับมาทำงานได้ตามปกติหรือเปล่า? หรือผมจะต้อง restart สซอฟต์วแร์ส่วนอื่นๆที่เกี่ยวข้องด้วย? … ผมไม่มีคำตอบในขณะนั้น และไม่อยากจะเสี่ยงหาคำตอบบนระบบที่มีคนใช้งานอยู่จริง (แบบว่ากล้วน่ะครับ ทำงานเป็น admin มาหลายปี ก็ยังทำใจไม่ได้ครับ)

ผมมี database อยู่ในมือแล้ว มันมีปัญหา ก็ซ่อมมันสิ … สมมติว่า database ตัวที่ใช้งานจริงมีปัญหาแบบเดียวกัน จะทำยังไง? ก็ต้องหาวิธีการแก้ไขแบบเดียวกันใช่ใหม?

เอ่อ … อันที่จริง วิธีการที่ดีกว่า สำหรับ ระบบที่ใช้งานอยู่จริงๆ ก็คือ “ป้องกัน” และ “สำรอง” ครับ แต่ในขณะที่ผมยังไม่มี ระบบที่ว่าอยู่ในมือ ก็สมมติเอาก่อนแล้วกันว่า ผมเจอปัญหากับ database พัง แล้วต้องหาวิธีการกู้ database ให้กลับมาใช้งานให้ได้ …

โอเค, งั้นก็ได้ เริ่มกันเลย ใน error message ของ mysql เองก็มี URL ให้อยู่แล้ว ดีกว่าระบบอื่นๆอีก 2-3 ระบบที่ผมเคยเจอมา นอกเหนือจากจะไม่มีตัวช่วย ที่จะชี้ว่าจะไปหาข้อมูลเพิ่ม เพื่อแก้ปัญหาได้ที่ใหน ข้อมูลเกีี่ยวกับ error ที่แสดงออกมายัง มากเกินความจำเป็น ข้อมูลที่สำคัญที่เกี่ยวกับข้อผิดพลาด ถูกซ่อนเอาไว้ในกองของข้อมูลอื่นที่ไม่เกี่ยวข้อง ที่นอกจากจะไม่ช่วยในการแก้ปัญหาแล้ว ยังช่วยให้หลงทางอีกต่างหาก

นี่, มี URL ให้แล้ว http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html ก็เปิด web browser ขึ้นมาอ่านเลยสิ

ถ้าคุณอ่านมาจนถึงบรรทัดนี้แล้ว และจะลอง ตัด/แปะ URL อันที่ว่านี่ใน web browser ดูนะครับ คุณก็จะเจอว่า

…

แป่ว … Page Not Found …

อันนี้ ไม่ใช่ว่า MySQL version 5.1 มันจะเก่าจนเกินไปนะครับ เพราะถ้าลองหาอย่างอื่นดู ในส่วนของ reference/manual ก็จะหาเจอ แต่ว่า ส่วนของ forcing-recovery สำหรับ InnoDB Engine อันที่จริงแล้ว อยู่ที่นี่ครับ

http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

มี bug อยู่ใน error message ของ MySQL ครับ

แต่ถึงกระนั้น หลังจากลองทำตามดูตามคำแนะนำ ใน page ที่ว่านี่แล้ว (ปรับค่า innodb_force_recovery ขึ้นไปเรื่อยๆ แล้ว restart mysql ดู) ก็ยังมีปัญหาอย่างเดิม ยังไม่สามารถแก้ปัญหาได้

อาจจะเป็นกรณีที่โชคยังดีอยู่บ้าง ตรงที่ว่า ถึงแม้จะมี error เกิดขึ้นแต่ตัว server ก็ยังทำงานได้ และรับการติดต่อจากตัว mysql client แสดงว่าส่วนของฐานข้อมูลที่มีปัญหา “น่าจะ” (ผมเดาเอา) ยังไม่ร้ายแรงมาก เมื่อลอง query ข้อมูลดูบางส่วนก็ปรากฏว่ายังใช้งานได้ หลังจากลองทำตามคำแนะนำใน mysql document เองแล้วงยังไม่ work หาอ่านคำแนะนำจากที่อื่น มีคำแนะนำให้ dump database ออกมา แล้วค่อยใส่กลับเข้าไปใหม่ ผมก็เลยลองดู …

ปรากฏว่า วิธีการนี้สามารถใช้งานได้ มีขั้นตอนที่ผมลองผิดลองถูกเพิ่มขึ้นอีกหน่อยนึง ถึงจะทำให้ database กลับมาใช้งานได้ ก็เลยคิดว่าจะเอามาบันทึกไว้ที่นี่

โดยสรุป: ผมยังไม่รู้วิธีการที่จะแก้ปัญหาทั้งหมดนะครับ สมมติว่า ถ้า database เกิดพังมากกว่านี้ วิธีการนี้ก็อาจจะใช้งานไม่ได้ (ผมเคยลองพัง database ที่ใช้ InnoDB engine แล้ว ปรากฏว่า mysqld ไม่สามารถ start ขึ้นมาทำงานได้ … ซึ่งถ้าเป้นกรณีนั้น วิธีการที่ผมจะพูดถึงก็ไม่มีประโยชน์อะไรเหมือนกัน) แต่ถ้ามันอาการแย่กว่า แบบที่ document ของ MySQL แนะนำเอาไว้แล้ว ทำตามแล้วยังมีปัญหาอยู่ วิธีการนี้ก็อาจจะมีประโยชน์อยู่บ้างในบางระดับ … ผมหวังเอาไว้อย่างนั้นนะครับ

โดยหลักแล้วก็คือ การ dump database ออกมาเป็น sql file แล้วก็ import กลับเข้าไปแค่นั้นเองครับ … ในกรณีนี้ ผมยังไม่รู้ root password ของ mysql และ ผมจะเปลี่ยนมันทหลัง แต่ในตอนนี้ผมต้องการให้ database ทั้งหมดถูกต้อง และ mysql server กลับมาทำงานได้ โดย load ไม่ได้อยู่ที่ 90-100% ตลอดเวลาก่อน

ขั้นตอน

$ sudo service mysql stop
$ sudo /usr/sbin/mysqld --skip-grant-tables &
$ MYPID=$!
$ mysqldump --all-databases > all.sql
$ sudo kill -QUIT $MYPID

เพื่อที่จะให้สามารถ dump database ได้ โดยที่ผมไม่รู้ root password ผมก็ต้องให้ mysql ทำงานใน mode ที่ไม่ตรวจสอบ privileges โดยการใช้ option “–skip-grant-tables” ซึ่งหลังจากนั้น ก็จะทำให้ผมใช้คำสั่ง mysqldump สำหรับ dump database ได้ เมื่อใช้ option –all-databases ก็จะเป็นการ dump ทุกๆ database ที่มีอยู่ และเก็บลงในไฟล์ชื่อ all.sql

หลังจาก dump เสร็จก็ terminate mysqld process โดยการส่ง SIGQUIT signal ให้กับ process ของ mysqld

หลังจากนี้ เพื่อที่จะเก็บ ข้อมูลเดิมทั้งหมดเอาไว้ก่อน ผมก็จะย้าย directory ที่เก็บข้อมูลของ mysql ไปเก็บไว้ในชื่อ mysql.0 และสร้าง directory สำหรับ mysql ขึ้นมาใหม่ โดยการใช้คำสั่งต่อไปนี้ครับ

$ sudo mv /var/lib/mysql /var/lib/mysql.0
$ sudo mkdir /var/lib/mysql
$ sudo chown mysql:mysql /var/lib/mysql
$ sudo chmod 700 /var/lib/mysql

หลังจากนั้นก็จะเป็นการ import ข้อมูลของ mysql กลับเข้าไป ซึ่งก็ต้อง start mysql server กลับขึ้นมาอีกรอบ และให้มันสร้างไฟล์อื่นๆที่จำเป็นใน mysql directory

$ sudo /usr/sbin/mysqld --skip-grant-tables &
$ MYPID=$!
$ mysql < all.sql
$ sudo kill -QUIT $MYPID

ถึงขั้นตอนนี้ database ที่เรา dump ออกมาในตอนแรกก็ถูก import กลับเขาไปใน database อีกครั้ง รวมทั้ง privileges และ password ทั้งหมดด้วย ซึ่งเราสามารถใช้คำสั่ง

$ sudo service mysql start

เพื่อให้ database กลับมาทำงานตามปกติ

ซึ่งในกรณีของผม ตัว database สามารถกลับมาทำงานได้ load ของ mysqld กลับเข้าสู่สภาวะปกติ (เข้าใกล้ 0 เมื่อไม่มีการใช้งาน) ไม่มีข้อความแสดง error ในไฟล์ /var/log/mysql/error.log จะมีแต่ข้อความแสดงการทำงานตามปกติ) และตัว application ที่ใช้งาน database ใช้งานได้อย่างถูกต้อง

ผมอาจจะแค่โชคดี สำหรับการซ่อมแซม database ที่พังเสียหายในกรณีนี้ แต่คิดว่าบันทึกในสิ่งที่ทำไว้ ก็อาจจะมีประโยชน์สำหรับคนที่อาจจะเจอเหตุการณ์แบบเดียวกันครับ