วิธีกู้ข้อมูลจากฐานข้อมูล MySQL ประเภท innodb by heha

เรื่องมีอยู่ว่าวันหนึ่ง server cloud ของผมจู่ๆ ก็เกิดอะไรไม่ทราบได้ ขณะที่ server auto optimize table ตาม schedule ที่ cron ตั้งไว้ อยู่ๆ database ก็ corrupt อ่าว… ฉิบหายแล้วไง อาการเป็นยังไง? อาจเป็นไปได้ดังนี้

• ไม่สามารถ alter table หรือ mysqldump ใดๆ กับ table ที่เสียได้ หรือ SELECT * ก็ไม่ได้เช่นกัน ถ้าทำจะปรากฎข้อความประมาณว่า “MySQL server has gone away” หรือ “Lost connection to MySQL server during query“
• ถ้าถึงขั้นเลวร้าย (แบบผม) database จะไม่สามารถเปิดขึ้นมาทำงานได้เลย โดยปรากฎข้อความใน error.log ของ mysql ประมาณว่า (ประมาณว่ามีคำว่า corruption และ start database ขึ้นมากี่ทีๆ ก็ดับทุกครั้ง)
  InnoDB: Database page corruption on disk or a failed
  InnoDB: file read of page 7.
  InnoDB: You may have to recover from a backup.

ซึ่งโดยปกติแล้ว หากใช้ InnoDB จะมีโอกาสที่ database เสียหายฝั่ง software น้อยมาก เพราะ InnoDB มีระบบ auto recover หากเกิดอะไรขึ้นกลางทางให้ ทำให้ recover row ที่อาจ insert ไม่สมบูรณ์ได้โดยอัตโนมัติ แต่หากเป็นความผิดพลาดที่เกิดจาก Hardware แล้วละก็ software เทพแค่ไหนก็ช่วยอะไรไม่ได้ ถ้ายังรัน mysql ได้ ให้หยุดทุกคำสั่งที่จะ write ลงตารางที่มีความเสียหายลงทั้งหมด เพิ่อเพิ่มโอกาสที่จะกู้ข้อมูลได้ให้สูงขึ้น จนกว่าการกู้ข้อมูลจะเสร็จสิ้น ซึ่งในทางปฏิบัติเราสามารถ copy ไฟล์ข้อมูล database ตาม config my.cnf ส่วน datadir ซึ่งเป็นข้อมูลบน physical drive ไปกู้ที่เครื่องอื่นได้เลย (ก่อน copy ต้อง stop service mysql ก่อนนะครับ ไม่งั้นข้อมูลอาจพังได้เช่นกันตอนย้ายไปกู้ที่เครื่องอื่น) โชคดีที่ innodb มีลักษณะการเก็บข้อมูลแบบ platform independent ซึ่งหมายความว่าคุณสามารถ copy ไปใส่ที่อีกเครื่องได้เลยโดยไม่ต้องทำการแปลงข้อมูลใดๆ แม้จะเป็นการ copy ข้าม OS ก็ไม่เป็นไร ต่อไปเป็นวิธีกู้ข้อมูลมีดังนี้

1. กู้โดยวิธีถึก วิธีนี้จะง่ายกว่าวิธีที่สองมาก ถ้าใช้วิธีแรกแล้วไม่พบ row ข้อมูลที่หายไป หรือ row ที่หายไปไม่ใช่ row ที่สำคัญก็แนะนำให้ใช้วิธีนี้ครับ
2. กู้โดยใช้  Percona Data Recovery Tool for InnoDB วิธีนี้ขั้นตอนค่อนข้างยุ่งยากพอสมควรครับ แต่จะมีโอกาสกู้ข้อมูลได้สูงกว่าวิธีแรกมาก และวิธีนี้ยังใช้กู้ข้อมูลที่เผลอลบทิ้งไปเองโดยไม่ได้ตั้งใจได้อีกด้วย!!

ก่อนอื่นขอบอกว่าคำสั่ง CHECK TABLE และ REPAIR TABLE นั้นไม่สามารถช่วยอะไรเราได้หากเราใช้ innodb (ใช้ได้เฉพาะ MYISAM เท่านั้น) เพราะถ้าสั่ง CHECK TABLE กับ innodb มันจะบอกว่า OK ไม่พบปัญหา (อ้าว) สำหรับขั้นตอนกู้ข้อมูลมีดังนี้

วิธีกู้ข้อมูลแบบถึก

