MySQL 論理バックアップとリカバリ テストの概要

1. データベース論理バックアップとはどのようなバックアップですか?

ご存知のとおり、データベースがデータを返す際、データベースは、当初設計して期待した特定の論理的関連形式の形式でデータを 1 つずつ提示し、特定のビジネス ロジック属性を持ちます。ただし、物理ストレージ レベルでは、データベース ソフトウェアは、特定の処理を行った後、データベース ソフトウェアが設計した特定の形式でデータを保存します。

データベース論理バックアップとは、バックアップ ソフトウェアが、データベースの論理構造オブジェクトを単位として、元々設計した論理関係に基づき、データベース内のデータに対して事前定義された論理関連付け形式に従って、関連するテキスト ファイルを 1 つずつ生成し、バックアップの目的を達成することを意味します。

2. よく使われる論理バックアップ

論理バックアップは、中小規模のシステムにとって最もシンプルで一般的に使用されているバックアップ方法と言えます。 MySQL でよく使用される論理バックアップには、主に 2 つの種類があります。1 つは、現在のデータベースのデータを完全に再現できる INSERT ステートメントを生成する方法です。もう 1 つは、論理バックアップ ソフトウェアを使用して、データベース テーブル データを特定の区切り文字で区切ってテキスト ファイルに記録する方法です。

① INSERT文のバックアップを生成する

2 種類の論理バックアップにはそれぞれ長所と短所があり、対象となる使用シナリオも若干異なります。まずは、INSERT ステートメントを生成する論理バックアップについて見てみましょう。

MySQL データベースでは、通常、MySQL データベース ソフトウェアに付属するツール プログラムの mysqldump を使用して、いわゆる INSERT ステートメントの論理バックアップ ファイルを実現します。基本的な使い方は以下のとおりです。

定義とデータのダンプMySQLデータベースまたはテーブル
使用方法: mysqldump [オプション] データベース [テーブル]
または mysqldump [オプション] --databases [オプション] DB1 [DB2 DB3...]
または mysqldump [オプション] --all-databases [オプション]

mysqldump は比較的簡単に使用できるため、「mysqldump --help」を実行することで必要な情報の大部分を取得できます。ここでは、MySQL データベースのいくつかの概念と原則を組み合わせて、mysqldump を使用してデータベースの論理バックアップを実行するときに注意する必要があるヒントと注意事項について説明します。

データベースを使用するほとんどのソフトウェアや Web サイトでは、データベースが時々シャットダウンしてサービスの提供を停止するのではなく、可能な限り最高の可用性を提供できることを望んでいることは誰もが知っています。データベースがサービスを提供できなくなると、システムはデータにアクセスして一部の動的な機能を提供できなくなります。

したがって、ほとんどのシステムでは、バックアップごとにシステムをシャットダウンすることは受け入れられない可能性があります。ただし、mysqldump プログラムの実装原則は、提供されたパラメータ情報とデータベース内のシステム テーブル情報を使用して各テーブルからデータを取得し、INSERT ステートメントを生成してバックアップ ファイルに書き込むことです。これにより問題が発生します。通常のシステム操作中に、データ変更の要求が継続的に実行される可能性があり、mysqldump によってバックアップされたデータに不整合が発生する可能性があります。

つまり、バックアップ データは同じ時点のデータではない可能性があり、整合性制約を満たすことができない可能性もあります。このようなバックアップ セットは、一部のシステムでは大きな問題にならないかもしれませんが、データの一貫性と整合性に厳しい要件がある一部のシステムでは、完全に無効なバックアップ セットであるため、大きな問題になります。

このようなシナリオではどうすればいいでしょうか?データベース内のデータの一貫性を保ちたい場合、それが実現できるのは 2 つの状況のみであることがわかっています。

• まず、すべてのデータを一度に取り出します。
• 2 番目に、データベース内のデータは保存されています。

最初の状況では、誰もが「これは可能だろうか？」と考えるはずです。

いずれにしても、テーブルが 2 つ以上ある限り、どのようにプログラムを書いても、昨夜と同じ時刻にデータを取得することは不可能です。はい、従来の方法ではデータ取得の時点を完全に一致させることはできませんが、同じトランザクションでは、データベースによって読み取られたデータが同じ時点であることを保証できることを忘れないでください。

