MYSQLのibdata1ファイルからレコードデータの復旧方法

Question

度々お世話になります。

以前質問させていただいた件で、ラズベリーパイが動かなくなり復旧方法をお尋ねしたところ、別なSDカードへラズベリーパイをインストールし、そこからマウントしてアクセスしてはどうかとの回答を頂き、無事にアクセスすることはできました。

バッチファイルやプロパティファイルは無事を確認したのですが、
MySQLのデータのみ、作成したデータベースの.frmファイルとdb.optファイルを残したのみで、MYD,MYIファイルが存在していませんでした。
（場所は/マウント先/var/lib/mysql/作成したDB名/を見ています）

上記ディレクトリ以外に自作したDBのMYD,MYIファイルは存在しますでしょうか・・・？

存在しない場合、ibdata1ファイルからのレコードデータ復旧等ご存知の型いらっしゃいますでしょうか？

レコードが復旧できなくても、発行したクエリの復旧でもかまいません。
どなたかよろしくお願いいたします。

Answer 1

少し古いMySQLで InnoDB を使っている場合、テーブルの構造はそれぞれ DB名/テーブル名.frm ファイルに、データはまとめて ibdata1 に保存されます。db.opt にはDBの設定が書き込まれています。

テーブル名.MYD, テーブル名.MYI は大昔に使われていた MyISAM というストレージエンジン用のファイルですので、MyISAM を使っていなければ無くて正解です。

というわけで全ファイルが残っているように見えます。ファイルの中身が無事であれば、同じバージョンのMySQLがインストールされたサーバーを用意し、/var/lib/mysql/ のデータをまるごとコピーさせれば mysqldump などでデータが取り出せると思います。

Comments

[s1170022]

ご教授いただきありがとうございます。
回答いただいた内容でディレクトリごとコピーし、mysqldumpをかけたのですが、「Got error: 2002: Can't connect to local MySQL server throgh socket '/var/run/mysqld/mysqld.sock'」と出力され実行できませんでした。

該当ディレクトリにmysqld.sockを作成してもダメでした・・・。
他に原因があるのでしょうか・・・？
よろしくお願いいたします。

※試行錯誤しつつコメントしているので、編集しなおしながらやっているため、初期返信時とコメントが変わっています。すみません。

[miyahan]

そもそもMySQLを起動していますか？ mysqld.sock はMySQLサーバーが作成するものなので、ユーザーが作ってはいけません（起動しなくなります）。my.ini を含め、すべてのファイルを別サーバーにコピーしてそこで暫定のサーバーを立てるイメージです。

[s1170022]

MYSQLを再起動しようとしても同じメッセージが表示されて起動不可でした。

コピーするものとしては、「/var/lib/mysql」のディレクトリごと入れ替えるイメージで合っていますでしょうか？

-- 追記
MYSQLを再インストール→MYSQL起動→「/var/lib/mysql」のディレクトリを入れ替え→mysqldumpを叩く

とした結果は、Unknown database '自作DB名'と出てきました。
起動時に読み込まれるのだと思うので、この結果は当然かと思うのですが、起動前、後で入れ替えてもどちらにせよエラーとなってしまいました・・・。

/var/lib/mysqlディレクトリ内にあるもので一部不要なもの等があるのでしょうか？

[miyahan]

コピーするファイルは /var/lib/mysql 配下の全ファイルと、設定ファイル(my.cnf) です。コピーは必ずMySQLサーバーを停止した状態で行って下さい。

[s1170022]

上記の通りstopしてから、my.cnf含むファイルコピーを行い、再度startをしても変化なしでした・・・
sudo journalctl -xnコマンドを実行して表示されたメッセージに、赤文字で「Failed to start LSB: Start and stop the mysql database server daemon.」というのも出てきたのですが、これは余り関係ないですか？

[miyahan]

