MySQL で単一のデータベースまたはテーブルを復元する方法と、起こりうる落とし穴

序文：

最も一般的に使用される MySQL 論理バックアップツールは mysqldump です。通常は、インスタンス全体またはビジネスデータベースの一部をバックアップします。リカバリを実行したかどうかはわかりません。データベースやテーブルの復元など、リカバリのシナリオは多数ある可能性があります。では、フルバックアップから単一のデータベースまたはテーブルを復元するにはどうすればよいでしょうか? 隠れた落とし穴は何でしょうか?この記事を一緒に見てみましょう。

1. 単一のデータベースまたはテーブルを復元する方法

前回の記事では、MySQL のバックアップとリカバリについて紹介しました。各データベースインスタンスには複数のデータベースが存在する場合があります。一般的には、バックアップはインスタンス全体をバックアップしますが、リカバリ要件は多岐にわたります。たとえば、特定のデータベースまたは特定のテーブルのみを復元したい場合、どうすればよいでしょうか。

インスタンス内のデータ量が多くない場合は、インスタンス全体を別の環境に復元し、必要なデータベースまたはテーブルを個別にバックアップして回復することができます。ただし、この方法は柔軟性が十分ではなく、データの量が比較的少ない場合にのみ適用できます。

実際、フルバックアップから単一のデータベースを復元するのは非常に便利です。単一のデータベースのリカバリを指定できる --one-database パラメータがあります。以下は具体的なデモンストレーションです。

# すべてのライブラリを表示してバックアップしますmysql> show databases;
+--------------------+
| データベース |
+--------------------+
| 情報スキーマ |
|mysql |
| パフォーマンススキーマ |
|sbテスト|
|システム|
|テストデータベース|
| テストdb2 |
+--------------------+

mysqldump -uroot -pxxxx -R -E --single-transaction --all-databases > all_db.sql

# testdb データベースを削除し、単一のデータベースのリカバリを実行します。mysql> drop database testdb;
クエリは正常、36 行が影響を受けました (2.06 秒)

# リカバリ前に testdb データベースが存在しない場合は、手動で作成する必要があります。mysql -uroot -pxxxx --one-database testdb < all_db.sql

上記の方法に加えて、手動スクリーニングを使用して単一のデータベースまたはテーブルを復元することもできます。このとき、Linux の有名な sed コマンドと grep コマンドが役立ちます。これら 2 つのコマンドを使用して、完全バックアップから単一のデータベースまたは単一のテーブルのステートメントをフィルター処理できます。スクリーニング方法は次のとおりです。

# フルバックアップから単一のデータベースを復元する sed -n '/^-- 現在のデータベース: `testdb`/,/^-- 現在のデータベース: `/p' all_db.sql > testdb.sql

# 単一テーブルのステートメントを除外します cat all_db.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > /tmp/test_tb_info.sql 
cat all_db.sql | grep --ignore-case '`test_tb`' に挿入 > /tmp/test_tb_data.sql

2. 落とし穴に注意

単一のデータベースまたは単一のテーブルを復元するために手動でフィルタリングする上記の方法は、シンプルで便利に思えますが、実際には小さな落とし穴が隠れています。以下で詳しく説明します。

# インスタンス全体をバックアップします。mysqldump -uroot -pxxxx -R -E --single-transaction --all-databases > all_db.sql

# 手動で test_tb をバックアップし、その後 test_tb を削除します
mysql> test_tb のようなテーブル test_tb_bak を作成します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

mysql> test_tb_bak に挿入し、test_tb から * を選択します。
クエリは正常、4 行が影響を受けました (0.02 秒)
記録: 4 重複: 0 警告: 0

mysql> テーブル test_tb を削除します。
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

# 完全バックアップから test_db テーブルの作成とデータ挿入ステートメントをフィルタリングします cat all_db.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_tb`/!d;q' > test_tb_info.sql 
cat all_db.sql | grep --ignore-case '`test_tb` に挿入' > test_tb_data.sql

# ステートメントをチェックすると問題ないようです cat test_tb_info.sql