したがって、Innodb や BDB などのトランザクションをサポートするストレージ エンジンの場合、同じトランザクション内でバックアップ プロセス全体を制御して、バックアップ データの一貫性と整合性を実現できます。mysqldump プログラムには、この機能をサポートするための関連パラメータ オプションも用意されています。つまり、「--single-transaction」オプションを使用すると、データベースの通常のサービスに影響を与えることはありません。

2 番目のケースでは、バックアップが必要なテーブルをロックして、読み取りのみを許可し、書き込みは許可しないようにすることが、誰もが最初に思いつくことだと思います。

はい、それが私たちにできるすべてです。妥協策として、バックアップ プロセス中にデータベースがデータ クエリ サービスのみを提供して書き込みサービスをロックし、データが一時的に変更されない一貫した状態になるようにするしかありません。mysqldump がバックアップを完了すると、書き込みロックが解除され、完全なサービスが再開されます。

mysqldump プログラム自体にも、「--lock-tables」や「--lock-all-tables」などの関連オプションが用意されており、実行前にテーブルをロックし、実行後に自動的にロックを解除します。

ここで注意すべき点は、「--lock-tables」はダンプする必要があるすべてのテーブルを一度にロックするのではなく、一度に 1 つのデータベースのテーブルのみをロックすることです。ダンプする必要があるテーブルが複数のデータベースにある場合は、データの一貫性と整合性を確保するために、「--lock-all-tables」を使用する必要があります。

mysqldump を使用して INSERT ステートメントの論理バックアップ ファイルを生成する場合、非常に便利なオプション「--master-data[=value]」を使用できます。 「--master-data=1」を追加すると、mysqldump は MySQL が現在使用している binlog ログの名前と位置をダンプファイルに記録しますが、これは CHANGE_MASTER ステートメントの形式で記録されます。「--master-data」または「--master-data＝2」のみを使用した場合、CHANGE_MASTER ステートメントはコメントの形式で存在します。このオプションは、オンライン スレーブ セットアップを実装するときに非常に便利です。スレーブがオンラインでセットアップされていない場合でも、場合によっては、リカバリ プロセス中にバックアップ バイナリ ログを介してさらにリカバリ操作を実行できます。

シナリオによっては、特別なデータを他のデータベースにエクスポートしたいが、最初に一時テーブルを作成したくない場合があります。これを実現するために、mysqldump プログラムの "--where='where-condition'" を使用することもできますが、これは 1 つのテーブルのみをダンプする場合にのみ使用できます。

実際、上記の使用上のヒントに加えて、mysqldump には、さまざまなシナリオで使用できる他の多くの便利なオプションも用意されています。たとえば、「--no-data」を使用してデータベース構造作成スクリプトのみをダンプしたり、「--no-create-info」を使用してダンプ ファイルにテーブル構造を作成するコマンドを削除したりすることができます。興味のある読者は、mysqldump プログラムの使用方法の概要を詳しく読んでから、自分でテストすることができます。

② 特定の形式でプレーンテキストバックアップデータファイルのバックアップを生成する

INSERT コマンドを生成して論理バックアップを作成するだけでなく、データベース内のデータを特定の区切り文字で区切ってテキスト ファイルに記録するという別の方法を使用して、論理バックアップの効果を実現することもできます。このようなバックアップ データは、INSERT コマンド ファイルと比較すると、必要なストレージ スペースが少なく、データ形式がより明確で、編集も簡単です。ただし、複数のテーブルのバックアップ データを同じバックアップ ファイルに存在させることができず、データベース構造を再構築するコマンドがないという欠点があります。バックアップ セットには複数のファイルが必要であり、ファイルの増加によってメンテナンスとリカバリのコストが増加するという点のみが影響しますが、基本的には簡単なスクリプトをいくつか作成するだけで実現できます。

では、このようなバックアップ セット ファイルを生成するには、一般的にどのような方法を使用すればよいのでしょうか。実際、MySQL には対応する機能がすでに実装されています。

MySQL では、カスタム区切り文字を使用したプレーンテキスト バックアップ ファイルを取得するために、通常、次の 2 つの方法が使用されます。

1. SELECT ... TO OUTFILE FROM ... コマンドを実行して実装します。