/var/lib/mysql/***.err というファイルに詳しいエラー内容が記録されているので、そのエラーメッセージをググってみてください。なんとなくパーミッション周りの設定ミスな気がします。

[s1170022]

/var/lib/mysqlに該当のものがなかったので、他のディレクトリでそれらしきものをあたってみたところ、/var/log/mysql/error.logというものが見つかりました。

結果、以下が出力されていました。
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
170708 7:47:16 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
170708 7:47:16 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
170708 7:47:16 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
170708 7:47:16 InnoDB: The InnoDB memory heap is disabled
170708 7:47:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170708 7:47:16 InnoDB: Compressed tables use zlib 1.2.8
170708 7:47:16 InnoDB: Using Linux native AIO
170708 7:47:16 InnoDB: Initializing buffer pool, size = 128.0M
170708 7:47:16 InnoDB: Completed initialization of buffer pool
170708 7:47:16 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

権限というよりはファイルが存在しないためにエラーとなっている感じです・・・。
とりあえずデータの吸出しができればよいとのことで/var/lib/mysqlのディレクトリの権限を777にしたり、色々権限まわりをいじっていたので確かに権限まわりのエラーではありそうですが、見当がつきません・・・

お手数おかけしてしまって非常に心苦しいのですがよろしくお願いいたします。

[s1170022]

度々すみません。
色々調べながら作業をした結果、最終的に以下のエラーメッセージとなりました。
170708 10:02:14 InnoDB: Assertion failure in thread 1996272144 in file fil0fil.c line 4578
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:02:14 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346093 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
このメッセージについても調べたところ、MYSQLのバグもあって、クラッシュした箇所を取り除かないと復旧は難しいとの記事も見つけました。

やはり復旧は難しいのでしょうか・・・？

[miyahan]

お、起動までできましたね。
エラーメッセージに "there may be corruption in the InnoDB tablespace." とあるので、ラズパイ故障時にファイルが破損してしまったようです。

こちらの回答を見て修復を試みて下さい：https://teratail.com/questions/82350

[s1170022]

実はまだ起動ができておらず、起動しようとして調べた結果、「mysqld_safe」を実行してセーフモードで立ち上げれば良いとの記事を見つけたので、こちらで実行した際のログが上記となります。

依然として通常のstartで立ち上げようとするとmysql.sockのエラーが出るのには変わりありません・・・
ほぼ全てchmod 777とchown mysql:mysqlをかけたのですが・・・

[s1170022]

上記URLに記載の、innodb_force_recovery = 1を5にした結果、何とかMYSQLを起動することには成功しました。

起動ができたのでdumpコマンドを叩いたところ、「テーブル名.frm was created with a different version of MySQL and cannot be read」と出てしまいました。
一部のテーブルのdumpは出力しているのですが、一番欲しいテーブルのdumpが上記メッセージになってしまっているため、
どうにかして読めるようにしたいです・・・
メッセージどおり、バージョンが一致していないと読み込み不可でしょうか？
ちなみにinnodb_force_recoveryは5で満足できなかった結果があったため、最大の6にしてあります。

[miyahan]

はい･･･。
今回かなり繊細な作業をしているので、製品およびバージョンは最低限合わせていただかないと。

[s1170022]

バージョンをあわせようと、調べてみて現在インストールしているMySQLのバージョン確認方法は出てくるのですが、
破損したほうのMySQLバージョンを調べる方法が出てきません・・・。
my.cnfとかどこかの定義ファイルに載っているものなのでしょうか？

また、上記で調査した結果、破損したほうがバージョンが古かった場合、現在のMySQLをダウングレードする方法はあるのでしょうか・・・

[miyahan]

原則MySQLには下位互換性があるので、古いデータを新しいMySQLサーバーに持ってきても動きます。なので、いま作業している復旧用サーバーが古いと考えるのが妥当です。

ですが、innodb_file_per_table が OFF (.ibdファイルが作成されていない) ということは、ラズパイは MySQL 5.5 以下だった可能性が高くバージョンは合っているようにも見えます。（そもそもバージョン違いが原因なら他のテーブルも読めないでしょうし）

ということはMySQL(InnoDB)が中身を解釈できないほどファイルが破損している可能性があります。

ちなみにそのテーブルに対し、CHECK TABLE <テーブル名>; および REPAIR TABLE <テーブル名>; を実行するとどうなりますか？

[s1170022]

ちなみに、古いラズパイも導入が今年に入ってから、復旧用にもラズパイを入れたのが今週でしたので、導入時期はそれほど差がありません。

テーブルは１５あるのですが、このうち読めないのが４つありました。
４つのうち２つは履歴テーブルでしたので、あきらめます。（ただ、こちらのテーブルはDUMPは取れ、最初に入れた数百レコードのみ読めるような状態です。）
残りの2つがマスタテーブルなのですが、こちらに対して上記クエリを実行した結果が以下となります。
CHECKクエリ
１つ目：ERROR2013 LostConnection to MySQL server during query
２つ目：ERROR2006 MySQL server has gone away
　　　　----- table
　　　　|テーブル名|check|Error|Table './DB名/テーブル名.frm'was created with a diffrent ～..
　　　　|テーブル名|check|error|Corrupt
　　　　------

REPAIRクエリ
１つ目：
　　　　----- table
　　　　|テーブル名|repair|note|The storage engine for the table doesn't support repair
　　　　-----
２つ目：CHECKクエリ実行と同じ

上記２つのマスタテーブルより、DUMPできたテーブルの幾つかのほうが、SELECTやINSERT回数は多いのですが、何故か一番欲しい２つのテーブルのみ参照できませんでした・・・

[s1170022]

ERROR2006についてはmy.cnfのMAXサイズを変更することによって出なくなりました。バージョン違いのエラーについてはまだ出ています・・・。
こちらは流石に対処法がないレベルでテーブルが破損していると見てしまったほうが良いのでしょうか・・
(バージョンが合っているはずなのに違うというメッセージが出るくらい中身が読めない)

[miyahan]

例のテーブルを読むとMySQLがクラッシュしてしまうようですね。
これ以上の修復は専門家でないと難しいと思います。

[s1170022]

やはり難しいのですね・・・
クラッシュしたテーブルについてはあきらめることとします。
ですが大抵のテーブル情報は復旧できましたので助かりました。

今回非常にお世話になりました。
ありがとうございます。