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 ログイン機能にグラフィック検証コード コンポーネントを統合する方法

推薦する

HTMLページが3秒後に自動的にジャンプする3つの一般的な方法

実際には、N 秒後にページを自動的にジャンプさせるにはどうすればよいかという問題によく遭遇します。私...

HTML テーブルの境界線を設定する際のヒント

HTML を初めて使用する多くの人にとって、テーブル <table> は最もよく使用され...

Vue Element フロントエンドアプリケーション開発 従来の Element インターフェースコンポーネント

目次1. リストインターフェースとその他のモジュールの表示処理2. 従来のインターフェースコンポーネ...

Vuexはシンプルなショッピングカートを実装します

この記事では、参考までに、Vuex の具体的なコードを共有して、簡単なショッピングカートを実装します...

Vue で PC 解像度の適応を実装するためのサンプルコード

目次プラン依存関係をインストールする依存関係の導入pxをremに変換するFlexible.jsを変更...

MySQL/MariaDB ルートパスワードリセットチュートリアル

序文パスワードを忘れることは、よく遭遇する問題です。MySQL または MariaDB データベース...

React サーバーサイドレンダリング原則の分析と実践

ほとんどの人は、サーバーサイド レンダリング (SSR と呼んでいます) の概念について聞いたことが...

Javascript で SessionStorage と LocalStorage を使用する方法

目次序文SessionStorage と LocalStorage の紹介SessionStorag...

MYSQL 左結合の最適化 (10 秒から 20 ミリ秒)

目次【機能背景】 [生のSQL] 【独自SQL解析】 【分析手順】 [最適化されたSQL] 【最適化...

MySQL でコミットされていないトランザクション情報を見つける方法

少し前に、「ORACLE でコミットされていないトランザクションの SQL ステートメントを見つける...

dockerでビルドしたnacos1.3.0の実装

1. nacosデータベースを再開します。データベース名nacos_configユーザー名とパスワー...

Linux 環境で crontab コマンドを使用して、スケジュールされた定期的な実行タスクを設定します (PHP 実行コードを含む)

この記事では、Linux 環境で crontab コマンドを使用して、タスクの定期的な実行をスケジュ...

MySQL 8.0.14 のインストールと設定方法のグラフィックチュートリアル (一般)

MySQLサービス8.0.14のインストール(一般)の参考までに、具体的な内容は次のとおりです。イ...

AngularでTweenMaxアニメーションライブラリを使用する際の問題と解決策

最近何もすることがないのでCSSをいじっていますより良いアニメーションライブラリTweenMaxを見...

MySQLはデフォルトのエンジンと文字セットの詳細を変更します

目次1. データベースエンジン1.1 ビューデータベースエンジン1.2 デフォルトのデータベースエン...