MySQL は、SQL ステートメントを使用して特定のデータを指定された形式でテキスト ファイルに出力するための SELECT 構文を提供します。また、エクスポートされたファイルをそのままデータベースに簡単にインポートするための実用的なツールと関連コマンドも提供します。これはまさにバックアップに必要なものではありませんか?

このコマンドには、次のように注意が必要ないくつかのパラメータがあります。

• 「FIELDS ESCAPED BY ['name']」は、SQL ステートメントでエスケープする必要がある文字をエスケープするための文字エスケープ関数を実装します。
• 「FIELDS [OPTIONALLY] ENCLOSED BY 'name'」は、フィールドの内容を「ラップ」できます。「OPTIONALLY」を使用しない場合、数値データを含むすべてのタイプのデータが「ラップ」されます。「OPTIONALLY」を使用した後、数値データは指定された文字で「ラップ」されません。
• 「FIELDS TERMINATED BY」は、2 つのフィールド間の区切り文字を設定するために使用できます。
• 「LINES TERMINATED BY」は、出力ファイルの各レコードの末尾に追加する文字を MySQL に指示します。

例えば：

root@localhost : テスト 10:02:02> SELECT * INTO OUTFILE '/tmp/dump.text'
-> フィールドは ',' で終了し、オプションで '"' で囲まれます
-> '\n' で終了する行
-> FROM test_outfile 制限 100;
クエリは正常、100 行が影響を受けました (0.00 秒)
root@localhost : テスト 10:02:11> 終了
さよなら
root@sky:/tmp# cat dump.text
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
……

2. mysqldump経由でエクスポート

ご存知のとおり、mysqldump は、INSERT ステートメントの形式でデータベース内のデータを使用して関連するバックアップ ファイルを生成できます。実際、mysqldump は、INSERT ステートメントを生成するだけでなく、上記の「SELECT ... TO OUTFILE FROM ...」によって実装される関数も実装し、同時に関連するデータベース構造に対応する作成スクリプトを生成することもできます。

例えば：

root@sky:~# ls -l /tmp/mysqldump
合計 0
root@sky:~# mysqldump -uroot -T/tmp/mysqldump テスト test_outfile --fields closed-by=\" --fields-terminated-by=,
root@sky:~# ls -l /tmp/mysqldump
合計 8
-rw-r--r-- 1 ルート ルート 1346 2021-4-20 22:18 test_outfile.sql
-rw-rw-rw- 1 mysql mysql 2521 2021-4-20 22:18 test_outfile.txt
root@sky:~# cat /tmp/mysqldump/test_outfile.txt
350021,21,"A","abcd"
350022,22,"B","abcd"
350023,23,"C","abcd"
350024,24,"D","abcd"
350025,25,"A","abcd"
……
root@sky:~# cat /tmp/mysqldump/test_outfile.sql
--MySQL ダンプ 10.11
--
-- ホスト: localhost データベース: test
-- ------------------------------------------------------
--サーバーバージョン 5.0.51a-log
/*!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' を設定します */;
/*!40101 @OLD_SQL_MODE=@@SQL_MODE、SQL_MODE='' を設定します */;
/*!40111 @OLD_SQL_NOTES=@@SQL_NOTES、SQL_NOTES=0 に設定 */;
--
-- テーブル `test_outfile` のテーブル構造
--
`test_outfile` が存在する場合はテーブルを削除します。
SET @saved_cs_client = @@character_set_client;
文字セットクライアントをutf8に設定します。
テーブル `test_outfile` を作成します (
`id` int(11) NOT NULL デフォルト '0',
`t_id` int(11) デフォルトはNULL、
`a` char(1) デフォルトはNULL、
`mid` varchar(32) デフォルト NULL
)ENGINE=MyISAM デフォルト文字セット=utf8;
SET character_set_client = @saved_cs_client;
/*!40103 TIME_ZONE=@OLD_TIME_ZONE を設定します */;
/*!40101 SQL_MODE を @OLD_SQL_MODE に設定します */;
/*!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 を設定します */;
-- ダンプは 2021-4-20 14:18:23 に完了しました

この出力構造は、バックアップとして使用するのに非常に適しています。もちろん、一度に複数のテーブルをダンプする必要がある場合は、テーブルごとに 2 つの対応するファイルが生成されます。

3. 論理バックアップとリカバリ方法

