Giới thiệu

Vì một vài lý do nào đó, hệ thống của bạn bị quá tải, hết tài nguyên v.v dẫn tới MySQL bị crash, cụ thể trong bài viết này là MySQL sử dụng Engine InnoDB mặc định bị crash, dẫn tới các file lưu tablespace của hệ thống InnoDB cùng với một số dữ liệu đang pending trong database bị corrupt, bạn không thể start lại được MySQL vì các cấu trúc của InnoDB lưu trong các file corrupt đã bị mất hoàn toàn.

Thông báo lỗi thường gặp khi start MySQL trong trường hợp trên:

[ERROR] InnoDB: Header page consists of zero bytes in datafile: .\ibdata1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.htmlfor how to resolve the issue.

[ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile '.\ibdata1' could not be found in the doublewrite buffer.

[ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption.

[ERROR] Plugin 'InnoDB' init function returned error. 

[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

[ERROR] Failed to initialize plugins. 

[ERROR] Aborting

Bài viết sau sẽ hướng dẫn bạn cách xử lý và lấy lại dữ liệu trong trường hợp trên.

Hướng dẫn

Backup dữ liệu MySQL

Tiến hành copy dữ liệu MySQL vào thư mục /tmp

• Cụ thể trong bài viết này sử dụng Centos 7 64bit, MySQL được cài đặt tại /var/lib/mysql.

cp -r /var/lib/mysql /tmp/

Start lại MySQL

• Để start lại được mysql, cần xoá các file chứa tablespace bị corrupt trên trước khi start. Hệ thống sẽ tự động tạo lại các file này nếu nhận thấy trong thư mục cài đặt chưa có.
• Tiến hành truy cập vào thư mục cài đặt mysqlcài đặt tại /var/lib/mysql

cd /var/lib/mysql

• Các file cần remove trong trường hợp này là ibdata1, ib_logfile0, ib_logfile1, ib_logfile101
• Tiến hành đổi tên các file này

• Start lại mysql

systemctl start mysqld

• Tới bước này bạn đã có thể truy cập và sử dụng MySQL, tuy nhiên các Database cũ sẽ không thể truy xuất dữ liệu từ các tables cũ

Cài đặt và sử dụng mysqlfrm để lấy lại cấu trúc table từ file frm

Cài đặt mysqlfrm

Thông thường đối với các tables sử dụng InnoDB Engine, cấu trúc tables sẽ được lưu trong trong các file frm, dữ liệu được lưu trong các file idb. mysqlfrm sẽ tiến hành tạo ra giả lập 1 instance MySQL mới và trích xuất được câu lệnh SQL dùng để tạo ra table này.

Ví dụ trong trường hợp này, cần recovery các tables trong database DB1. Các file frm và idb được lưu trong thư mục “/var/lib/mysql/DB1”

• Tiến hành cài đặt mysqlfrm. mysqlfrm được cung cấp trong pack mysql-utilities, bạn có thể cài đặt thông qua công cụ yum của Centos

yum install epel-release -y

yum install mysql-utilities -y

• Khi cài đặt xong câu lệnh mysqlfrm sẽ được add vào hệ thống

• Trong một số trường hợp, khi cài đặt xong không thế sử dụng được mysqlfrm do bị lỗi như sau

Traceback (most recent call last):
  File "/bin/mysqlfrm", line 27, in <module>
    from mysql.utilities.common.tools import (check_python_version,
ImportError: No module named utilities.common.tools

• Bạn có thể sử dụng câu lệnh sau để workaround bug này (viết trong 1 dòng):

ln -s /usr/lib/python2.7/site-packages/mysql/utilities /usr/lib64/python2.7/site-packages/mysql/utilities

Sử dụng mysqlfrm

• Tiến hành chmod -R 777 thư mục mysql đã backup ở /tmp

chmod -R 777 /tmp/mysql

• Tiến hành sử dụng mysqlfrm trên dữ liệu của database DB1 được backup trong thư mục /tmp. Ví dụ để trích xuất ra câu lệnh SQL dùng để tạo table Player của database 1, ta sử dụng câu lệnh sau (viết 1 dòng):

mysqlfrm --user=mysql --server=root:passwordrootmysql@localhost --port=3307 /tmp/mysql/DB1/Player.frm -vvv

• Hệ thống sẽ xuất ra cho bạn 1 đoạn query  dùng để tạo table Player trên. Trong ví dụ này là:

CREATE TABLE `Player` (
`ID` int(10) unsigned NOT NULL,
`PlayerName` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Account` varchar(80) NOT NULL,
`SaveTime` datetime DEFAULT NULL,
`BaseInfo` blob NOT NULL,
`AsyncData` blob,
`BinData` mediumblob,
`Ban` bigint(20) DEFAULT ‘0’,
`BanReason` varchar(128) DEFAULT NULL,
`Plat` int(11) DEFAULT ‘0’,
PRIMARY KEY (`ID`),
KEY `PlayerName` (`PlayerName`),
KEY `Account` (`Account`),
KEY `SaveTime` (`SaveTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Tạo lại table từ query được trích xuất

• Tạo 1 Database mới dùng để restore dữ liệu

CREATE DATABASE db_restore;

• Tạo lại table sử dụng query được trích xuất từ bước trước.
• Lưu ý: Cuối câu query mysql có dấu “;“

• Tiến hành xoá tablespace dược tạo ra cùng với table

ALTER TABLE Player DISCARD TABLESPACE;

• Copy file idb của table này từ thư mục đã backup vào thư mục của database cần restore và phân quyền lại file này

cp /tmp/mysql/DB1/Player.ibd /var/lib/mysql/db_restore/

chown mysql. /var/lib/mysql/db_restore/Player.ibd

chmod 660 /var/lib/mysql/db_restore/Player.ibd

• Quay lại mysql, tiến hành import lại tablespace của table tạo trong bước trước

ALTER TABLE Player IMPORT TABLESPACE;

• Như vậy dữ liệu đã được khôi phục

• Bạn tiếp tục thực hiện lại thao tác trên cho tới khi hết table cần khôi phục.

Như vậy sau bài viết này bạn đã có thể thực hiện thao tác khôi phục lại dữ liệu từ dababase MySQL sử dụng Engine InnoDB sau khi bị crash và corrupt các file chứa dữ liệu hoạt động của InnoDB.

Cách 2:Tiến hành restore bằng cách “bán” tự động

• Tiến hành truy cập thư mục chứa database đã backup tại /tmp

cd /tmp/mysql/DB1

• Trích xuất query tạo table từ các file frm

touch table.sql

for table in `ls -lah ./ | grep frm | awk '{print $9}'`; do mysqlfrm --user=mysql --server=root:passwordrootmysql@localhost --port=3307 ./"$table" >> table.sql; done

grep -v "#" ./table.sql > ./table-ori.sql

sed -i 's/^$/;/' ./table-ori.sql

• Tiến hành tạo database và import query mới trích xuất được từ table-ori.sql

create database new_restore_db;

use new_restore_db;

source /tmp/mysql/DB1/table-ori.sql;

• Tiền hành discard tablespace của các tables mới tạo
• Lưu ý: Bạn vẫn đang đứng ở thư mục backup database /tmp/mysql/DB1

for table in `ls -lah ./ | grep frm | awk '{print $9}' | awk -F "." '{print $1}'`; do mysql -D new_restore_db -e "ALTER TABLE $table DISCARD TABLESPACE"; done

• Tiến hành copy các file idb vào thư mục của database cần restore

find ./ -name "*.ibd" -exec cp {} /var/lib/mysql/new_restore_db \;

• Phân quyền lại thư mục

chown -R mysql. /var/lib/mysql/new_restore_db

find /var/lib/mysql/new_restore_db/. -name "*.ibd" -exec chmod 660 {} \;

• Tiến hành import lại tablespace

for table in `ls -lah ./ | grep frm | awk '{print $9}' | awk -F "." '{print $1}'`; do mysql -D new_restore_db -e "ALTER TABLE $table IMPORT TABLESPACE"; done

• Như vậy là các database đã được khôi phục thành công