1. mkdir /var/lib/mysql2/ ขึ้นมา หรืออาจ copy จาก db ปัจจุบันก็ได้ (ถ้าจะ copy ต้องปิด mysql ก่อน copy) หรือถ้าจะรันจาก backup ตรงๆ อย่างผมก็ไม่ต้องทำอะไร
2. chown -R mysql:mysql /path/to/ข้อ 1
3. copy my.cnf ที่ใช้รัน ตั้งชื่อใหม่เป็น my2.cnf (หรือถ้า debian ก็ทั้ง /etc/mysql เป็น /etc/mysql2)
4. edit my2.cnf ที่ copy มาดังนี้
   - port – แก้ 3306 เป็น 3307 (หรืออื่นๆ ที่ไม่ได้ใช้งาน)
   - datadir – path ที่เราสร้างในข้อ 1
   - socket – เปลี่ยนชื่อ mysql.sock เป็น mysql2.sock
   - innodb_data_home_dir - path ที่เราสร้างในข้อ 1
   -  innodb_log_group_home_dir – path ที่เราสร้างในข้อ 1
   -  innodb_buffer_pool_size – อันนี้จริงๆ ไม่ต้องแก้ก็ได้ แต่ถ้าเครื่อง ram น้อย แล้วไฟล์ my.cnf ต้นฉบับตั้งค่าไว้เยอะก็ลดลงไปหน่อยไม่ให้ล้น ram ที่มี
5. mysql_install_db –user=mysql –datadir=/path/to/ข้อ 1
6. mysqld_safe –defaults-file=/path/to/my2.cnf &
7. mysql -S /path/to/mysql2.sock -uroot -p
   เพื่อทดสอบการเชื่อมต่อ ถ้า login ได้แสดงว่าทำสำเร็จ แล้วใน config phpmyadmin (config.inc.php) ก็อาจไปเพิ่มบรรทัดนี้
   $cfg['Servers'][$i]['port'] = ’3307′;
   ก็จะเข้าไปดูข้อมูลหรือ export ข้อมูลแบบใช้ GUI ได้ตามสะดวกโยธิน
8. mysqladmin -S /path/to/mysqld2.sock shutdown เพื่อปิด instance เวลาเลิกใช้งาน

เปิด mysql ได้สอง Instance แล้วเรียบร้อย จะ copy ข้อมูลข้ามกันไปมายังไงก็ได้แล้ว ง่ายแค่นี้เอง

• ต้องแก้ Code ใหม่เนื่องจากไม่สามารถใช้ SQL query ได้อีกต่อไป ต้องใช้ function ที่ตัวเชื่อมกับ HandlerSocket มีให้เท่านั้น
• Join ไม่ได้เพราะเป็นการใช้งานแบบ NoSQL เต็มรูปแบบ ใช้ได้เฉพาะ Query ง่ายๆ ของ SELECT,INSERT, UPDATE, DELETE ที่อ้างอิงกับ primary key
• Replication ไม่ได้ถ้าใช้เป็นรูปแบบการ write (แก้ปัญหาได้ด้วยการใช้แค่ read อย่างเดียว write ยังใช้เป็น SQL เหมือนเดิม)
• ไม่มีระบบ Autenticatation ไม่มี Security ใดๆ (ต้องเข้าใจก่อนว่า NoSQL เจ้าอื่นๆ เช่น Redis, Memcache ก็เป็นแบบนี้เช่นกัน) แต่สามารถใช้ Firewall block port ที่จะใช้งานจากภายนอกแทนได้
• ควรปิดระบบ query cache เวลาใช้งานไม่งั้นค่าที่อ่านมาได้อาจผิดพลาด (แต่เนื่องจากมันเร็วส์อยู่แล้ว ปิด query cache ก็ไมได้กระทบกับระบบอะไรมากหรอก)
• ถ้า insert รัวๆ Auto Increment จะนับข้ามบ้างเป็นบางครั้ง ถ้าไม่ serious ว่าเลขต้องเรียงต่อกันก็ไม่เป็นไรครับ
• ใช้ได้กับ InnoDB Storage Engine เท่านั้น!

มาดูที่ข้อดีกันบ้าง

