MySQL のデータの偶発的な削除の解決策と kill ステートメントの原則

mysql が誤ってデータを削除しました

削除ステートメントを使用して誤ってデータ行を削除する
誤ってデータテーブルを削除するには、drop table または truncate table を使用します。
drop database ステートメントを使用して誤ってデータベースを削除する
rm を使用して誤って mysql インスタンス全体を削除する

誤って削除された行

フラッシュバックツールを使用して、データをフラッシュバックして復元します。原則としては、binlogの内容を変更し、元のデータベースから取得して再生します。binlog_format=rowおよびbinlog_row_imsge=Fullであることを確認する必要があります。
具体的な回復時間
- 挿入の場合は、binlog イベントタイプを write_rows イベントから delete_rows イベントに変更します。
- 削除の場合はその逆になります。
- 更新の場合、binlog にはデータが変更される前と後の値が含まれます。この 2 行を入れ替えるだけです。
上記の原則に従って、複数の事柄も逆の順序で実行されます。
防止策: sql_safe_updates パラメータをオンに設定します。このように、delete または update ステートメントで where 条件を記述し忘れた場合、または where 条件にインデックスフィールドが含まれていない場合、このステートメントを実行するとエラーが報告されます。

誤って削除されたデータベース/テーブルの場合

完全バックアップと増分ログを使用する必要があります。定期的にオンラインで完全バックアップを実行し、バイナリログをリアルタイムでバックアップする必要がありますか?

誰かが午後 12 時に誤ってデータベースを削除した場合、データを復元するプロセスは次のようになります。

最新の完全バックアップを取得します。データベースは 1 日に 1 回バックアップされ、最後のバックアップは同じ日の 0:00 に行われたと仮定します。

バックアップから一時ライブラリを復元します。

ログバックアップから午前0時以降のログを取り出す

誤ってデータを削除するステートメントを除くすべてのログを一時データベースに適用します。

知らせ：

データの回復を高速化するために、この一時ライブラリに複数のデータベースがある場合は、mysqlbinlog コマンドを使用するときに –database パラメータを追加して、誤って削除されたテーブルが配置されているライブラリを指定できます。これにより、データを復元するときに他のライブラリログを適用する必要がなくなります。

ログを適用するときは、12 時の誤った操作を含むステートメントの binlog をスキップする必要があります。

リカバリを高速化する方法: 一時インスタンスをバックアップして復元した後、この一時インスタンスをオンラインバックアップデータベースのスレーブデータベースとして設定します。

システムで無制限にログをバックアップすることは不可能です。コストとディスク容量のリソースに基づいて、ログを保持する日数も設定する必要があります。 DBA チームが、インスタンスを半月以内の任意の時点に復元できることを保証できると言った場合、これはバックアップシステムが少なくとも半月分のログを保持することを意味します。

「誰もこんなことが起きてほしくない」とはいえ、誤って削除してしまった場合でも、すぐにデータを復元して損失を最小限に抑えることができるので、逃げる必要はありません。しかし、急いで手作業で作業を進め、ミスを犯してしまい、業務に二次的な損害を与えてしまうようなことがあっては困ります。

スタンバイデータベースの遅延レプリケーション

データベースのバックアップが特に大きい場合、または誤った操作と最後の完全バックアップの間の時間が長い場合、たとえば、週次バックアップのインスタンスで、バックアップの 6 日後に誤った操作が発生した場合、6 日分のログを復元する必要があり、リカバリ時間は日単位で計算される場合があります。
遅延レプリケーションスタンバイデータベースは、特別なスタンバイデータベースです。CHANGE MASTER TO MASTER_DELAY = N コマンドを使用すると、スタンバイデータベースがマスターデータベースに対して N 秒の遅延を維持するように指定できます。
たとえば、N を 3600 に設定すると、プライマリデータベースでデータが誤って削除され、誤った操作コマンドが 1 時間以内に検出された場合、そのコマンドは遅延レプリケーションスタンバイデータベースではまだ実行されていないことになります。このとき、スタンバイデータベースでstopslaveを実行し、先に紹介した方法を使用してエラーのある操作コマンドをスキップし、必要なデータを復元します。

rmでデータを削除する

クラスター全体が悪意を持って削除されず、ノードの 1 つのデータのみが削除されない限り、HA システムは動作を開始し、新しいマスターデータベースを選択して、クラスター全体の正常な動作を保証します。この時点では、このノード上のデータを復元し、それをクラスター全体に接続するだけです。

もちろん、現在では DBA だけでなく SA (システム管理者) も自動化システムを備えているため、マシンを一括してオフラインにする操作によって、MySQL クラスター全体のすべてのノードが消去される可能性があります。この状況に対処するための私の唯一のアドバイスは、バックアップをデータセンター間、できれば都市間で保存することです。 SQL文を強制終了する

