MySQLはbinlogを通じてデータを復元する

MySQL ログファイル

成熟したソフトウェアには、成熟したログ システムが備わっています。ソフトウェアで問題が発生した場合、これらのログは問題の原因を見つけるための貴重な情報源となります。同様に、MySQL も例外ではなく、MySQL の実行ステータスを記録する一連のログが存在します。

MySQL には主に以下のログがあります。

• エラーログ: MySQL 操作中のエラー情報を記録します
• 一般クエリ ログ: クエリ、変更、更新などの各 SQL ステートメントを含む、MySQL が実行しているステートメントを記録します。
• スロークエリログ: クエリに多くの時間を費やすSQL文を記録します
• Binlog ログ: テーブルの作成、データの更新など、データ変更の記録を記録します。
  

これらのログは、my.cnf ファイルで設定する必要があります。mysql 設定ファイルのパスがわからない場合は、mysql コマンドを使用して見つけることができます。

mysql --verbose --help|grep -A 1 'Default options' #このコマンドは、my.cnf が検索するパスを順番に一覧表示します。

バイナリログ

Binlog は、テーブルの更新やレコードの更新、つまりデータ操作言語 (DML) を含むすべてのデータベース更新ステートメントを記録するバイナリ ログ ファイルです。Binlog は主に、データの回復とマスター スレーブ レプリケーションの構成に使用されます。

データ復旧: データベースが誤って削除されたり、予期せぬ事態が発生したりした場合でも、binlog を通じてデータを特定の時点に復元できます。マスタースレーブレプリケーション: データベースが更新されると、マスターデータベースはそれを binlog を通じて記録し、スレーブデータベースに更新を通知します。これにより、マスターデータベースとスレーブデータベースのデータの一貫性が確保されます。

MySQL は、その機能に応じて、サービス層モジュールとストレージ エンジン層モジュールに分かれています。サービス層は、クライアント接続や SQL 文処理の最適化などの操作を担当し、ストレージ エンジン層は、データの保存とクエリを担当します。Binlog は、サービス層モジュールのログに属し、つまり、エンジンに依存しません。データ エンジンのすべてのデータ変更は、Binlog ログに記録されます。データベースがクラッシュした場合、InnoDB エンジンが使用されている場合は、binlog ログで InnoDB REDO ログのコミット ステータスも確認できます。

Binlogログがオンになっています

ログ記録を有効にする方法:

1. 設定を追加する

ログビン=オン
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

2. log-binパラメータのみを設定する

 log-bin=/path/bin-log

binlog がオンになっている場合、MySQL は log_bin_index で指定された .index ファイルと複数のバイナリ ログ ファイルを作成します。インデックスには、MySQL が使用するすべての binlog ファイルが順番に記録されます。 binlog ログは、指定された名前 (またはデフォルト値) にサフィックスとして増分番号を追加して再構築されます (例: bin-log.000001)。binlog ログは、次の 3 つの状況が発生すると再構築されます。

ファイル サイズが max_binlog_size パラメータの値に達したら、flush logs コマンドを実行して MySQL サービスを再起動します。

Binlog ログ形式

binlog 形式は、binlog_format パラメータの値によって設定できます。オプションの値は、statement、row、mixed です。 * ステートメント形式: データベースによって実行された元の SQL ステートメントを記録します。 * 行形式: 特定の行の変更を記録します。これが現在のデフォルト値です。 * 混合形式: 上記の 2 つの形式にはそれぞれ長所と短所があるため、混合形式が登場します。

Binlog ログ表示ツール: mysqlbinlog

binlog はバイナリ ファイルであるため、他のファイルのように直接開いて表示することはできません。しかし、MySQL にはバイナリ ファイルを解析できる binlog 表示ツール mysqlbinlog が用意されています。もちろん、異なる形式のログの解析結果は異なります。1. ステートメント形式のログでは、mysqlbinlog /path/bin-log.000001 を実行すると、実行された元の SQL ステートメントを直接確認できます。2. 行形式のログでは、読みやすさはそれほど良くありませんが、パラメーター mysqlbinlog -v /path/bin-log.000001 を使用することで、ドキュメントを読みやすくすることができます。

mysqlbinlog の非常に重要なパラメータの 2 つのペア: 1. --start-datetime --stop-datetime は、特定の期間内の binlog を解析します。2. --start-position --stop-position は、2 つの位置間の binlog を解析します。

binlog を使用してデータを復元する

binlog を使用してデータを復元するということは、基本的に、binlog を通じてすべての DML 操作を見つけ、間違った SQL ステートメントを削除し、もう一度長い手順を実行してデータを復元することになります。

オフライン練習

1. データテーブルを作成し、初期値を挿入する

    テーブル「users」を作成します（
              `id` int(11) 符号なし NOT NULL AUTO_INCREMENT,
              `name` varchar(255) デフォルト NULL,
              `age` int(8) デフォルト NULL,
              主キー (`id`)
     )ENGINE=InnoDB デフォルト文字セット=utf8mb4;
     `users` (`id`, `name`, `age`) に挿入します
        価値観
            (null, '名前1', 5);

2. 最後の完全バックアップのデータベースとバイナリログの位置を見つけます (追記: もちろん、時間で復元することもできます)。ここでは、現在のステータスがバックアップの初期値として使用されます。

    mysqldump -uroot -p T > /path/xxx.sql; # データベースをバックアップします。 show master status; # 現在の位置 (154) を確認します。

3. 複数のレコードを挿入する

  `users` (`id`, `name`, `age`) に挿入します
  価値観
     (null, '名前2', 13),
     (null, '名前3', 14),
     (null, '名前4', 15),
     (null, '名前5', 16),
     (null、'名前6'、17);

4. 誤った操作を実行し、誤った操作の後にさらにいくつかのデータを挿入する

ユーザーを更新して age = 5 に設定します。
 `users` (`id`, `name`, `age`) に挿入します
 価値観
    (null, '名前7', 16),
    (null、'名前8'、18);

5. 誤った操作を発見したら、データ復旧を実行します。まず、MySQL の外部サービスを停止し、バックアップ データを使用して最後のデータに復元します。

6. mysqlbinlogコマンドを使用してバイナリファイルを分析し、

    エラーは位置 706 で発生し、最後の通常の操作は位置 513 で終了しました。
    1152から最後まで通常のSQL実行があります

7. mysqlbinlogコマンドを使用してbinlogログから実行可能なSQLファイルをエクスポートし、データをmysqlにインポートします。

 mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql;
 mysql -uroot -p < /path/bak.sql;

8. 誤った更新ステートメントをスキップし、その後の通常のステートメントを手順7のロジックで再実行して、データ回復作業を完了します。

まとめ

いつであっても、データベースのクラッシュはイライラさせ、不安をかき立てます。 Binlogは、さまざまな状況でデータベースがクラッシュしたり、データが失われたりした後の後悔の薬と言えます。この記事では、オフライン環境を通じてデータベースのデータ復旧実験を簡単に行います。何か問題があれば、アドバイスをお願いします。

上記は、binlog による MySQL データ復旧の詳細です。MySQL binlog データ復旧の詳細については、123WORDPRESS.COM の他の関連記事をご覧ください。