MySQL データを誤って削除した場合の簡単な解決策 (MySQL フラッシュバックツール)

概要

Binlog2sql は、Python で開発されたオープンソースの MySQL Binlog 解析ツールです。Binlog を元の SQL に解析できます。また、Binlog をロールバック SQL に解析し、主キーの INSERT SQL を削除することもサポートしています。これは、DBA や運用保守担当者がデータを回復するための優れたヘルパーです。

1. インストールと設定

1.1 目的

高速データロールバック（フラッシュバック）
マスタースレーブ切り替え後の新しいマスターでの失われたデータの修復 binlog から標準 SQL を生成し、派生関数は MySQL 5.6 および 5.7 をサポートします。

1.2 インストール

シェル> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
シェル> pip インストール -r requirements.txt

2. 使用方法

2.1 使用前の設定

2.1.1 パラメータ設定

[mysqld] 
サーバーID = 1 
log_bin = /var/log/mysql/mysql-bin.log 
最大バイナリログサイズ = 1G 
binlog_format = 行 
binlog_row_image = フル

2.1.2 ユーザーに必要な最小限の権限セット

選択、スーパー/レプリケーション クライアント、レプリケーション スレーブ

推奨される承認

選択、スーパー/レプリケーション クライアント、レプリケーション スレーブ

権限

select: サーバー側でinformation_schema.COLUMNSテーブルを読み取り、テーブル構造のメタデータを取得し、それを視覚的なSQL文に組み込む必要があります。
スーパー/レプリケーションクライアント: 両方の権限が利用可能です。サーバー側で binlog リストを取得するには、「SHOW MASTER STATUS」を実行する必要があります。
レプリケーションスレーブ: BINLOG_DUMP プロトコルを介してコンテンツをバイナリログする権限を取得します。

2.2 基本的な使い方

2.2.1 基本的な使い方

標準SQLを解析する

シェル> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'

出力：

`test`.`test3`(`addtime`, `data`, `id`) に VALUES ('2016-12-10 13:03:38', 'english', 4) を挿入します。#開始 570 終了 736 
`test`.`test3` を更新し、`addtime`='2016-12-10 12:00:00'、`data`='中文'、`id`=3 を設定します。WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954 
`test`.`test3` から削除します。WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

ロールバックSQLを解析する

シェル> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

出力：

`test`.`test3`(`addtime`, `data`, `id`) に VALUES ('2016-12-10 13:03:38', 'english', 4) を挿入します。#開始 981 終了 1147 
`test`.`test3` を更新し、`addtime`='2016-12-10 13:03:22'、`data`='中文'、`id`=3 を設定します。WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

2.2.2 オプション

mysql接続構成

-h ホスト; -P ポート; -u ユーザー; -p パスワード

解析モード

--stop-never binlog の解析を続行します。オプション。デフォルト値は False で、コマンドが実行されたときに最新の binlog 位置に同期することを意味します。

-K, --no-primary-key INSERT ステートメントから主キーを削除します。オプション。デフォルト: False

-B、--flashback はロールバック SQL を生成し、大きなファイルを解析でき、メモリによって制限されません。オプション。デフォルトは False です。 stop-never または no-primary-key と同時に追加することはできません。

--back-interval -B モードでは、ロールバック SQL 行が 1,000 行出力されるたびに、一定秒数の SLEEP ステートメントを追加します。SLEEP を追加しない場合は、0 に設定してください。オプション。デフォルトは1.0です。

解像度範囲制御

--start-file 開始解析ファイルにはファイル名のみが必要で、完全なパスは必要ありません。しなければならない。

--start-position/--start-pos 解析開始位置。オプション。デフォルトは start-file の開始位置です。

--stop-file/--end-file ファイルの解析を停止します。オプション。デフォルトは start-file と同じファイルです。解析モードが stop-never の場合、このオプションは効果がありません。

--stop-position/--end-pos 解析位置を停止します。オプション。デフォルトは停止ファイルの最後の位置です。解析モードが stop-never の場合、このオプションは無効です。

--start-datetime 解析開始時刻。形式は '%Y-%m-%d %H:%M:%S' です。オプション。デフォルトではフィルタリングはありません。

--stop-datetime 時間の解析を停止します。形式は '%Y-%m-%d %H:%M:%S' です。オプション。デフォルトではフィルタリングはありません。

オブジェクトフィルタリング

-d, --databases は、ターゲット db の SQL のみを解析します。複数のデータベースは、-d db1 db2 のようにスペースで区切られます。オプション。デフォルト値は空です。

-t、--tables は、対象テーブルの SQL のみを解析します。複数のテーブルは、-t tbl1 tbl2 のようにスペースで区切られます。オプション。デフォルト値は空です。

--only-dml dml のみを解析し、ddl は無視します。オプション。デフォルトは False です。

