MySQL でデータ復旧に binlog を使用する方法

序文

最近、オンラインでデータが誤って操作されました。データベースが直接変更されたため、それを回復する唯一の方法は MySQL binlog にあります。 Binlog は ROW モードを使用します。つまり、影響を受けるレコードごとに SQL ステートメントが生成されます。 binlog2sql プロジェクトも使用されます。

MySQL バイナリログ (bin-log とも呼ばれる) は、MySQL 実行の変更によって生成されるバイナリログファイルです。主な機能は 2 つあります。

* データ返信

* マスタースレーブデータベース。スレーブ側で追加、削除、変更を実行し、マスターとの同期を維持するために使用されます。

binlogの基本設定とフォーマット

binlogの基本設定

Binlog は、mysql 構成ファイルの mysqld ノードで構成する必要があります。

# ログ内のサーバーID
サーバーID = 1
# ログパス log_bin = /var/log/mysql/mysql-bin.log
# ログを保存する日数 expire_logs_days = 10
# 各バイナリログのサイズ max_binlog_size = 1000M
#binlgo モード binlog_format=ROW
# デフォルトではすべてのレコードですが、記録する必要があるレコードと記録しないレコードを設定できます #binlog_do_db = include_database_name
#binlog_ignore_db = 含めるデータベース名

バイナリログのステータスを表示する

バイナリログを表示; バイナリログファイルを表示
ログステータスを表示するには、「%log_bin%」のような変数を表示します。
SHOW MASTER STATUS ログファイルの場所を表示する

binlogの3つの形式

1.行

行ログの場合、行の変更ごとにレコードが生成されます。

利点: コンテキスト情報は比較的完全です。誤った操作から回復する場合、元の情報はログで直接見つかります。マスタースレーブレプリケーションが適切にサポートされています。

デメリット: 出力が非常に大きく、Alter文の場合は大量のレコードが生成されます。

形式は次のとおりです。

「back」から削除します。ここで、「deptid」= 27および `status` = 1 and` account` = '=' = '=' = '=' 18200000000 'および `leolid` =' 1 'および` createTime` =' 2016-01-29 「誕生日」= '2017-05-05 00:00:00'および `avatar` = 'girl.gif' = 25および` password` = 'ecfadcde9305f8891bcfe5a1e28c253e'および `salt` = '8pgby'および `did` = 1 lime onimit 1

2.声明

SQL文の場合、各文はレコードを生成する。

利点: 生成されるログの量は比較的少なく、マスターとスレーブのバージョンが一致しない場合があります。

デメリット: 自動インクリメント主キーや UUID など、マスターとスレーブの関係における一部のステートメントはサポートされません。

形式は次のとおりです。

`sys_role` から削除します。

3.ミックス

両方の利点を組み合わせたものです。一般的には、STATEMENT モードが使用され、サポートされていないステートメントには ROW モードが使用されます。

SQLに変換する

mysqlbinlog

binlog はバイナリなので、まずはテキストファイルに変換する必要があります。通常は、MySQL に付属している mysqlbinlog を使用してテキストに変換します。

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2018-10-10

パラメータの説明

--no-defaults エラーを防ぐには: mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
--base64-output='decode-rows' は、-v と一緒に使用され、base64 デコードを実行します。データベース、開始時間、開始位置など、範囲を制限するために使用される他の多くのパラメータがあります。これらのパラメータはトラブルシューティング時に非常に役立ちます。

binlog の基本ブロックは次のとおりです。

# 417750 で
#181007 1:50:38 サーバー ID 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd クエリ thread_id=440109962 exec_time=0 error_code=0
タイムスタンプを 1538877038/*!*/ に設定します。
始める

1. 417750 の #

ファイルの先頭からの現在の位置のオフセットを指定します。これは、mysqlbinlog コマンドの --start-position パラメータとして使用できます。

2. #181007 1:50:38 サーバー ID 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd クエリ thread_id=440109962 exec_time=0 error_code=0

181007 1:50:38 は、時刻が 2018 年 10 月 7 日の 1:50:38 であることを示します。Serverid は、構成ファイルで構成したものです。End_log_pos 417844 は、このブロックが 417844 で終了することを意味します。 thread_id: 実行のスレッド ID、exec_time: 実行時間、error_code: エラーコード

3. TIMESTAMP=1538877038/!/; を設定します。

始める

具体的な実行ステートメント

1行のレコードで生成されるログは次のようになります。