• เร็วส์ (750,000 query/sec แต่อันนี้เป็นคำโม้จากคนสร้างนะครับ ใช้จริงอาจไม่ถึง แต่เร็วกว่า memcache ถ้า memory พอดีกับข้อมูลครับ)
• INSERT, UPDATE ตัว HandlerSocket จะจับมัดเป็นก้อนเดียวกันโยนไปทีเดียวเลยเหมือนใช้ Transaction ในตัว (เฉพาะ performance นะครับ ไม่ได้ใช้ transaction ได้เต็มรูปแบบ) จริงๆ ก็คือ เร็วส์แหละครับ
• ข้อมูลยังคงออกมาเป็นรูปแบบของตาราง MySQL ที่เราคุ้นเคย เรายังใช้ SQL มาดึงข้อมูลภายหลังได้ หากเกิดเปลี่ยนใจไม่อยากใช้ HandlerSocket แล้ว
• เร็วส์ เร็วส์ เร็วส์ (ไม่มีข้อดีอย่างอื่นแล้ว)

ถ้าพิจารณาข้อจำกัดต่างๆ แล้ว ทุกอย่างผ่านหมด ก็มาดูที่วิธีติดตั้งได้เลย

1. install percona server ก่อนเลยครับ ถ้าไม่ใช้ Percona Server ก็ต้อง compile plugin ใช้เองซึ่งวุ่นวายกว่ามากครับ วิธี Install ตามนี้เลย

- apt-get
- yum

2. แก้ไขไฟล์ my.cnf เพิ่มบรรทัดเหล่านี้เข้าไป


loose_handlersocket_port = 9998
# the port number to bind to for read requests
loose_handlersocket_port_wr = 9999
# the port number to bind to for write requests
loose_handlersocket_threads = 16
# the number of worker threads for read requests
loose_handlersocket_threads_wr = 1
# the number of worker threads for write requests
open_files_limit = 65535
# to allow handlersocket to accept many concurrent
# connections, make open_files_limit as large as
# possible.


9998 คือ port สำหรับ read อย่างเดียว ส่วน 9999 คือ port สำหรับ write นะครับ (แก้เลขเป็น port อื่นได้)

3. login เข้า MySQL เป็น root แล้วพิมพ์คำสั่งไปว่า

install plugin handlersocket soname 'handlersocket.so';

4. สั่ง SHOW PROCESS LIST ถ้า Install สำเร็จจะปรากฏ Worker ใน process list ดังนี้


mysql> SHOW PROCESSLIST;
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+---------------+---------+------+-------------------------------------------+------------------+
| 1 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL |
| 2 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL |
...
| 16 | system user | connecting host | NULL | Connect | NULL | handlersocket: mode=rd, 0 conns, 0 active | NULL |
| 17 | system user | connecting host | handlersocket | Connect | NULL | handlersocket: mode=wr, 0 conns, 0 active | NULL |


5. เสร็จสิ้น หลังจากนี้ก็ต้องหา library ที่สามารถติดต่อกับ HandlerSocket มาใช้ละครับ เช่น php ใช้ php-handlersocket nodejs ใช้ node-handlersocket

ก่อนอื่นต้องอธิบายก่อนว่า Percona Server คือ MySQL เวอร์ชั่นปรับปรุงนั่นเอง โดยทางทีมพัฒนาได้นำเอา InnoDB Engine ไปพัฒนาและปรับปรุงประสิทธิภาพหลายๆ อย่าง และใส่ฟีเจอร์เด็ดๆ เพิ่มเข้ามามากมายจนสุดท้ายออกมาเป็น Percona Server ซึ่งเจ้านี่มีความเข้ากันได้กับ InnoDB Engine ตัวเดิมของ MySQL 100% ครับ ใช้แทน MySQL ได้ทุกประการ รวมไปถึง Tools ต่างๆ ที่เคยใช้กับ MySQL ได้ก็จะใช้กับ Percona Server ได้เช่นกัน (แม้จะเป็น MyISAM ก็สามารถใช้งานได้ปกติไม่มีปัญหาใดๆ ครับ แค่ performance จะยังคงเหมือน MySQL ไม่ได้ถูกปรับปรุงขึ้นตามด้วย)

