mysql5.7.33 で誤って ibdata ファイルを削除した後にデータを回復する方法

2.1. MySQLの障害発生前にデータベース内の各テーブルに何件のレコードがあったかを確認する
2.2. ibdata の削除の失敗をシミュレートします。
2.3. データを取得する方法:
2.4. 新しいMySQLインスタンスdb_bbsライブラリにインポートされたテーブルレコードを取得し、元のライブラリtest.txtテーブルレコードファイルと比較します。

1. シナリオの説明:

多くの場合、MySQL データベースを起動できないためにデータにアクセスできませんが、通常、アプリケーションデータは失われません。システムテーブルスペースなどの他のファイルが破損しているか、MySQL のバグが発生しているだけです。

このときバックアップがない場合は、データが失われたと考える人が多いと思いますが、実際にはほとんどの場合、データは保存できます。
MyISAM エンジンのテーブルスペースの場合、対応するデータファイルを新しいデータベースにコピーするだけで、データを復元できます。
InnoDB エンジンのデータベーステーブルスペースの場合、テーブルスペースを転送することでデータを回復できます。
前提として、MySQLはパラメータinnodb_file_per_table = 1つの独立したテーブルスペースファイルを有効にしています。

MySQL データテーブルスペースファイル ibdata ファイルが破損しているか、変更または削除されていない場合、MySQL サービスは再起動に失敗します。同時に、MySQL データは時間内にバックアップされません。できるだけ多くの MySQL データを回復するにはどうすればよいでしょうか? ?

2. 事例のデモンストレーション:

2.1. MySQLの障害発生前にデータベース内の各テーブルに何件のレコードがあったかを確認する

for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt

MySQL の障害を確認する前に、データベースにはいくつのテーブルがありますか?

合計 39 個のテーブルがあります:

