MySQL誤操作後のデータの迅速なリカバリ方法

要旨:binlogを用いて誤操作データをフラッシュバックする.基本的には、データベースと付き合っているプログラマー(もちろん同僚かもしれません)は、MySQLが誤操作した後、どのようにして迅速にロールバックするかという問題に直面します.例えば、deleteは1枚の表で、制限条件を忘れて、1枚の表がなくなりました.もしこれがオンライン環境の核心業務データであれば、このことは大きくなります.誤操作後、データをすばやくロールバックできることが重要です.
従来の解法
フルバックアップでインスタンスを再ロードし、インクリメンタルbinlogバックアップを再利用して、誤操作前の状態に復元します.その後、誤操作のSQLをスキップし、binlogを適用し続けます.この方法は時間と労力がかかり,これ以上推薦する価値がない.
binlog 2 sqlによる高速フラッシュバック
まず、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を開くことを強くお勧めします.
次に、オープンソースツールbinlog 2 sqlをインストールします.binlog 2 sqlは簡単で使いやすいbinlog解析ツールで、その機能の一つはロールバックSQLを生成することです.

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

その後、ロールバックSQLを生成できます.
背景:明ちゃんは20時過ぎにtestライブラリ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、最新のbinlogファイルはmysql-binです.000052、誤操作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、誤操作sqlの正確な位置が728-938の間にあることを得て、更に位置によって更に濾過して、flashbackモードを使ってロールバック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 |
+----+--------+---------------------+

これで首になる心配はない.
よくある質問
DDLが誤って操作されたので、どうやって素早くロールバックするのかと聞かれます.例えばdropは大きな時計を持っています.
なかなかできません.rowモードでもDDL操作では1行あたりのデータの変化がbinlogに記録されないため、DDLはbinlogでロールバックできない.DDLロールバックを実現するには、DDLを実行する前に古いデータをバックアップする必要があります.確かにmysql serverのソースコードを修正することでDDLの高速ロールバックを実現した人がいて、アリのxiaobin linを見つけてpatchを提出しました.しかし、私の知っている限りでは、国内ではこの特性を応用しているインターネット会社は少ない.理由は、最も主要なのは怠け者だと思います.この低周波機能をする必要はありません.副次的な原因は、追加のストレージを増やすことです.
したがって、DDLが誤動作した場合は、バックアップによるリカバリのみが一般的です.もし会社がバックアップも使えなくなったら、本当に航空券を買うことをお勧めします.何してるの?走れ!
mysql binlog 2 sqlのほかに、ロールバックツールはありますか?
もちろんあります.アリ彭立勲はmysqlbinlogにflashbackの特性を追加した.これはmysqlが最初に持っていたflashback機能であるはずだ.彭はDMLのロールバックを解決し、binlogを利用してDMLフラッシュバックを行う設計構想を説明した.DDLロールバック特性もアリチームが提案し実現した.この2つの機能には革新的な精神があり、その後現れたフラッシュバックツールは基本的に上の2つの模倣である.また、どこでオープンソースのInceptionはMySQL自動化メンテナンスツールで、これは比較的重いです.DMLロールバックをサポートしています.binlogからロールバックしているわけではありません.バックアップロールバックからロールバックしています.DDLロールバックテーブル構造もサポートしています.データはロールバックできません.slaveロールバックという方法もあります.ビジネストラフィックを追加しないslaveを作って、わざと時間を遅らせます.これは実は伝統的な方法に基づいて、実例を除いてこのステップを回復します.この方法は機械を1台余分に消費するので、お勧めしません.
mysqlロールバック関連の優秀なツールの優秀な文章が漏れている場合は、お知らせください.
私のメールアドレス[email protected]
参考資料
[1]彭立勲、MySQLの下でフラッシュバックを実現する設計構想
[2] Lixun Peng, Provide the flashback feature by binlog
[3]丁奇、MySQLフラッシュバック方案の討論と実現
[4] xiaobin lin, flashback from binlog for MySQL
[5]王竹峰、どこへ行くの?
[6] danfengcao, binlog2sql: Parse MySQL binlog to SQL you want