1. ดูก่อนว่า mysql ของเรานั้นยังสามารถทำงานได้เป็นปกติดีอยู่หรือเปล่าหลังจากเรารู้ตัวว่ามันพัง วิธีทดสอบก็ง่ายๆ ครับ ลอง restart mysql 1 รอบดูว่า start ได้ไหม ถ้าไม่ได้ให้ edit my.cnf  innodb_force_recovery=1 แล้วค่อยสั่ง start ใหม่หากยัง start ไม่ได้ให้ค่อยๆ ปรับจาก 1 เป็น 2 แล้ว start ใหม่ไล่เพิ่มขึ้นเรื่อยๆ จนกว่าจะได้ ค่าสูงสุดทีสามารถปรับได้คือ 6 ซึ่งจริงๆ ถ้าปรับเพิ่มถึง 4 แล้วยัง start ไมได้ก็ให้เปลี่ยนไปใช้  Percona Data Recovery Tool for InnoDB ได้เลยเพราะมีโอกาสที่ข้อมูลจะสูญหายสูงมาก
2. สร้างตารางใหม่ที่มีโครงสร้างตารางเหมือนตารางที่จะกู้อันเดิมไว้ โดยกำหนด type เป็น MyISAM
3. สมมติตารางที่เราจะกู้ชื่อ user และตารางที่เราสร้างใหม่ชื่อ user_new ให้รัน query ดังนี้
   insert ignore into user_new select * from user limit 0, 10;
   แล้วค่อยๆ เปลี่ยน limit ให้สูงขึ้นเรื่อยๆ (ปรับ offset เพิ่มไปเรื่อยๆ และ length ก็เพิ่มจาก 10 เป็น 1000 หรือ 10000 ได้ตามต้องการ) ไล่ query ไปจนกว่าจะ insert ไมได้ และขึ้นข้อความว่า “ERROR 2013 (HY000): Lost connection to MySQL server during query” ซึ่งหมายความว่าเราพบ row ที่มีข้อมูลเสียหายแล้ว แต่อาจยังอยู่ตรงกลางจะหว่าง limit ที่เรากำหนดเยอะๆ ให้ปรับค่า limit ให้น้อยลงเรื่อยๆ จนกว่าจะสามารถระบุได้ว่าที่ offset เท่าไร limit  query เหลือ 1 แล้วจะยังพังอยู่ ก็แสดงว่าเราพบ row ที่เสียที่แท้จริง ก็ให้ปรับ offset เพิ่อข้าม query ตัวนั้นไป และเพิ่ม limit ให้กว้างขึ้นเช่นเดิม จนกว่าจะพบ row ที่เสียถัดไป ทำเหมือนเดิมไปเรื่อยๆ จนกว่าจะครบทุก row ในตาราง
4. mysqldump table อื่นๆ ที่ไม่ได้เสียหายใดๆ ออกมาให้หมดแล้วเก็บไฟล์ไว้ รวมไปถึงตาราง user_new ที่เราได้กู้ข้อมูลออกมาแล้วไว้ด้วย
5. stop mysql server แล้วลบข้อมูลของ innodb ใน datadir ทั้งหมดทิ้ง เหลือเพียง directory mysql และ performance_schema ไว้
6. start mysql server แล้ว import ข้อมูลที่เรา mysqldump เก็บเอาไว้กลับเข้าไปให้หมด

7. ALTER TABLE user_new ENGINE=InnoDB; เพื่อเปลี่ยน type ตารางกลับเป็น innodb แล้วเปลี่ยนชื่อตารางกลับเป็นชื่อเดิม ทุกอย่างก็จะใช้งานได้ตามเดิม อย่าลืมไปแก้ innodb_force_recovery ใน my.cnf กลับเป็น 0 แล้วไม่อย่างนั้นจะไม่สามารถ insert, update ใดๆ ได้

ที่มา: http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/

วิธีกู้ข้อมูลโดยใช้ Data Recovery Tool for InnoDB

1. download tools ได้ที่  https://launchpad.net/percona-data-recovery-tool-for-innodb ควรเป็นคนละเครื่องกับที่มีข้อมูลตารางที่เสียหาย และไม่ต้อง start mysql เอาไว้
2. untar แล้วเข้าไปที่ directory mysql-source สั่ง ./configure แต่ไม่ต้องสั่ง make
3. กลับไปที่ root directory ของ tools แล้วสั่ง make ขั้นตอนนี้ถ้าคุณเจอ error แนะนำให้ลองเปลี่ยนไป compile ใน OS ตระกูล debian ครับ (เพราะผมลองรันใน debian ผ่าน และ innodb ที่จะนำมากู้สามารถ copy มาจาก OS ไหนก็ได้ตามที่กล่าวไว้ข้างต้น)
4. copy ไฟล์ .ibd ใน datadir ของ mysql ของตารางที่เราต้องการจะกู้มาไว้ในเครื่องนี้ สมมติตารางที่เราจะกู้คือตาราง user เก็บไว้ที่ directory data (จะมีไฟล์นี้ได้ต้อง config รูปแบบการเก็บข้อมูลของ innodb เป็น  innodb_file_per_table=1 แต่แรก ถ้าไม่ได้เซ็ตเอาไว้ให้อ่านวิธีจาก  http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:extracting_the_desired_pages แทน)
5. รัน ./page_parser -5 -f data/user.ibd โดยตรง -5 นี้ต้องดูว่าตารางของเราเป็นชนิด REDUNDANT หรือ COMPACT ซึ่งตั้งแต่ mysql 5.0.3 ขึ้นไป default จะเป็น compact ใช้ -5 ครับ ถ้าเป็น REDUNDANT ให้ใช้ -4
6. จะมี directory ชื่อ pages-xxxxx ถูกสร้างขึ้นที่ dir เดียวกับ tools (xxxxx เป็นตัวเลขใดๆ ที่เกิดจากการสร้าง แต่ละคนจะไม่เหมือนกัน) ให้สั่ง
   ls pages-xxxxx/FIL_PAGE_INDEX/