[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

2.2. ibdata の削除の失敗をシミュレートします。

ibdata ファイルを削除します (本番環境では許可されていません)

innodb_force_recovery =6 現時点では MySQL の強制起動パラメータを使用して MySQL サービスを開始しますが、効果はありません。データテーブルスペースファイル ibdata ファイルが削除されているため、現時点では MySQL サービスを開始できません。
このため、MySQL は innodb エンジンを使用します。また、独立した表領域パラメータ innodb_file_per_table = 1 が有効になっています。そのため、このときは、テーブルスペースを転送する方法を使用してデータを救出することができます。

2.3. データを取得する方法:

まず、不足しているテーブル構造を作成します。

まず、障害が発生した MySQL サーバーに mysql-utilities をインストールします。

yum -y mysql-utilities をインストールします

mysqlfrm を使用して、.frm ファイルからテーブル作成ステートメントを取得します。

.frm ファイルを解析してテーブル作成ステートメントを生成する

mysqlfrm --診断 
[root@test02 db_bbs]# mysqlfrm --diagnostic /data/mysql/data/db_bbs/t_admin.frm |grep -v "^#"
テーブル `db_bbs`.`t_admin` を作成します (
 `f_id` int(4) NOT NULL AUTO_INCREMENT, 
 `f_type` tinyint(1) NULLではない、 
 `f_username` varchar(80) NOT NULL, 
 `f_password` varchar(80) NOT NULL, 
 `f_nick_name` varchar(80) NOT NULL, 
 `f_real_name` varchar(80) NOT NULL, 
 `f_create_time` bigint(4) NOT NULL, 
 `f_update_time` bigint(4) NOT NULL, 
 `f_last_login_time` bigint(4) デフォルト NULL, 
 `f_last_login_ip` varchar(80) デフォルト NULL, 
 `f_status` tinyint(1) NULLではない、 
主キー `PRIMARY` (`f_id`) BTREE の使用
)ENGINE=InnoDB ROW_FORMAT = 2;

すべてのテーブル作成ステートメントを /tmp/create.sql ファイルにインポートします。

[root@test02 ~]# cd /data/mysql/data/db_bbs/
[root@test02 db_bbs]# for n in `ls -l /data/mysql/data/db_bbs/*.frm|awk -F '/' '{print $NF}'|xargs -n 40`;do mysqlfrm --diagnostic $n|grep -v "^#" >>/tmp/create.sql;done

生成されたテーブル作成ステートメントを新しい MySQL インスタンスライブラリにインポートします。

[root@10-10-127-11 ~]# mysql db_bbs < create.sql 
2 行目のエラー 1064 (42000): SQL 構文にエラーがあります。10 行目の '5' 付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。

その理由は、取得したテーブル作成SQLにパラメーターROW_FORMAT = 2が含まれているためです。
###テーブル作成ステートメント内の ROW_FORMAT = 2、ROW_FORMAT = 5 を含む文字を削除します。テーブル作成ステートメントを再インポートします。###

バッチ置換コマンドは次のとおりです。

cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
cat /tmp/create.sql|sed -e 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g'|grep ROW_FORMAT |uniq -c
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 2;/ENGINE=InnoDB ;/g' /tmp/create.sql
sed -i 's/ENGINE=InnoDB ROW_FORMAT = 5;/ENGINE=InnoDB ;/g' /tmp/create.sql
cat /tmp/create.sql|grep ROW_FORMAT |uniq -c

テーブル作成ステートメントを新しい MySQL インスタンス db_bbs データベースにエクスポートすると、フィールドが長すぎるというエラーが報告されます。

[root@10-10-127-11 ~]# mysql db_bbs -f < create.sql 
232 行目のエラー 1074 (42000): 列の長さが列 'f_content' に対して大きすぎます (最大 = 16383)。代わりに BLOB または TEXT を使用してください。
299 行目のエラー 1074 (42000): 列の長さが列 'f_desc' に対して大きすぎます (最大 = 16383)。代わりに BLOB または TEXT を使用してください。
365 行目のエラー 1074 (42000): 列 'f_body_image' の列の長さが大きすぎます (最大 = 16383)。代わりに BLOB または TEXT を使用してください。
406 行目のエラー 1074 (42000): 列の長さが列 'f_content' に対して大きすぎます (最大 = 16383)。代わりに BLOB または TEXT を使用してください。
433 行目のエラー 1074 (42000): 列 'f_summary' の長さが大きすぎます (最大 = 16383)。代わりに BLOB または TEXT を使用してください。

フィールドの長さタイプを変更した後、テーブル作成SQLを新しいMySQLデータベースに再インポートします。

[root@10-10-127-11 ~]# mysql db_bbs -f < create.sql 
[ルート@10-10-127-11 ~]# 
[ルート@10-10-127-11 ~]# 
[root@10-10-127-11 ~]# mysql -e "use db_bbs;show tables;"|sed '1d'|wc -l
39

###新しく作成された MySQL インスタンス内のデータが含まれていない .ibd ファイルを破棄し、障害のあるデータベースの .idb ファイルをインポートします。###

新しく作成されたライブラリのデータ .ibd ファイルを破棄します。

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done
[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
ls: *.ibd にアクセスできません: そのようなファイルまたはディレクトリはありません

*すべての .idb ファイルが破棄されたことがわかります。次に、データを含む古い .ibd ファイルを新しい MySQL インスタンスの ./data/db_bbs/ ディレクトリにコピーします。所有者を変更することを忘れないでください: chown mysql。次に、これらのデータファイルをデータベースにインポートします**。

[root@test02 db_bbs]# scp *.ibd [email protected]:/data/mysql/data/db_bbs/
[email protected]のパスワード: 
browse_record.ibd 100% 100MB 50.0MB/秒 00:02  
t_admin.ibd         
........
........

[root@10-10-127-11 db_bbs]# ll *.ibd|wc -l
39
[root@10-10-127-11 db_bbs]# ll *.ibd
-rw-r----- 1 ルート ルート 104857600 3月14日 21:56 browse_record.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_admin.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_anonymous_code.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_apply.ibd
-rw-r----- 1 ルート ルート 9437184 3月14日 21:56 t_attach.ibd
-rw-r----- 1 ルート ルート 147456 3月14日 21:56 t_banner.ibd
-rw-r----- 1 ルート ルート 163840 3月14日 21:56 t_banner_log.ibd
-rw-r----- 1 ルート ルート 114688 3月14日 21:56 t_black_ip.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_black_user.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_block_userbaseinfo.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_collect.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_country_code.ibd
-rw-r----- 1 ルート ルート 163840 3月14日 21:56 t_ct_goods.ibd
-rw-r----- 1 ルート ルート 131072 3月14日 21:56 t_ct_goods_record.ibd
-rw-r----- 1 ルート ルート 9437184 3月14日 21:56 t_ct_integral.ibd
-rw-r----- 1 ルート ルート 46137344 3月14日 21:56 t_ct_integral_record.ibd
-rw-r----- 1 ルート ルート 27262976 3月14日 21:56 t_ct_news.ibd
-rw-r----- 1 ルート ルート 9437184 3月14日 21:56 t_ct_order.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_feedback.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_lexicon.ibd
-rw-r----- 1 ルート ルート 327680 3月14日 21:56 t_logs.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_manage.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_module.ibd
-rw-r----- 1 ルート ルート 9437184 3月14日 21:56 t_post_extend.ibd
-rw-r----- 1 ルート ルート 12582912 3月14日 21:56 t_post.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_post_video.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_realtime_message.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_recommend.ibd
-rw-r----- 1 ルート ルート 46137344 3月14日 21:56 t_reply.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_reward.ibd
-rw-r----- 1 ルート ルート 196608 3月14日 21:56 t_sensitive_word.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_system_message.ibd
-rw-r----- 1 ルート ルート 9437184 3月14日 21:56 t_userbaseinfo.ibd
-rw-r----- 1 ルート ルート 344064 3月14日 21:56 t_userextendinfo.ibd
-rw-r----- 1 ルート ルート 12582912 3月14日 21:56 t_user_health.ibd
-rw-r----- 1 ルート ルート 98304 3月14日 21:56 t_user_message.ibd
-rw-r----- 1 ルート ルート 442368 3月14日 21:56 t_user_read_module_log.ibd
-rw-r----- 1 ルート ルート 17825792 3月14日 21:56 t_viewpoint.ibd
-rw-r----- 1 ルート ルート 114688 3月14日 21:56 t_white_ip.ibd

[root@10-10-127-11 db_bbs]# chown mysql.mysql *.ibd

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done

各テーブルのテーブルスペースをインポートすると、個々のテーブルに対してエラーが発生します。

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done 
1 行目のエラー 1808 (HY000): スキーマが一致しません (テーブルには ROW_TYPE_DYNAMIC 行形式があり、.ibd ファイルには ROW_TYPE_COMPACT 行形式があります。)

テーブルファイルを確認すると、独立したテーブルスペースにインポートされたときに browse_record テーブルのみがエラーを報告し、このテーブルのデータ回復が失敗していることがわかります。

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record
警告: InnoDB: テーブル 'browse_record' のテーブルスペースが破棄されました
エラー: テーブル 'browse_record' のテーブルスペースが破棄されました
エラー: 破損
db_bbs.t_admin 正常
db_bbs.t_anonymous_code OK
db_bbs.t_apply はOK
db_bbs.t_attach 正常
db_bbs.t_banner 大丈夫です
db_bbs.t_banner_log 正常
db_bbs.t_black_ip 大丈夫
db_bbs.t_black_user 大丈夫
db_bbs.t_block_userbaseinfo OK
db_bbs.t_collect は正常です
db_bbs.t_国コード OK
db_bbs.t_ct_goods 大丈夫
db_bbs.t_ct_goods_record は正常です
db_bbs.t_ct_integral 正常
db_bbs.t_ct_integral_record 正常
db_bbs.t_ct_news 大丈夫
db_bbs.t_ct_order は正常です
db_bbs.t_feedback 大丈夫
db_bbs.t_lexicon OK
db_bbs.t_logs 正常
db_bbs.t_manage 正常
db_bbs.t_module 正常
db_bbs.t_post 大丈夫です
db_bbs.t_post_extend は正常です
db_bbs.t_post_video 大丈夫です
db_bbs.t_realtime_message 正常
db_bbs.t_recommend 大丈夫
db_bbs.t_reply 大丈夫です
db_bbs.t_reward 大丈夫
db_bbs.t_sensitive_word は正常です
db_bbs.t_system_message 正常
db_bbs.t_user_health 正常
db_bbs.t_user_message 正常
db_bbs.t_user_read_module_log は正常です
db_bbs.t_userbaseinfo OK
db_bbs.t_userextendinfo OK
db_bbs.t_viewpoint 正常
db_bbs.t_white_ip 大丈夫

上記の browse_record テーブルの復元失敗エラーの解決方法は次のとおりです。

参考: https://blog.csdn.net/weixin_30607659/article/details/94987901

新しい MySQL インスタンスにインポートされた browse_record テーブルを削除し、次のテーブル作成ステートメントを実行して新しい browse_record テーブルを作成します。

テーブル `browse_record` を作成します (
 `id` int(4) 符号なし NOT NULL AUTO_INCREMENT,
 `post_id` int(4) 符号なし NOT NULL,
 `user_id` int(4) 符号なし NOT NULL,
 `status` tinyint(1) unsigned NOT NULL,
 `update_time` bigint(4) 符号なし NOT NULL,
 `create_time` bigint(4) 符号なし NOT NULL,
 BTREEを使用した主キー（`id`）
 キー `browse` (`post_id`,`user_id`) BTREE の使用
) ENGINE=InnoDB デフォルト CHARSET=utf8mb4 row_format=compact;

ヒント: 新しい MySQL インスタンスで browse_record テーブルを削除して削除が失敗した場合は、新しい MySQL インスタンスで db_bbs データベースを削除し、すべての db_bbs テーブルのテーブル作成ステートメントを再インポートして、次のコマンドを実行します。

mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a DISCARD TABLESPACE" ;done

テーブルスペースを新しい MySQL インスタンスに再インポートします。

[root@10-10-127-11 db_bbs]# mysql -e "show tables from db_bbs" | grep -v Tables_in_db_bbs| while read a; do mysql -e "ALTER TABLE db_bbs.$a import TABLESPACE" ;done
[root@10-10-127-11 db_bbs]#

ここでデータ修復が完了しました

MySQL db_bbs ライブラリ内のテーブルを確認します。

[root@10-10-127-11 db_bbs]# mysqlcheck -c db_bbs
db_bbs.browse_record は正常です
db_bbs.t_admin 正常
db_bbs.t_anonymous_code OK
db_bbs.t_apply はOK
db_bbs.t_attach 正常
db_bbs.t_banner 大丈夫です
db_bbs.t_banner_log 正常
db_bbs.t_black_ip 大丈夫
db_bbs.t_black_user 大丈夫
db_bbs.t_block_userbaseinfo OK
db_bbs.t_collect は正常です
db_bbs.t_国コード OK
db_bbs.t_ct_goods 大丈夫
db_bbs.t_ct_goods_record は正常です
db_bbs.t_ct_integral 正常
db_bbs.t_ct_integral_record 正常
db_bbs.t_ct_news 大丈夫
db_bbs.t_ct_order 正常
db_bbs.t_feedback 大丈夫です
db_bbs.t_lexicon OK
db_bbs.t_logs 正常
db_bbs.t_manage 正常
db_bbs.t_module 正常
db_bbs.t_post 大丈夫です
db_bbs.t_post_extend は正常です
db_bbs.t_post_video 大丈夫です
db_bbs.t_realtime_message 正常
db_bbs.t_recommend 大丈夫
db_bbs.t_reply 大丈夫です
db_bbs.t_reward 大丈夫
db_bbs.t_sensitive_word は正常です
db_bbs.t_system_message 正常
db_bbs.t_user_health 正常
db_bbs.t_user_message 正常
db_bbs.t_user_read_module_log は正常です
db_bbs.t_userbaseinfo OK
db_bbs.t_userextendinfo OK
db_bbs.t_viewpoint 正常
db_bbs.t_white_ip 大丈夫

2.4. 新しいMySQLインスタンスdb_bbsライブラリにインポートされたテーブルレコードを取得し、元のライブラリtest.txtテーブルレコードファイルと比較します。

[root@10-10-127-11 ~]# for n in `mysql -e "use db_bbs;show tables;"|sed '1d'`;do echo $n; mysql -e "use db_bbs;select count(*) from $n;";done >test.txt11

元のライブラリ test.txt テーブルレコードファイルと比較します。

[root@test02 ~]# vimdiff test.txt11 test.txt

ここではテーブルレコードが完全に一致しています。MySQL データの修復が完了しました。

参考文献:
https://mp.weixin.qq.com/s/r3KTPsFay292JnO0lgTLUg
https://www.cnblogs.com/jiangxu67/p/4744283.html
https://blog.csdn.net/Sonny_alice/article/details/80198200
https://www.cnblogs.com/jiangxu67/p/4744283.html

これで、MySQL 5.7.33 で誤って削除された ibdata ファイルからデータを回復する方法に関するこの記事は終了です。MySQL で誤って削除された ibdata の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: