MySQL の誤操作後にデータを迅速に回復する方法

Mysql リレーショナル データベース管理システム

MySQL は、スウェーデンの MySQL AB 社によって開発された、オープンソースの小規模リレーショナル データベース管理システムです。 MySQL は、インターネット上の小規模および中規模の Web サイトで広く使用されています。 MySQL は、サイズが小さく、速度が速く、総所有コストが低く、特にオープンソースの特性により、Web サイトの総所有コストを削減するために、多くの中小規模の Web サイトが Web サイト データベースとして MySQL を選択しています。

この記事では主に、MySQL の誤操作後にデータを迅速に回復する方法を紹介します。必要な友人は参考にしてください。

要約: binlog を使用して誤操作データをフラッシュバックします。
基本的に、データベースを扱うすべてのプログラマー (もちろん、同僚かもしれません) は、MySQL の誤操作後に素早くロールバックする方法という問題に遭遇するでしょう。たとえば、テーブルを削除して制限を追加するのを忘れた場合、テーブル全体が失われます。これが依然としてオンライン環境の中核となるビジネスデータである場合、この問題は大きな問題になります。誤操作後にデータを迅速にロールバックできることは非常に重要です。

従来のソリューション

完全バックアップを使用してインスタンスを再作成し、増分バイナリログバックアップを使用して誤操作前の状態に復元します。その後、誤操作 SQL をスキップして、引き続き binlog を使用します。この方法は時間と労力がかかるため、お勧めできません。

すぐにフラッシュバックするには、binlog2sql を使用します

まず、MySQL サーバーで binlog がオンになっていることを確認し、次のパラメータを設定します:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row

binlog がオンになっておらず、ロールバック SQL が事前に生成されていない場合は、すぐにロールバックすることは実際には不可能です。重要なビジネス データを保存する MySQL の場合は、binlog を有効にすることを強くお勧めします。

次に、オープンソース ツール binlog2sql をインストールします。 binlog2sql は、シンプルで使いやすい binlog 解析ツールです。その機能の 1 つは、ロールバック SQL を生成することです。

shell> git clone https://github.com/danfengcao/binlog2sql.git
shell> pip install -r requirements.txt

これで、ロールバック SQL を生成できます。

背景: Xiao Ming は、20 時頃にテスト ライブラリ内の TBL テーブル全体のデータを誤って削除してしまい、緊急のロールバックが必要になりました。

test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 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 rows in set (0.00 sec)

mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)

20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)

データを復元する手順:

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

mysql> show master status;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000051 |    967 |
| mysql-bin.000052 |    965 |
+------------------+-----------+

2. 誤操作 SQL の binlog の場所を見つけます。ミスをした人はおおよその操作時間を知ることしかできず、おおよその時間に基づいてデータをフィルタリングします。

shell> 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'
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 4 end 290 time 2016-12-13 20:25:46
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; 
#start 514 end 701 time 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; #start 728 end 938 time 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`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. 728 ～ 938 の間で誤操作 SQL の正確な位置を取得し、その位置に基づいてさらにフィルタリングし、フラッシュバック モードを使用してロールバック SQL を生成し、ロールバック SQL が正しいかどうかを確認しました

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

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

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052'
 --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'

mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 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 |
+----+--------+---------------------+

この時点で、もうクビになることを心配する必要はありません。

FAQ

「誤って DDL 操作をすぐにロールバックするにはどうすればよいですか?」と疑問に思う人もいるかもしれません。たとえば、大きなテーブルが削除されます。

それは難しいです。行モードであっても、DDL 操作ではデータの各行の変更がバイナリログに記録されないため、バイナリログを通じて DDL をロールバックすることはできません。 DDL ロールバックを実装するには、DDL を実行する前に古いデータをバックアップする必要があります。実際、mysql サーバーのソース コードを変更することで DDL の高速ロールバックを実現した人がいます。私は Alibaba の xiaobin lin を見つけてパッチを送信しました。しかし、私の知る限り、この機能を適用している国内のインターネット企業はほとんどありません。理由としては、私が面倒だからこの低周波機能を使う必要がないということが主な理由だと思います。第二の理由は、追加のストレージが追加されるということです。

そのため、DDL が悪用された場合、通常はバックアップによってのみ復元できます。会社がバックアップを使用できなくなった場合は、航空券を購入することを強くお勧めします。何をするか？ Run

mysql binlog2sql 以外にロールバック ツールはありますか?

もちろんです。 Ali Peng Lixun は、mysql の最初のフラッシュバック機能である mysqlbinlog にフラッシュバック機能を追加しました。Peng は DML ロールバックの問題を解決し、DML フラッシュバックに binlog を使用する設計アイデアを説明しました。 DDL ロールバック機能も Alibaba チームによって提案および実装されました。この 2 つの機能は革新的であり、その後登場したフラッシュバック ツールは基本的に上記 2 つの機能を模倣したものです。さらに、Qunar のオープン ソースの Inception は、比較的重い、binlog からではなく、バックアップからの DML ロールバックをサポートしています。データはロールバックできません。 ~ スレーブ遅延バックアップと呼ばれる別の方法もあります。これには、ビジネス トラフィックを追加しないスレーブを設定し、一定期間意図的に遅延させることが含まれます。これにより、従来の方法に基づくインスタンスの回復手順が実際に不要になります。この方法は追加のマシンを消費するため、お勧めできません。

mysql ロールバックに関連する優れたツールや優れた記事が不足している場合は、お知らせください。

上記は、MySQL の誤動作後にデータを迅速に回復する方法の内容です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。