バックアップがあるだけでは十分ではありません。これらのバックアップの使用方法を知る必要があります。では、上記で作成した論理バックアップを復元する方法を見てみましょう。

すべてのバックアップ データは、当社のオリジナルのデータベース構造設計に関連した形式で保存されるため、論理バックアップの復元は比較的簡単です。もちろん、2 つの異なる論理バックアップ形式では、回復方法が若干異なります。以下では、これら 2 つの論理バックアップ ファイルの回復方法について簡単に紹介します。

①INSERT文ファイルのリカバリ

INSERT ステートメントの形式でバックアップ ファイルを復元するのが最も簡単です。バックアップ ファイル内の SQL コマンドのすべて (または一部) を実行するだけです。まず、完全なリカバリが必要な場合は、「mysql < backup.sql」を使用してバックアップファイルを直接呼び出し、その中のすべてのコマンドを実行し、データをバックアップ時の状態に完全に復元することができます。 mysql を使用して MySQL に接続している場合は、mysql で「source/path/backup.sql」または「\./path/backup.sql」を実行して復元することもできます。

②純粋なデータテキストバックアップの回復

上記の論理バックアップの 2 番目の形式の場合、リカバリは少し面倒になり、関連するコマンドを使用して各テーブルを 1 つずつ復元する必要があります。もちろん、スクリプトを使用して複数のテーブルを自動的にリカバリすることも便利です。リカバリ方法も 2 つあります。1 つは MySQL の「LOAD DATA INFILE」コマンドを使用する方法で、もう 1 つは MySQL が提供する mysqlimport ツールを使用してリカバリする方法です。

論理バックアップでは何ができますか?何ができないのか？

論理バックアップをリカバリに使用する方法を理解した後、これらの論理バックアップで何ができるかを知る必要があります。

1. 論理バックアップにより、無関係なデータに影響を与えることなく、関連する SQL またはコマンドを実行することで、データベース内の関連データをバックアップ時の状態に完全に復元できます。
2. データベース全体の論理バックアップにより、新しい MySQL 環境でバックアップ時とまったく同じデータベースを完全に再構築することができ、MySQL が配置されているプラ​​ットフォームの種類によって制限されません。
3. 特定の条件下での論理バックアップを通じて、特定のデータを他の MySQL または他のデータベース環境に簡単に移行 (または同期) できます。
4. 論理バックアップを使用すると、バックアップ セット内のデータ全体を復元せずに、その一部のみを復元できます。
   

論理バックアップで何ができるかを理解した後は、何ができないかも明確にする必要があります。そうすることで、そのようなバックアップが期待に応えられるかどうか、本当に必要なバックアップであるかどうかを明確に判断できるようになります。

論理バックアップでは、バックアップ時刻以外の時刻にデータを復元することはできません。

4. 論理バックアップとリカバリテスト

誰かのデータベースに問題があると聞いて、自信を持って以前作成したデータベースを復元する準備をしているときに、バックアップ セットが利用できないか、バックアップ作成時に期待した回復効果が得られないことに気付くことがあります。このような状況に遭遇すると、誰もが非常に落ち込んでしまうのではないかと思います。データベース バックアップの最も重要かつ決定的な用途は、データベースに何らかの異常な状態が発生し、データを復元する必要がある場合です。

メンテナーとして、私たちはそのような低レベルのミスを決して犯すべきではありません。では、どうすればこのような問題を回避できるのでしょうか?

唯一の方法は、定期的にシミュレートされたリカバリ テストを実行して、バックアップ セットが実際に有効かどうか、およびバックアップの期待どおりに復元できるかどうかを確認することです。

この時点で、「リカバリテストはどのように行うのですか？オンライン環境ではデータを復元することはできないですよね？」と疑問に思う人もいるかもしれません。

はい、オンライン環境のデータは復元できませんが、テスト環境やその他の場所では復元できないのはなぜですか?

リカバリ テストを実行する目的は、バックアップが有効であり、期待どおりに機能するかどうかを確認することだけです。

したがって、リカバリ テストを実行する前に、まずバックアップがどのようなシナリオを対象としているかを明確に把握する必要があります。

たとえば、データベース全体の論理バックアップを作成する場合、その目的は、データベースに論理的または物理的な異常が発生したときに、データベース データ全体をバックアップ時点に復元できるようにすることです。その場合、リカバリ テストでは、論理バックアップ全体をデータベース全体に復元するだけで、完全なデータベースを正常に再構築できるかどうかを確認できます。

