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

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 を応援していただきありがとうございます。

以下もご興味があるかもしれません:
  • MySQL の Binlog 関連コマンドとリカバリテクニック
  • MySQL バイナリログデータ復旧: 誤ってデータベースを削除した場合の詳細な説明
  • MySQLデータベースのログファイル(binlog)を自動的に復元する方法を説明します
  • MySQLはデータ復旧を実装するためにbinlogログを使用する
  • MySQL で binlog を介してデータを復元する方法

<<:  LinuxでHomebrewを使用する正しい方法

>>:  Ant Design Pro ログイン機能にグラフィック検証コード コンポーネントを統合する方法

推薦する

Vue フォーム入力バインディング v-model

目次1.vモデル2. プロパティとイベントのバインディング3. フォーム要素のバインディング3.1 ...

Ubuntu Server 16.04 MySQL 8.0 のインストールと設定のグラフィックチュートリアル

Ubuntu Server 16.04 MySQL 8.0 のインストールと設定のグラフィックチュー...

組み込み移植 docker エラー問題 (概要)

長い移植と情報検索の期間を経て、組み込みDockerの問題を解決することができました。インターネット...

よくある CSS のヒントと経験談 11 選

1. 画像の下にある数ピクセルの空白を削除するにはどうすればよいですか?コードをコピーコードは次のと...

Nginx をベースに特定の IP への短期アクセス数を制限する

特定の期間内に特定の IP へのアクセス回数を制限する方法は、特に悪意のある DDOS 攻撃に直面し...

Golang を使って Docker API を実際に操作する

目次SDKのインストールローカル Docker の管理コンテナの実行バックグラウンドでコンテナを実行...

Linux における mv コマンドの高度な使用例

序文mv コマンドは、move の略語で、ファイルを移動したり、ファイル名を変更したり (ファイルの...

Dockerプライベート倉庫の構築と利用の詳細説明

イメージは hub.docker.com に保存できますが、ネットワーク速度が比較的遅いです。内部環...

Node.jsを理解するのはとても簡単です

目次Node.js の公式紹介Node.jsのコア開発言語ウェブ上の JavaScript と No...

MySQL 8.0.12 クイックインストールチュートリアル

MySQL 8.0.12 のインストールには 2 日かかり、さまざまな問題が発生しました。以下にまと...

v-html レンダリング コンポーネントの問題

以前 HTML を解析したことがあるので、今日は Vue ドラッグ アンド ドロップを使用して、Ku...

mysql のインデックスと FROM_UNIXTIME に関する問題

ゼロ、背景今週の木曜日にたくさんのアラートを受け取りました。DBA に確認を依頼したところ、遅いクエ...

MySQLデータベースを誤って削除した後にデータを回復するための手順

日々の運用・保守作業において、MySQL データベースのバックアップは重要です。ウェブサイトにとって...

Vueは単一ファイルコンポーネントの完全なプロセス記録を実装します

目次序文単一ファイルコンポーネント基本概念シンプルなローダーコンポーネントコンテンツの解析コンポーネ...

ネイティブ js はカスタム スクロール バー コンポーネントを実装します

この記事の例では、カスタムスクロールバーコンポーネントを実装するためのjsの具体的なコードを参考まで...