--sql-type は指定されたタイプのみを解析し、INSERT、UPDATE、および DELETE をサポートします。複数のタイプは、--sql-type INSERT DELETE のようにスペースで区切られます。オプション。デフォルトでは、すべての追加、削除、変更を解析します。このパラメータが使用されてもタイプが入力されていない場合は、3 つのいずれも解析されません。

2.3 適用事例

2.3.1 テーブルデータ全体が誤って削除され、緊急ロールバックが必要になった

フラッシュバックの詳細な紹介については、サンプルディレクトリ example/mysql-flashback-priciple-and-practice.md の「フラッシュバックの原理と実践」を参照してください。

テストライブラリのtblテーブルの元のデータ

mysql> テーブルから * を選択します。 
+----+--------+---------------------+ 
| ID | 名前 | 追加時刻 | 
+----+--------+---------------------+ 
| 1 | シャオ・ジャオ | 2016-12-10 00:04:33 | 
| 2 | 小千 | 2016-12-10 00:04:48 | 
| 3 | シャオサン | 2016-12-13 20:25:00 | 
| 4 | シャオ・リー | 2016-12-12 00:00:00 | 
+----+--------+---------------------+ 
セット内の 4 行 (0.00 秒) 
 
mysql> テーブルから削除します。 
クエリは正常、4 行が影響を受けました (0.00 秒) 
 
20:28 に、エラーのため tbl テーブルがクリアされました。mysql> select * from tbl; 
空のセット (0.00 秒)

データを復元する手順:

1. mysqlにログインし、現在のbinlogファイルを表示します。

mysql> マスターステータスを表示します。 
+------------------+-----------+ 
| ログ名 | ファイルサイズ | 
+------------------+-----------+ 
|mysql-bin.000051 | 967 | 
|mysql-bin.000052 | 965 | 
+------------------+-----------+

2. 最新の binlog ファイルは mysql-bin.000052 です。不正な SQL 操作の binlog の場所を見つけましょう。ミスを犯した人はミスが起きたおおよその時間しか知らないので、おおよその時間に基づいてデータをフィルタリングします。

シェル> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'

出力：

`test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, 'Xiao Li') に INSERT します。#開始 317 終了 487 時間 2016-12-13 20:26:26 
`test`.`tbl` を更新し、`addtime`='2016-12-12 00:00:00'、`id`=4、`name`='Xiao Li' を設定します。WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='Xiao Li' LIMIT 1; #開始 514 終了 701 時間 2016-12-13 20:27:07 
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #開始 728 終了 938 時間 2016-12-13 20:28:05 
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05 
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05 
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='Xiao Li' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. 不正な SQL の正確な位置は 728 から 938 の間であることがわかりました。さらに位置に基づいてフィルタリングし、フラッシュバックモードを使用してロールバック SQL を生成し、ロールバック SQL が正しいかどうかを確認します (注: 実際の環境では、この手順で必要な SQL をさらにフィルタリングすることがよくあります。grep、エディターなどと組み合わせて使用します)。

シェル> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat

出力：

`test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, 'Xiao Li') に INSERT します。#開始 728 終了 938 時間 2016-12-13 20:28:05 
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #開始 728 終了 938 時間 2016-12-13 20:28:05 
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #開始 728 終了 938 時間 2016-12-13 20:28:05 
`test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, 'Xiao Zhao') に INSERT INTO します。#開始 728 終了 938 時間 2016-12-13 20:28:05

4. ロールバック SQL が正しいことを確認し、ロールバックステートメントを実行します。 MySQL にログインして、データのロールバックが成功したことを確認します。

シェル> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < ロールバック.sql 
 
mysql> テーブルから * を選択します。 
+----+--------+---------------------+ 
| ID | 名前 | 追加時刻 | 
+----+--------+---------------------+ 
| 1 | シャオ・ジャオ | 2016-12-10 00:04:33 | 
| 2 | 小千 | 2016-12-10 00:04:48 | 
| 3 | シャオサン | 2016-12-13 20:25:00 | 
| 4 | シャオ・リー | 2016-12-12 00:00:00 | 
+----+--------+---------------------+

結論

3.1 制限事項（mysqlbinlog との比較）

MySQL サーバーを起動する必要があります。オフラインモードでは解析できません。

パラメータ binlog_row_image は FULL である必要があります。MINIMAL はまだサポートされていません。

解析速度はmysqlbinlogほど速くない

3.2 利点（mysqlbinlog と比較）

純粋なPython開発、インストールと使用が簡単

フラッシュバックと主キーなしの解析モードが組み込まれているため、パッチのインストールは不要です。

フラッシュバックモードでは、フラッシュバック戦闘に適しています。

標準SQLに解析して簡単に理解し、スクリーニングできる

コードは簡単に変更でき、よりパーソナライズされた分析をサポートできます。

要約する

上記は、編集者が紹介した MySQL データの偶発的な削除の問題に対する簡単な解決策です。皆様のお役に立てれば幸いです。ご質問がある場合は、メッセージを残してください。編集者がすぐに返信します。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。
この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

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