MySQLはmysqldump+binlogを使用して、削除されたデータベースの原理分析を完全に復元します。

1. 概要

MySQL データベースの日常的な操作とメンテナンスにおいて、ユーザーが誤ってデータを削除することがあります。誤ってデータを削除する一般的な操作は次のとおりです。

ユーザーが削除を実行し、不正な条件により削除すべきでないデータを削除します (DML 操作)。
ユーザーが更新を実行し、条件が正しくないために更新データが失敗します (DML 操作)。
ユーザーが誤ってテーブル drop table (DDL 操作) を削除した場合。
ユーザーが誤ってテーブルを空にして切り捨てます (DDL 操作)。
ユーザーはデータベースを削除してデータベースをドロップし、逃げる (DDL 操作)
…待って

このような状況は頻繁に発生するわけではありませんが、発生した場合には、回復できる必要があります。その方法については、以下で説明します。

（II）修復原則

データベースを障害発生前の時点に復元する場合は、データベースの完全バックアップと、完全バックアップ後に生成されたすべてのバイナリログが必要です。

完全バックアップ機能: 完全バックアップを使用して、データベースを最後の完全バックアップの場所に復元します。

バイナリログの機能: フルバックアップセットを使用してデータベースを最後のフルバックアップの場所に復元した後、最後のフルバックアップ後にデータベースで発生したすべてのアクションをやり直す必要があります。やり直しプロセスでは、バイナリログファイルを SQL ステートメントに解析し、データベースに入れて再度実行します。

たとえば、Xiao Ming は 4 月 1 日の午後 8 時に mysqldump を使用してデータベースをバックアップしました。4 月 2 日の午前 12 時に、Xiao Hua は誤ってデータベースを削除しました。その後、データベースのリカバリを実行するときに、4 月 1 日の夜に取得した完全バックアップを使用して、データベースを「4 月 1 日の午後 8 時」に復元する必要があります。4 月 1 日の午後 8 時以降から 4 月 2 日の午前 12 時までのデータを復元するにはどうすればよいでしょうか。この期間中に実行された SQL をやり直すには、バイナリログを解析する必要があります。

（III）削除データベースの回復テスト

（3.1）実験目的

今回の実験では、データベースの削除を直接テストし、drop database lijiamandb を実行して復元できるかどうかを確認しました。

（3.2）テストプロセス

テストデータベース lijiamandb にテストテーブル test01 と test02 を作成し、mysqldump を実行してデータベースを完全に準備してから、drop database を実行してデータベースを復元できるかどうかを確認します。

ステップ 1:テストデータを作成します。忙しい日常の運用環境をシミュレートするために、頻繁なデータベース操作によって大量のバイナリログが生成されます。特に、大量のデータを生成するためにストアドプロシージャとイベントを使用します。

テストテーブルを作成します。

lijiamandb を使用し、テーブル test01 を作成します。
 （
 id1 int NULLではない auto_increment、
 名前varchar(30),
 主キー(id1)
 );

テーブル test02 を作成する
 （
 id2 int NULLではない auto_increment、
 名前varchar(30),
 主キー(id2)
 );