7. จะพบว่ามี dir ย่อยๆ อยู่มากมายเช่น 0-26, 0-27 (เครื่องคุณจะเป็นชื่ออื่นที่ไม่เหมือนกัน) ซึ่ง dir พวกนี้จะมีจำนวนตาม INDEX ที่เราได้สร้างเอาไว้เราต้องทำการหาว่า dir ไหนที่เก็บ PRIMARY KEY ของเราเอาไว้ ซึ่งนั่นจะเป็น dir เดียวกับที่เก็บข้อมูลของตารางที่เราต้องการจะกู้เอาไว้ด้วย วิธีหา dir ที่เก็บ PRIMARY KEY ที่ง่ายที่สุดคือสั่ง  du -hs ไล่ไปทุกๆ dir ถ้าพบ dir ไหนมีขนาดใหญ่ที่สุดแสดงว่า dir นั้นคือ dir ที่เราต้องการ
8. (ขั้นตอนนี้ถ้าคุณไม่ได้เซ็ต  innodb_file_per_table=1  ไว้ ให้ทำตามวิธีในหน้า http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:extracting_the_data ก่อนจึงค่อยทำต่อ) เข้าไปในเครื่องที่มีฐานข้อมูลตารางเดียวกับที่เราต้องการจะกู้และยังรันได้อยู่ จากนั้น download tools ตัวเดียวกันนี้ไปลงไว้ในเครื่องนั้นด้วย (หรือถ้าเราจะสั่งรันในเครื่องนี้ที่เราจะพยายามกู้ข้อมูลเลยก็ได้ แต่ต้อง start mysql ขึ้นมาก่อน และปิด mysql อีกครั้งหลังทำเสร็จ) แล้วรันคำสั่ง
   ./create_defs.pl –host 127.0.0.1 –port 5520 –user root –password msandbox –db employees –table user > include/table_defs.h
   แก้ข้อมูลเป็นของเครื่องของคุณให้ถูกต้อง แล้วเราจะได้ไฟล์ table_defs.h มาใช้งาน ซึ่งตัวนี้เป็นไฟล์สำหรับเก็บข้อมูลโครงสร้างของตารางในรูปภาษา C เพื่อให้ตัว tools นำไปใช้งานได้ ให้ copy ไปไว้ใน dir include ในเครื่องที่เราจะทำการกู้ข้อมูล
9. สั่ง make ตัว tools ใหม่อีกครั้งเพื่อ compile ใหม่อีกรอบหลังได้ไฟล์ table_defs.h มาแล้ว
10. สั่ง ./constraints_parser -5 -f pages-xxxx/FIL_PAGE_INDEX/0-xx/ > data/user.recovery
    ซึ่ง path ที่สั่งคือ dir ที่เราหาเอาไว้ว่า primary key อยู่ที่ dir ไหนในขั้นตอนที่ 7 นั่นเอง อย่าลืมเปลี่ยน -5 เป็น -4 ด้วยถ้าตารางของคุณเป็นแบบ REDUNDANT และสามารถเพิ่ม option -D ได้ถ้าการกู้ข้อมูลครั้งนี้เป็นการกู้ข้อมูลเฉพาะ row ที่ถูกลบไป (หากต้องการกรองข้อมูลขยะบางส่วนทิ้งไปอ่านเพิ่มเติมได้ที่  http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:fine_tuning_the_table_definition )
11. สร้างตาราง user_new ขึ้นมาเพื่อรองรับข้อมูลที่กู้ได้
12. start mysql server และสั่ง mysql -uroot -p เพื่อเข้าใช้งาน mysql และรัน query
    LOAD DATA INFILE ‘/recovery-tools-dir/data/user.recovery’ REPLACE INTO TABLE `user_new` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘user\t’ (user_id, username, password);
13. เข้าไปยังตาราง user_new เพื่อคัดเลือก row ที่ต้องการได้ตามสะดวกผ่าน phpmyadmin
14. mysqldump table อื่นๆ ที่ไม่ได้เสียหายใดๆ ออกมาให้หมดแล้วเก็บไฟล์ไว้ รวมไปถึงตาราง user_new ที่เราได้กู้ข้อมูลออกมาแล้วไว้ด้วย
15. stop mysql server แล้วลบข้อมูลของ innodb ใน datadir ทั้งหมดทิ้ง เหลือเพียง directory mysql และ performance_schema ไว้
16. start mysql server แล้ว import ข้อมูลที่เรา mysqldump เก็บเอาไว้กลับเข้าไปให้หมด

ที่มา: http://www.mysqlperformanceblog.com/2012/02/20/how-to-recover-deleted-rows-from-an-innodb-tablespace/

ขอให้ทุกคนโชคดีครับ