mysql binlog を使用してデータを復元する方法

データリカバリに mysql binlog を使用する前に、まず binlog を有効にする必要があります。ここでテスト環境をセットアップし、mysql binlog を使用してデータベースを復元する方法を学習します。原理は比較的単純で、binlog は変更されたデータを mysql に保存します。たとえば、データベースを作成してデータを書き込むと、これらは mysql の binlog に保存されます。

復元する必要がある場合は、開始位置と終了位置の 2 つの位置を見つける必要があります。終了位置の半分は、データが破壊または削除される前の位置です。 Mysql 8 ではデフォルトで binlog が有効になっています

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+

log_bin が有効になっていることがわかります。同時に、保存場所が /daba/mysql ディレクトリにあることがわかります。プレフィックスは binlog です

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

これはテスト データベース (履歴情報があり、以前のデータは役に立たない) であるため、複数の A binlog ログ ファイルがあることがわかります。テストを容易にするために、リセット操作が直接実行されます。 binlog ファイルをすべて削除する場合は注意してください。本番環境の場合、データの価値は計り知れません。

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000143 |       156 | No        |
| binlog.000144 |       200 | No        |
| binlog.000145 |       156 | No        |
| binlog.000146 |       156 | No        |
+---------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)


ls /data/mysql/binlog.*
/data/mysql/binlog.000001  /data/mysql/binlog.index

リセット(マスターリセット)を実行すると、以前のbinlogファイルが削除され、新しいbinlogファイルが生成されていることがわかります。

このファイルの内容を確認できます

$mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...

今回はデータを追加します

mysql <<EOT
create database test_liuhaolin_com;
select now();
EOT

mysql <<EOT
use test_liuhaolin_com;

create table if not exists test(
	\`id\` int unsigned not null auto_increment primary key,
	\`key\` varchar(100),
	\`val\` varchar(255)
) engine=myisam charset=utf8mb4;
EOT

mysql <<EOT
use test_liuhaolin_com;
insert into test  values (&#39;1&#39;,&#39;website&#39;, &#39;https://www.liuhaolin.com&#39;);
EOT

この際、データベースtest_liuhaolin_com

mysql> drop database test_liuhaolin_com;
Query OK, 1 row affected (0.09 sec)

を誤って削除してしまいました

次に、このデータベースを復元する方法という実際的な問題を解決する必要があります。

まず、干渉を防ぐために、フラッシュ ログを実行して新しい binlog ファイルを生成します。

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1594 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1594 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

データを回復するには、まずデータが削除された場所を見つける必要があります。

mysqlbinlog binlog.000001 | grep -n  "drop database"
113:drop database test_liuhaolin_com

113 行目に delete ステートメントがあることがわかります。最後に、2 つの場所が見つかります。

1. 復元する必要があるデータの開始位置

2. 復元する必要があるデータの終了位置です。復元する

#ここでの開始位置はデータベースが作成される場所であり、終了位置はデータベースが削除される場所です。

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

ファイル tmp.sql

binlog

ファイルの開始位置と終了位置を知ることができます。 is 233

binlog

終了位置が 1371 であることがわかりますので、データ回復を実行します。

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1371 | mysql

検証として、実行前にデータベースが存在するかどうかを確認できます。

rree

以上がmysql binlog を使用してデータを復元する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。