テストテーブルにデータを挿入するためのストアドプロシージャを作成します。ストアドプロシージャが実行されるたびに、test01 と test02 にそれぞれ 10,000 件のレコードが挿入されます。

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`()
始める
#ルーチン本体はここに記述します...
str1 varchar(30)を宣言します。
str2 varchar(30)を宣言します。
i int を宣言します。
i = 0 に設定します。

i < 10000 の場合
 str1 = substring(md5(rand()),1,25) を設定します。
 test01(name) に values(str1) を挿入します。
 str2 = substring(md5(rand()),1,25) を設定します。
 test02(name) に values(str1) を挿入します。
 i = i + 1 と設定します。
 終了しながら;
 終わり

上記のストアドプロシージャを 10 秒ごとに実行するイベントを作成します。

lijiamandb を使用します。
 存在しない場合はイベントを作成する e_insert
 10秒ごとにスケジュールどおり
 完了時に保存
 p_insert() を呼び出します。

EVENTを開始し、10秒ごとにtest01とtest02に10,000件のレコードを自動的に挿入します。

mysql> '%event_scheduler%' のような変数を表示します。
+----------------------------------------------------------+-------+
| 変数名 | 値 |
+----------------------------------------------------------+-------+
| イベントスケジューラ | オフ |
+----------------------------------------------------------+-------+

mysql> グローバルevent_schedulerをオンに設定します。
 クエリは正常、影響を受けた行は 0 行 (0.08 秒)

--3分後。。。
ステップ2:最初のステップで大量のテストデータを生成した後、mysqldumpを使用してlijiamandbデータベースの完全バックアップを実行します。
mysqldump -h192.168.10.11 -uroot -p123456 -P3306 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql

注: バックアップセット内の mysqldump バックアップの終了ポイントを設定するには、--master-data=2 を追加する必要があります。

--3分後。。。

ステップ 3:データベースの削除前後のデータの一貫性チェックを容易にするために、まずテーブルへのデータの挿入を停止します。この時点で、test01 と test02 の両方に 930,000 行のデータがあります。その後のリカバリでも 930,000 行のデータがあることを確認する必要があります。

mysql> グローバルevent_schedulerをオフに設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> test01 から count(*) を選択します。
 +----------+
 | カウント(*) |
 +----------+
 | 930000 |
 +----------+
セット内のローイング（0.14秒）

mysql> test02 から count(*) を選択します。
 +----------+
 | カウント(*) |
 +----------+
 | 930000 |
 +----------+
セット内のローイング（0.13秒）

ステップ4:データベースを削除する

mysql> データベース lijiamandb を削除します。
クエリは正常、2 行が影響を受けました (0.07 秒)

ステップ5: mysqldumpを使用した完全インポート

mysql> データベース lijiamandb を作成します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql>終了
 さよなら
 [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql 
 mysql: [警告] コマンドライン インターフェイスでパスワードを使用すると安全でない可能性があります。

完全なバックアップと復元を実行した後、レコードは 753,238 件しかないことがわかりました。

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb 

mysql> test01 から count(*) を選択します。
 +----------+
 | カウント(*) |
 +----------+
 |753238|
 +----------+
セット内のローイング（0.12秒）

mysql> test02 から count(*) を選択します。
 +----------+
 | カウント(*) |
 +----------+
 |753238|
 +----------+
セット内のロー（0.11秒）

明らかに、完全インポート後、データは不完全です。次に、mysqlbinlog を使用してバイナリログの増分リカバリを実行します。

増分ログリカバリに mysqlbinlog を使用する場合、最も重要なことは、リカバリする開始位置 (start-position) と終了位置 (stop-position) を決定することです。開始位置 (start-position) はすべてを実行した後の位置であり、終了位置は障害が発生する前の位置です。
ステップ6: mysqldumpバックアップの最終的な場所を確認する

[root@masterdb バックアップ]# cat lijiamandb.sql |grep "CHANGE MASTER"
-- MASTER を MASTER_LOG_FILE='master-bin.000044'、MASTER_LOG_POS=8526828 に変更します

バックアップがログ番号 44 の位置 8526828 である場合、リカバリの開始点はログ番号 44 の 8526828 に設定できます。

--次に、復元するエンドポイント、つまり「DROP DATABASE LIJIAMAN」を実行する前の位置を確認します。binlog で確認する必要があります。

[root@masterdb binlog]# ls
 マスターbin.000001 マスターbin.000010 マスターbin.000019 マスターbin.000028 マスターbin.000037 マスターbin.000046 マスターbin.000055
 マスターbin.000002 マスターbin.000011 マスターbin.000020 マスターbin.000029 マスターbin.000038 マスターbin.000047 マスターbin.000056
 マスターbin.000003 マスターbin.000012 マスターbin.000021 マスターbin.000030 マスターbin.000039 マスターbin.000048 マスターbin.000057
 マスターbin.000004 マスターbin.000013 マスターbin.000022 マスターbin.000031 マスターbin.000040 マスターbin.000049 マスターbin.000058
 マスターbin.000005 マスターbin.000014 マスターbin.000023 マスターbin.000032 マスターbin.000041 マスターbin.000050 マスターbin.000059
 マスターbin.000006 マスターbin.000015 マスターbin.000024 マスターbin.000033 マスターbin.000042 マスターbin.000051 マスターbin.index
 マスターbin.000007 マスターbin.000016 マスターbin.000025 マスターbin.000034 マスターbin.000043 マスターbin.000052
 マスターbin.000008 マスターbin.000017 マスターbin.000026 マスターbin.000035 マスターbin.000044 マスターbin.000053
 マスターbin.000009 マスターbin.000018 マスターbin.000027 マスターbin.000036 マスターbin.000045 マスターbin.000054

# 何度も検索した結果、drop database はログファイル番号 54 [root@masterdb binlog] にあることがわかりました。# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"
データベース lijiamandb を削除する

# 検索しやすいようにテキストに保存する [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt


# データベースを削除する前の場所がファイル 54 の 9019487 であることを確認します
 # 9019422 で
 #200423 16:07:46 サーバー ID 11 end_log_pos 9019487 CRC32 0x86f13148 Anonymous_GTID last_committed=30266 シーケンス番号=30267 rbr_only=no
 @@SESSION.GTID_NEXT を 'ANONYMOUS'/*!*/ に設定します。
 # 9019487 で
 #200423 16:07:46 サーバー ID 11 end_log_pos 9019597 CRC32 0xbd6ea5dd クエリ thread_id=100 exec_time=0 error_code=0
 タイムスタンプを 1587629266/*!*/ に設定します。
 @@session.sql_auto_is_null=0/*!*/ を設定します。
 /*!\C utf8 *//*!*/;
 @@session.character_set_client=33、@@session.collation_connection=33、@@session.collation_server=33/*!*/ を設定します。
 データベース lijiamandb を削除する
 //*!*/;
 # 9019597 で
 #200423 16:09:25 サーバー ID 11 end_log_pos 9019662 CRC32 0x8f7b11dc Anonymous_GTID last_committed=30267 シーケンス番号=30268 rbr_only=no
@@SESSION.GTID_NEXT を 'ANONYMOUS'/*!*/ に設定します。
 # 9019662 で
 #200423 16:09:25 サーバー ID 11 end_log_pos 9019774 CRC32 0x9b42423d クエリ thread_id=100 exec_time=0 error_code=0
 タイムスタンプを 1587629365/*!*/ に設定します。
 データベース lijiamandb を作成する

ステップ7:開始点と終了点を決定し、増分リカバリを開始する: ログ番号44の8526828
終了: ファイル 54 9019487

これは 3 つのコマンドに分かれて実行されます。開始ログファイルには開始位置パラメータが含まれ、個別に実行されます。停止ログファイルには停止位置パラメータが含まれ、個別に実行されます。中間ログファイルには特別なパラメータは含まれず、すべて一緒に実行されます。

# ログファイルを開始する

# ログファイルの開始 mysqlbinlog --start-position=8526828 /mysql/binlog/master-bin.000044 | mysql -uroot -p123456

 
# 中間ログファイル mysqlbinlog /mysql/binlog/master-bin.000045 /mysql/binlog/master-bin.000046 /mysql/binlog/master-bin.000047 /mysql/binlog/master-bin.000048 /mysql/binlog/master-bin.000049 /mysql/binlog/master-bin.000050 /mysql/binlog/master-bin.000051 /mysql/binlog/master-bin.000052 /mysql/binlog/master-bin.000053 | mysql -uroot -p123456

 
# ログファイルを終了します mysqlbinlog --stop-position=9019487 /mysql/binlog/master-bin.000054 | mysql -uroot -p123456

ステップ8:回復が完了し、すべてのデータが復元されたことを確認します

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb
mysql> test01 から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 930000 |
+----------+
セット内のローイング（0.15秒）

mysql> test02 から count(*) を選択します。
+----------+
 | カウント(*) |
+----------+
 | 930000 |
+----------+
セット内のローイング（0.13秒）

4. 結論

1. DML 操作の場合、binlog はすべての DML データの変更を記録します。
--挿入の場合、binlogは挿入の行データを記録します
--更新の場合、binlogは変更前と変更後の行データを記録します
--削除の場合、binlog は削除前のデータを記録します。ユーザーが誤って DML 操作を実行した場合、mysqlbinlog を使用してデータベースを障害前の時点に復元できます。

2. DDL 操作の場合、binlog は行の変更ではなくユーザーの行動のみを記録しますが、これは障害前の時点にデータベースを復元する機能には影響しません。

つまり、mysqldump の完全バックアップと binlog ログを使用すると、障害が発生する前の任意の時点にデータを復元できます。

これで、mysqldump+binlog を使用して MySQL で削除されたデータベースを完全に復元する方法についての説明は終わりです。MySQL で削除されたデータベースを復元する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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