# 417750 で
#181010 9:50:38 サーバー ID 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd クエリ thread_id=440109962 exec_time=0 error_code=0
タイムスタンプを 1539136238/*!*/ に設定します。
始める
//*!*/;
# 417844 で
#181010 9:50:38 サーバー ID 1630000 end_log_pos 417930 CRC32 0xce36551b Table_map: `goods`.`good_info` が番号 129411 にマップされました
# 417930 で
#181010 9:50:38 サーバー ID 1630000 end_log_pos 418030 CRC32 0x5827674a Update_rows: テーブル ID 129411 フラグ: STMT_END_F
### `goods`.`good_info` を更新します
＃＃＃どこ
### @1='2018:10:07' /* 日付 meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
＃＃＃セット
### @1='2018:10:07' /* 日付 meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# 418030 で
#181010 9:50:38 サーバー ID 1630000 end_log_pos 418061 CRC32 0x468fb30e Xid = 212760460521
専念 /*！*/;
# 418061 で

1 行のレコードによって生成されたログが上記に表示されます。 SET TIMESTAMP=1539136238/*!*/;で開始し、 COMMIT/*!*/;で終了します。 2 つの at で示される場所に基づいて範囲を制限できます。

レコードの先頭の SET TIMESTAMP の前の 417750 の # と、末尾の COMMIT の後の 418061 の # に注意してください。

binlog2sql の使用

binlog2sql 公式サイトの紹介: MySQL binlog から必要な SQL を解析します。オプションに応じて、元の SQL、ロールバック SQL、主キーのない INSERT SQL などを取得できます。

基本的な使い方は以下のとおりです。

python binlog2sql.py -hlocalhost -P3306 -udev -p'\*' -d room -t room_info --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

具体的な使い方はGitHubで非常にわかりやすく説明されているのでここでは説明しません。主に、開始時間と終了時間 --start-datetime/--stop-datetime、テーブル名制限 -t、データベース制限 -d、ステートメント制限 --sql-type など、フィルタリングに使用される多くの条件を見ることができます。ここでは主に、私が遭遇したいくつかの問題についてお話します。

mysql バイナリログモード

ここでは、ROW モードに元の情報が含まれているため、ROW に設定する必要があります。binlog2sql を直接使用してロールバック SQL を逆生成できる場合、STATEMENT を生成できない場合は、MySQL のスケジュールされたバックアップファイルを使用してロールバックを行う必要があります。

データを復元するための具体的な操作

当時オンラインで実行されていたのは更新文であり、一意のキーインデックスがなかったためです。その結果、2,000 件を超えるレコードが更新されました。声明は次のとおりです。

room_info を更新し、status=1 を status=2 に設定します。

まず、操作時間に応じて対応する binlog ファイルを探します。操作時間は午前 9 時頃だったと記憶しているので、最終変更時間が 9 時以降で最も近い時間である対応する binlog ファイルを探します。ファイルの変更時刻を表示するには、Linux の ll コマンドを使用します。
特定のデータベースをフィルタリングする MySQL インスタンスのすべての binlog ファイルは 1 つのファイルにあるため、最初に閉じたくない他のデータベースを削除する必要があります。データインスタンスを指定するには、-d パラメータを使用します。次に、開始時刻（--start-datetime）と終了時刻（--stop-datetime）を使用してさらにフィルタリングします。

mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d room --start-datetime='2018-10-10 9:00:00' --stop-datetime='2018-10-10 10:00:00' mysql-bin.011012>temp.sql

圧縮検索ファイル解析

zip temp.zip temp.sql && sz temp.zip

ファイルを取得して、正規表現マッチング機能を持つ vscode などのテキストツールを使用してローカルで分析します。変更した機能に基づいて、たとえば、変更してはならない部屋番号 888888 があります。この部屋番号の変更記録を確認できます。ROW モードのステートメントは、Where first と set second です。正規表現room_id=888888.*show_state=1.*AND show_state=2すぐに一致させることができます。当時の私のステートメントは 2,000 件を超えるレコードに影響を与えました。見つけたステートメントに基づいて、開始SET TIMESTAMP=1539136238の前の at と終了 COMMIT の後の at を見つけます。

binlog2sql を使用してロールバックステートメントを生成する

python binlog2sql.py -hlocalhost -P3306 -udev -p'*' -d room -t room_info -B --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

加えて

ここでは、1 回の更新が複数のレコードに影響する状況があります。ユニークキーの場合は、1 つのレコードのみが影響を受けます。それほど面倒なことはありません。binlog2sql を -d および -t パラメータ付きで使用してデータベースとテーブルを制限し、grep を使用して検索するだけで、対応する SQL を直接取得できます。 mysqlbinlog には、テーブルを制限したりステートメントを制限する機能がありません。たとえば、テーブルに正確な Delete ステートメントを使用すると、大量のデータを削減でき、すばやく見つけることができます。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

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