復元されたデータがバックアップ時刻と一致しているかどうかについては、手動で判断して比較するしかありません。

さらに、テーブルなどのデータベース オブジェクトに問題が発生した場合に、テーブル データをできるだけ早くバックアップ時点の状態に復元できることも期待できます。次に、指定した単一のテーブルに対してサンプルのリカバリ テストを実行できます。

データベース ホストがクラッシュし、ハードウェアが損傷してすべてのデータベース データが失われたと仮定して、完全なデータベース復旧のテスト例を実行します。

データベースにハードウェア障害が発生し、すべてのデータが失われた場合、損傷したホストを交換して対応するサービスを復元するために、できるだけ早く新しいホストを見つける必要があります。サービスを復元する前に、まず破損したデータベースを再構築する必要があります。すでに新しいホストを入手し、MySQL ソフトウェアがインストールされ、関連する設定が調整されており、データベースの復元を待っている状態であると仮定します。

クラッシュ時の最新の完全なデータベース論理バックアップ ファイルを取得し、準備した新しいホストにコピーして、インストールされた MySQL を起動する必要があります。

論理バックアップ形式が 2 つあるため、各形式のリカバリ方法は異なります。ここでは、両方の形式の論理バックアップのリカバリの例を示します。

①INSERT文の論理バックアップの場合

a. バックアップ ファイルを準備し、「/tmp」などの特定のディレクトリにコピーします。

b. 次のコマンドを実行して、バックアップ セット内の関連コマンドを実行します。

mysql -uユーザー名 -p < バックアップ.sql

または、まず mysql 経由でデータベースにログインし、次のコマンドを実行します。

root@localhost : (なし) 09:59:40> ソース /tmp/backup.sql

c. 次に、データベース内の対応するデータベース オブジェクトが完全かどうかを確認します。

d. いくつかのテーブルのデータをランダムにチェックして手動で検証し、アプリケーションに内部テスト検証を開始するように通知します。すべての検証に合格すると、サービスを外部に提供できます。

もちろん、上記の手順はすべて各ステップが正常であることを前提に実行されています。特定のステップで問題が見つかった場合、ステップ b で例外が発生してプロセスを続行できない場合は、まず発生したエラーに基づいて回復コマンドに問題があるかどうかを確認する必要があります。私たちの環境に何か問題があるのでしょうか?等

問題がバックアップ ファイルにあることが確認された場合、バックアップは無効となり、テストは失敗します。回復プロセスは正常であるが、検証中にデータベース オブジェクトが欠落しているか、一部のオブジェクトのデータが正しくないか、またはデータがまったく存在しないことが判明した場合。これは、バックアップ レベルが期待を満たすことができず、バックアップが失敗することを意味します。

もちろん、実際の作業のリカバリ プロセス中に同様の状況に遭遇した場合、以前のバックアップ セットがある場合は、一歩下がって以前のバックアップ セットを使用して同じリカバリ操作を実行する必要があります。以前のバックアップ セットのデータは多少歪んでいる可能性がありますが、すべてのデータを失うことなく、少なくとも部分的に復元できます。

② 特殊な区切り文字で区切られたプレーンテキストデータをバックアップする場合

a. 最初の手順は INSERT バックアップ ファイルと同じで、クラッシュ時間に最も近いバックアップ ファイルを準備します。

b. 特定のツールまたはコマンドを使用してデータをデータベースにインポートします。

データベース構造作成スクリプトとプレーンテキストデータのバックアップファイルは別々に保存されているため、最初にデータベース構造作成スクリプトを実行してからデータをインポートする必要があります。構造スクリプトの作成方法は、上記の最初のバックアップのリカバリ テストの手順 b とまったく同じです。

データベース構造ができたので、次のようにしてバックアップ データをインポートできます。

mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt

または

INFILE '/tmp/test_outfile.txt' のデータを TABLE test_outfile にロードします。フィールドは '"' で終了し、',' で囲まれています。

以降の手順は、INSERT ステートメントのバックアップ ファイルを復元する場合とまったく同じなので、ここでは繰り返しません。

上記は、MySQL 論理バックアップとリカバリ テストの詳細な概要です。MySQL 論理バックアップとリカバリ テストの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。