MySQLエラー後のデータの迅速なリカバリ方法

12762 ワード
データベース
基本的には、データベースと付き合っているプログラマー(もちろん同僚かもしれません)は、MySQLが誤操作した後、どのようにして迅速にロールバックするかという問題に直面します.例えば、うっかりupdateがテーブル全体のフィールドを更新したり、deleteがテーブルを1枚作ったりして、制限条件を忘れたりして、テーブル全体がなくなりました.もしこれがオンライン環境の核心業務データであれば、このことは大きくなります.誤操作後、データをすばやくロールバックできることが重要です.
従来の解法
フルバックアップでインスタンスを再ロードし、インクリメンタルbinlogバックアップを再利用して、誤操作前の状態に復元します.その後、誤操作のSQLをスキップし、binlogを適用し続けます.DMLのロールバックには時間と労力がかかり,これ以上推奨する価値はない.
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)

データを復元するには、次の手順に従います.
  • mysqlにログインし、現在のbinlogファイル
    mysql> show master status;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000051 |       967 |
| mysql-bin.000052 |       965 |
+------------------+-----------+
    • を表示します.
  • 最新の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
  • 誤操作sqlの正確な位置が728-938の間にあることを得て、更に位置によって濾過して、flashbackモードを使ってロールバックsqlを生成して、ロールバックsqlが正しいかどうかを検査します(注:実際の環境の下で、このステップはいつも更に必要なsqlをスクリーニングします.grep、編集器などを結合します)
    shell> 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
  ：
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> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < rollback.sql

 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台余分に消費するので、お勧めしません.
    • gccコンパイル:いくつかパッケージする.o和.aファイルは新しいです.aファイル
    leetcode 1031. 2つの非オーバーラップサブ配列の最大和(C++)