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

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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MyBatis バッチによる MySql データの挿入/変更/削除
  • 誤って削除されたデータを復元するための mysqlbinlog コマンドを使用した mysql の実装
  • MySQLデータベースは重複データを削除し、メソッドインスタンスを1つだけ保持します
  • MySQL の大きなテーブルで大量のデータを一括削除する方法
  • MySQLがデータの削除を推奨しない理由
  • MySQL 内の数千万のデータを一括削除する Python スクリプト
  • MySQL のデータ削除とデータ テーブル メソッドの例
  • MySQL でデータを削除してもテーブル ファイルのサイズが変更されないのはなぜですか?
  • MySQL で大量のデータ (数千万) を素早く削除するためのいくつかの実用的なソリューションの詳細な説明
  • MySQLでデータを削除してもディスク領域が解放されないのはなぜですか

<<:  Dockerは異常なコンテナ操作を排除する

>>:  きちんとした標準的なHTMLタグの書き方を学ぶ

推薦する

JS を使用して HTML で回転するクリスマスツリーを実装する

<!DOCTYPE ヘムル パブリック> <html> <ヘッド&g...

Docker を使用して Jenkins をインストールするためのサンプル コード

Dockerコンテナのインストール時に遭遇しやすい2つの問題1.ポートはすでに割り当てられています(...

MySqlのインストールとアンインストールに関する詳細なチュートリアル

この記事では、MySqlのインストールとアンインストールのチュートリアルを参考までに紹介します。具体...

JS を使用して要素がビューポート内にあるかどうかを確認する方法

序文要素がビューポート内にあるかどうかを監視する2つの方法を共有する1. 位置計算Element.g...

Vue2.x の応答性の簡単な説明と例

1. Vue レスポンシブの使用法を確認する​ Vue の応答性は、私たち全員がよく知っています。 ...

プロセスごとにネットワーク帯域幅を監視する Linux ツール Nethogs のインストールと展開

概要Linux 用のオープン ソース ネットワーク監視ツールは数多くあります。たとえば、帯域幅の使用...

MySQLのREDOログとUNDOログの詳細な説明

MySQL ログ システムで最も重要なログは、REDO ログとアーカイブ ログです。後者は MySQ...

Windows システムに MySQL を素早くインストールして展開する方法 (グリーンの無料インストール バージョン)

まずは緑色の無料インストール版のMySQLをダウンロードします。任意のフォルダに入れて構いません。今...

nginx のフロントエンドとバックエンドに同じドメイン名を設定する方法

この記事では、主にnginxのフロントエンドとバックエンドに同じドメイン名を設定する方法を紹介し、皆...

Vue フロントエンドで PDF を生成してダウンロードする方法

目次1. インストールと導入2. PDFファイルをパッケージ化してエクスポートする方法構成の詳細PD...

要素UIテーブルはドロップダウンフィルタリング機能を実現します

この記事の例では、要素UIテーブルにドロップダウンフィルタリングを実装するための具体的なコードを参考...

Vueカスタムディレクティブを使用してドラッグアンドドロッププラグインを構築する方法

HTML5 のドラッグ アンド ドロップ機能は誰もが知っていますが、これを使用するとドラッグ アンド...

Tomcatでcatalina.batがUTF-8に設定されている場合、コンソールに文字化けした文字が表示されます

1. catalina.bat は UTF-8 に設定する必要があります。UTF-8 に設定しないと...

vue の webpack -v エラー解決の概要

XiaobaiはVueについて学び、次にwebpackについて学び、そしてさまざまなものをインストー...

共通要素のデフォルトのマージンとパディング値に関する議論

今日は、さまざまなブラウザでのデフォルト要素のマージン値が何であるかという問題について説明しました。...