`test_tb` が存在する場合はテーブルを削除します。
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
テーブル `test_tb` を作成します (
 `inc_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動増分主キー',
 `col1` int(11) NULLではない、
 `col2` varchar(20) デフォルト NULL,
 `col_dt` 日時 デフォルト NULL、
 `create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
 `update_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
 主キー (`inc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='テストテーブル';
/*!40101 SET character_set_client = @saved_cs_client */;

猫 test_tb_data.sql

`test_tb` に値 (1,1001,'dsfs','2020-08-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27') を挿入します。
(2,1002,'vfsfs','2020-09-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(3,1003,'adsfsf',NULL,'2020-09-17 06:19:27','2020-09-17 06:19:27'),
(4,1004,'walfd','2020-09-17 14:19:27','2020-09-17 06:19:27','2020-09-18 07:52:13');

# 単一テーブルリカバリ操作を実行する mysql -uroot -pxxxx testdb < test_tb_info.sql
mysql -uroot -pxxxx testdb < test_tb_data.sql

# 復元されたデータを表示し、バックアップ テーブルと比較しますmysql> select * from test_tb;
+--------+------+--------+----------------------+----------------------+---------------------+
| inc_id | col1 | col2 | col_dt | 作成時間 | 更新時間 |
+--------+------+--------+----------------------+----------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 06:19:27 | 2020-09-17 06:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 06:19:27 | 2020-09-18 07:52:13 |
+--------+------+--------+----------------------+----------------------+---------------------+
セット内の 4 行 (0.00 秒)

mysql> test_tb_bak から * を選択します。
+--------+------+--------+----------------------+----------------------+---------------------+
| inc_id | col1 | col2 | col_dt | 作成時間 | 更新時間 |
+--------+------+--------+----------------------+----------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+----------------------+----------------------+---------------------+
セット内の 4 行 (0.00 秒)

よく観察すると、復元されたデータに何か問題があることがわかります。どうやら時間が正しくないようです。もう一度よく見ると、一部の時間が 8 時間ずれていることがわかります。詳細な調査の結果、タイムスタンプ型フィールドの時間データ復旧に問題があることが判明しました。正確には、バックアップファイルには 0 タイムゾーンが記録されていましたが、弊社のシステムでは通常 East 8 ゾーンを使用しているため、8 時間の誤差が発生しました。

では、すべてが復元されても問題がないのはなぜかと疑問に思うかもしれません。いい質問ですね。バックアップファイルを見て確認してみましょう。

# バックアップ ファイルの始まり -- MySQL ダンプ 10.13 Distrib 5.7.23、Linux (x86_64) 用
--
-- ホスト: localhost データベース:
-- ------------------------------------------------------
--サーバーバージョン 5.7.23-ログ

/*!40101 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT を設定します */;
/*!40101 @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS を設定します */;
/*!40101 @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION を設定します */;
/*!40101 名前をutf8に設定 */;
/*!40103 @OLD_TIME_ZONE=@@TIME_ZONE を設定します */;
/*!40103 TIME_ZONE='+00:00' を設定します */; 
上記の 2 行に注意してください /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS、FOREIGN_KEY_CHECKS=0 に設定 */;
/*!40101 @OLD_SQL_MODE=@@SQL_MODE、SQL_MODE='NO_AUTO_VALUE_ON_ZERO' を設定します */;
/*!40111 @OLD_SQL_NOTES=@@SQL_NOTES、SQL_NOTES=0 に設定 */;


# バックアップファイルの終了 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SQL_MODE を @OLD_SQL_MODE に設定します */;
/*!40014 FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS を設定します */;
/*!40014 UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS を設定します */;
/*!40101 CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT を設定します */;
/*!40101 CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS を設定します */;
/*!40101 COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION を設定します */;
/*!40111 SQL_NOTES=@OLD_SQL_NOTES を設定します */;

-- ダンプは 2020-09-18 15:56:40 に完了しました

バックアップファイルを注意深く確認すると、mysqldump によってバックアップされたファイルでは、セッションのタイムゾーンが最初に 0 に変更され、最後に元のタイムゾーンに戻されていることがわかります。つまり、バックアップファイルに記録されるタイムスタンプデータは 0 タイムゾーンに基づいています。フィルタリングされた SQL を直接実行すると、0 タイムゾーンのタイムスタンプが East 8th ゾーンのシステムに挿入され、明らかに 8 時間の時差が発生します。

ここで私が読んだ内容を理解していただけるでしょうか。バックアップとリカバリの経験がある方なら、理解しやすいかもしれません。上記の問題の解決方法も非常に簡単です。つまり、SQL ファイルを実行する前に、現在のセッションのタイムゾーンを 0 に変更します。もう一度説明しましょう。

# test_db テーブルをクリアします。datamysql> truncate table test_tb;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

# ファイルの先頭にタイムゾーン宣言を追加します vim test_tb_data.sql
セッションのTIME_ZONEを'+00:00'に設定します。
`test_tb` に値 (1,1001,'dsfs','2020-08-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27') を挿入します。
(2,1002,'vfsfs','2020-09-04 12:12:36','2020-09-17 06:19:27','2020-09-17 06:19:27'),
(3,1003,'adsfsf',NULL,'2020-09-17 06:19:27','2020-09-17 06:19:27'),
(4,1004,'walfd','2020-09-17 14:19:27','2020-09-17 06:19:27','2020-09-18 07:52:13');

# リカバリを実行して比較し、データが正しいことを確認しますmysql> select * from test_tb;
+--------+------+--------+----------------------+----------------------+---------------------+
| inc_id | col1 | col2 | col_dt | 作成時間 | 更新時間 |
+--------+------+--------+----------------------+----------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+----------------------+----------------------+---------------------+
セット内の 4 行 (0.00 秒)

mysql> test_tb_bak から * を選択します。
+--------+------+--------+----------------------+----------------------+---------------------+
| inc_id | col1 | col2 | col_dt | 作成時間 | 更新時間 |
+--------+------+--------+----------------------+----------------------+---------------------+
| 1 | 1001 | dsfs | 2020-08-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 2 | 1002 | vfsfs | 2020-09-04 12:12:36 | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 3 | 1003 | adsfsf | NULL | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 |
| 4 | 1004 | walfd | 2020-09-17 14:19:27 | 2020-09-17 14:19:27 | 2020-09-18 15:52:13 |
+--------+------+--------+----------------------+----------------------+---------------------+
セット内の 4 行 (0.00 秒)

要約:

単一のデータベースまたはテーブルを復元する方法は、インターネット上で簡単に検索できます。そのほとんどは、sed コマンドと grep コマンドを使用して手動でスクリーニングする上記の方法に言及しています。しかし、ほとんどの記事では起こりうる問題については触れられていません。テーブルフィールドにタイムスタンプタイプがある場合は、この方法を使用する際に特に注意する必要があります。どのような回復ニーズに直面しても、回復するほど状況を悪化させないように細心の注意を払う必要があります。回復前に練習するための空のインスタンスを用意しておくのが最善です。

上記は、MySQL で単一のデータベースまたは単一のテーブルを復元する方法の詳細と、遭遇する可能性のある落とし穴です。MySQL で単一のデータベースまたは単一のテーブルを復元する方法の詳細については、123WORDPRESS.COM の他の関連記事に注意してください。

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