ส่วน Tools ที่เราจะใช้สำหรับ Backup จริงๆ ชื่อ XtraBackup ครับ ซึ่งเป็นทีมพัฒนาทีมเดียวกับ Percona Server (และ Percona Data Recovery Tool for InnoDB จากบทความที่แล้วด้วยเช่นกัน) เจ้าตัว Xtrabackup นี้จริงๆ ใช้งานกับ mysql ธรรมดาที่ไม่ใช่ Percona Server ก็ได้แต่จะมีความสามารถบางอย่างที่ทำไม่ได้หากไม่ได้ใช้ Percona Server ครับ เช่น การ Backup/Restore ฐานข้อมูลเฉพาะตารางบางตารางที่เราต้องการ (เพื่อประหยัดเวลา/cpu ของ server) เป็นต้น ซึ่ง Tools ตัวนี้จะช่วย Backup แบบไม่ต้องปิด server (ไม่ต้อง Lock Table ระหว่างทำการ backup) ได้เฉพาะตารางที่ใช้งานฐานข้อมูลชนิด InnoDB เท่านั้น (จริงๆ MyISAM ก็ใช้ Tools ตัวนี้ช่วย backup ได้ครับ แค่จะยังติด lock อยู่เหมือนเดิม) นอกจากนี้หากเราใช้งานฐานข้อมูลบน VPS หรือ Cloud ที่ให้พื้นที่ใช้งานน้อยๆ ยังสามารถ Backup เป็นแบบ Incremental หรือส่งไฟล์ Backup เป็น stream ไปเข้า server ตัวอื่นที่มีพื้นที่เยอะกว่าได้อีกด้วย! (Amazing ไหมละ!) ซึ่งการ Backup โดยที่ Server ยังคงให้บริการได้ปกติแบบนี้เราจะเรียกว่า Hot Backup ครับ ส่วนการ Backup ที่จำเป็นต้องปิด Server ระหว่าง Backup เราจะเรียกว่า Cold Backup เอาละหลังจากติดตั้งเสร็จแล้ว (และต้องมี account root ของ OS ด้วยนะครับ) ลองมาดูวิธีใช้งานกันดีกว่าครับ (ทุกขั้นต้อนต้องทำขณะเป็น root ครับ)

ขั้นตอนการ Backup

1. innobackupex –user=DBUSER –password=DBUSERPASS –no-lock –defaults-file=/path/to/my.cnf /path/to/BACKUP-DIR/
   แก้ dbuser/dbuserpass ให้เรียบร้อย และ path นี้เป็น dir สำหรับเก็บ backup ที่ได้ออกมา รอจนปรากฎคำว่า “innobackupex: completed OK!” ที่บรรทัดสุดท้าย แสดงว่าสำเร็จ (ถ้าโปรแกรมฟ้องว่าหา datadir ไม่พบให้ไปแก้ my.cnf เติม datadir เข้าไปครับ ทั่วไป default จะอยู่ที่ /var/lib/mysql แต่ถ้ามี datadir แล้วยังฟ้อง แสดงว่าหาไฟล์ my.cnf ไม่เจอ ตรวจสอบไฟล์ my.cnf ให้ดีว่า path ที่ระบุถูกต้องหรือไม่)
2. innobackupex –apply-log /path/to/BACKUP-DIR/xxx
   โดยที่ xxx คือ dir ที่โปรแกรมสร้างขึ้นซึ่งมักจะเป็นชื่อวันที่ + เวลาที่ backup รอจนปรากฎคำว่า “innobackupex: completed OK!” ที่บรรทัดสุดท้าย แสดงว่าสำเร็จ (ถ้าโปรแกรมฟ้องว่า ibbackup เลือก binary ไม่ถูก ให้เติม option –ibbackup ตามด้วยชื่อในหน้านี้โดยเลือกให้ถูกต้องตามที่เขียนไว้ครับ)

ขั้นตอนการ Restore