セッション B はスレッドを直接終了し、何もせずに終了しますか?明らかに、これはうまくいきません。

テーブルを追加、削除、変更、またはクエリすると、テーブルに MDL 読み取りロックが追加されます。したがって、セッション B はブロックされた状態ですが、MDL 読み取りロックを保持し続けます。スレッドが強制終了された場合、スレッドは直接終了し、MDL 読み取りロックが解放される機会がなくなります。

Kill は、すぐに停止するという意味ではなく、このステートメントはもう実行する必要がなくなり、「実行停止ロジック」を開始できることを実行スレッドに伝えるという意味です。

実際、kill query thread_id_b を実行すると、MySQL で kill コマンドを処理するスレッドは次の処理を実行します。

セッションBの実行状態をTHD::KILL_QUERYに変更する
セッション B の実行スレッドにシグナルが送信されます。

図1の例では、セッションBはロック待機状態にあります。セッションBのスレッド状態を
THD::KILL_QUERY、スレッド B はこの状態の変化を認識せず、待機を続けます。シグナルを送信する目的は、セッション B を待機状態から終了させて、THD::KILL_QUERY 状態を処理することです。

上記には3つの意味が含まれています。

文の実行中には複数の埋め込みポイントがあります。スレッドの状態はこれらの「埋め込みポイント」で決定されます。スレッドの状態が見つかった場合
ステートメント終了ロジックを開始するのは THD::KILL_QUERY です。
待機状態の場合は、復帰可能な待機状態でなければなりません。そうでない場合は、「埋もれたポイント」までまったく実行されません。
終了ロジックに入るステートメントの開始から終了ロジックの完了までのプロセスがあります。

殺害の例

set global innodb_thread_concurrency=2 を実行して、InnoDB 同時スレッドの上限を 2 に設定し、次のシーケンスを実行します。

以下が見られます:

実行中にセッション C がブロックされました。

ただし、セッション D によって実行された kill query C コマンドは効果がありません。

セッション E が kill connection コマンドを実行するまで、セッション C は切断されず、「クエリ中に MySQL サーバーへの接続が失われました」というメッセージが表示されます。

しかしこの時点で、セッション E で show processlist を実行すると、次の図が表示されます。

id=12 のスレッドの Commnad 列に Killed と表示されます。つまり、クライアントが切断されていても、ステートメントはサーバー上で引き続き実行されます。

この例では、スレッド12の待機ロジックは次のようになります。10ミリ秒ごとに、InnoDB実行に入ることができるかどうかを確認します。
失敗した場合は、nanosleep 関数を呼び出してスリープ状態に入ります。

つまり、スレッド 12 のステータスは KILL_QUERY に設定されていますが、InnoDB に入るのを待つループ中にスレッドのステータスが判断されないため、終了ロジックステージにはまったく入りません。

セッション E が kill connection コマンドを実行すると、次の処理が実行されます。

スレッド 12 の状態を KILL_CONNECTION に設定します。
スレッド 12 のネットワーク接続を閉じます。この操作により、セッション C が切断プロンプトを受信することがわかります。

show processlist を実行すると、コマンド列が killed と表示されるのはなぜですか?実際、これは show processlist を実行するときに特別なロジックがあるためです。

スレッドの状態が KILL_CONNECTION の場合、[コマンド] 列には [Killed] と表示されます。

したがって、実際には、クライアントが終了したとしても、このスレッドのステータスはまだ待機中のままです。 InnoDB に入る条件が満たされ、セッション C のクエリステートメントが実行を継続した場合にのみ、スレッド状態が KILL_QUERY または KILL_CONNECTION になったと判断でき、終了ロジックステージに入ることができます。

kill が無効になる最初のタイプの状況は、スレッドがスレッドステータスを決定するロジックを実行していない場合です。また、過度の IO 負荷により、読み取りおよび書き込み IO 関数が返されず、スレッドの状態を時間内に判断できなくなる可能性もあります。

2番目のケースでは、終了ロジックに長い時間がかかります
実行中に非常に大きなトランザクションが強制終了され、ロールバック操作に長い時間がかかりました。
クエリ処理中に生成される大きな一時ファイルなどの大規模なローリング操作では、一時ファイルを削除するために IO リソースを待機する必要があり、結果として長い時間がかかります。
DDL が最終段階まで実行されて強制終了されると、中間プロセスの一時ファイルを削除する必要があり、これにも IO リソースが必要になります。

ctrl+C の場合、mysql は実際に接続プロセスを開始し、kill query コマンドを送信します。

低速クライアント接続に関する誤解

データベースにテーブルが多数ある場合、接続は遅くなります。たとえば、数万のテーブルを持つライブラリがある場合、デフォルトのパラメータを使用して接続すると、MySQL はローカルライブラリ名とテーブル名の補完機能を提供します。