1. เมื่อต้องการ Restore Backup ที่เก็บไว้ ให้สั่ง “service mysql stop” ลบข้อมูลใน datadir ทิ้งให้ว่างเปล่า (หรือจะแค่เปลี่ยนชื่อ ป้องกันความผิดพลาดก็ได้ครับ) แล้วสั่ง
   innobackupex –copy-back –default-file=/path/to/my.cnf  /path/to/BACKUP-DIR/xxx
   รอจนปรากฎคำว่า “innobackupex: completed OK!” ที่บรรทัดสุดท้าย แสดงว่าสำเร็จ ถ้าขึ้นว่า “Original data directory ‘./’ is not empty! at /opt/local/bin/innobackupex” แสดงว่าหาไฟล์ my.cnf ไม่เจอ
2. chown -R mysql:mysql /var/lib/mysql
   เพื่อเปลี่ยน permission จาก root (user ที่เราใช้อยู่) เป็น mysql แล้วสั่ง “service mysql start” เป็นอันเสร็จ ง่ายไหมล่ะครับ หุหุ

ขั้นตอนการทำ Replication

อันนี้เป็นของแถมครับ โดยปกติแล้วเราจะทำ Replication กันเราจะต้อง Backup ข้อมูลจากเครื่อง Master และสั่ง Lock Table ไม่ให้มีการเปลี่ยนแปลงข้อมูลได้จนกว่าจะ Setup Replication เสร็จ แต่นี่ไม่ต้องครับ ตัว Master ยังคงให้บริการได้ปกติและเราสามารถเพิ่มจำนวน Slave กี่ตัวก็ได้ในขณะนั้นตามต้องการ โดยให้ทำตามขั้นตอน Backup จนจบขั้นตอนที่ 2 แล้วต่อด้วยขั้นตอนด้านล่างต่อครับ

1. หลังได้ไฟล์ Backup ชื่อ xxx (เป็นเวลา backup) แล้วสั่ง
   tar -zcvf db.tar.gz xxx
   ให้เรียบร้อย โดย xxx คือ dir ที่เก็บ backup ของเราเอาไว้ (ที่ชื่อเป็นวัน-เวลา backup นั่นแหละครับ) เพื่อเตรียมโยนไปยัง server อีกตัวที่ต้องการจะทำ Slave ครับ
2. โยนไฟล์ db.tar.gz ที่บีบอัดไว้ไปยังเครื่อง Slave ด้วย rsync, scp ตามแต่สะดวกครับ หรือใครไม่ได้ลงไว้ ก็ใช้ tools มาตรฐานเลยครับ ssh ด้วยคำสั่ง
   ssh USER@SLAVE_IP cat < “/path/to/db.sql.gz” “>” “/path/to/save”
   ก็แก้ไข USER, SLAVE_IP, path ตัวแรก (เครื่องที่มี backup ไว้), path ที่ต้องการจะส่งไปให้ (เครื่อง slave) ให้ถูกต้องแล้วส่งไฟล์ได้เลยครับ
3. โยนไฟล์ my.cnf จากเครื่อง Master ไปยังเครื่อง Slave ด้วยคำสั่งเดียวกันกับข้อ 2 ครับ
4. mysql -uroot -p เข้าในเครื่อง Master ไปสร้าง user สำหรับเครื่อง Slave ดังนี้ครับ
   GRANT REPLICATION SLAVE ON *.*  TO ‘repl’@'$slaveip’ IDENTIFIED BY ‘$slavepass’;
   repl คือ user mysql ส่วน $slaveip, $slavepass ก็ตามชื่อเลยครับ
5. login เข้าเครื่อง Slave แล้ว untar ด้วย
   tar -zxvf db.tar.gz
   ที่ตำแหน่งที่เราเก็บไฟล์ไว้ตามคำสั่งในข้อ 2
6. service mysql stop ที่เครื่อง slave
7. copy dir xxx ที่ได้จากการแกะ tar ไปที่ datadir ของเครื่อง Slave ทับไปเลย (หรือจะ mv เปลี่ยนชื่อ datadir เดิมเก็บไว้ก่อนก็ได้เช่นเดิมครับ)
8. แก้ไฟล์ my.cnf ที่เครื่อง slave โดยบรรทัด server-id แก้เป็น
   server-id=2
9. ถ้าใช้ ubuntu หรือ debian ให้แก้ไฟล์ /etc/mysql/debian.cnf ซึ่งจะมี user debian-sys-maint อยู่ในนั้นด้วยครับ โดยแก้ password ให้เป็น password เดียวกับเครื่อง Master ไม่อย่างนั้น startup script “service mysql stop/start/restart” จะพังครับ
10. chown -R mysql:mysql datadir
    โดย datadir แก้เป็นตำแหน่งที่เก็บข้อมูล database ตามต้องการครับ (default คือ /var/lib/mysql)
11. เปิดไฟล์ xtrabackup_binlog_info ที่อยู่ใน datadir ที่เราพึ่ง copy มาจะพบเลขลักษณะประมาณนี้
    TheMaster-bin.000001 481
    ของคุณจะเป็นเลขอื่นที่ไม่ซ้ำกันกับผมแน่ๆ ครับ เป็นเลขตำแหน่ง log สุดท้ายของ database ที่เราต้องการจะ replicate นั่นเอง
12. mysql -uroot -p
    เข้า mysql เครื่อง Slave ครับแล้วพิมพ์

    CHANGE MASTER TO
    MASTER_HOST='$masterip',
    MASTER_USER='repl',
    MASTER_PASSWORD='$slavepass',
    MASTER_LOG_FILE='TheMaster-bin.000001',
    MASTER_LOG_POS=481;
    START SLAVE;
    แก้ข้อมูลให้ถูกต้องโดยตามข้อมูลที่สร้างที่ผ่านมา

13. ลองสั่ง SHOW SLAVE STATUS \G โดยยังไม่ออกจาก MySQL Console หากพบบรรทัดเหล่านี้แสดงว่าสำเร็จแล้ว

             ...
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
             ...

เป็นไงบ้าง เจ๋งใช่ไหมละครับ ลองเก็บไปใช้กันดูนะครับ

• ไม่สามารถ 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/

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

1. ตารางที่ query มันใหญ่มาก! ไม่สามารถสั่ง INSERT INTO user2 (SELECT * FROM user) ได้ตรงๆ เลยแบบง่ายๆ

2. ลองสั่ง INSERT INTO user2 (SELECT * FROM user LIMIT 0,1000) ไรทำนองนี้ก็ไม่ให้อีก! มันฟ้องว่า subquery ไม่ support limit เวรจริงๆ ครับ 55

3. ลองใช้ php ช่วย คือสั่ง SELECT * FROM user WHERE user_id NOT IN (SELECT user_id FROM user2) LIMIT 0,1000 แล้วค่อยเอา user_id ที่ได้มา query ต่อเป็น “INSERT INTO user2 (SELECT * FROM user WHERE user_id IN(“.implode(‘,’, $a_user_id).”))” ก็ยังไม่ได้เพราะแต่ละ row มันใหญ่มาก กว่าจะเสร็จก็ช้ามากๆ

4. สุดท้ายจึงได้เป็น SELECT user_id FROM user WHERE user_id NOT IN (SELECT user_id FROM user2) LIMIT 0,1000 แล้วต่อด้วย ”INSERT INTO user2 (SELECT * FROM user WHERE user_id IN(“.implode(‘,’, $a_user_id).”))” เหมือนเดิม ก็ใช้ได้ละครับ

5. รันไปซักพักพบ “MySQL Server has gone away” อีกแล้ว – -” จึงค่อยๆ ลด Limit มาเหลือ 1 ก็ยังพัง และค่อยๆ ลอง SELECT * FROM user WHERE id=1 ไปเรื่อยๆ โดยเปลี่ยน * เป็น field ในตารางที่มีทีละ field ปรากฏว่าก็เจอ field เจ้าปัญหาจนได้ เป็น Text field นี่เอง! ซึ่งเราไม่สามารถแม้กระทั่งสั่ง DELETE FROM user WHERE id=1 ช่างโหดร้ายยิ่ง!

6. ต่อมาจึงต้องใส่ offset เพื่อ skip row ที่มีปัญหาไป แล้วก็รันๆ ไปซักพักอีกก็เจออีก สรุปว่าข้อมูล user แสนคน เจอ row ที่มีปัญหาทั้งหมด 5 row มันไปทำอีท่าไหนถึงพังก็ไม่อาจทราบได้ และตอนนี้ก็สามารถกลับมาใช้งานได้ตามปกติซะที