MySQL 入門

MySQL データベースのリストア

バックアップファイルからMySQLデータベースをリストア（復元）することは、ディザスタリカバリ、データのマイグレーション、または不要な変更のロールバックにおいて極めて重要なオペレーションです。このプロセスでは、mysqlクライアントのコマンドラインツールを使用して、バックアップファイルに含まれるSQLステートメントを読み込み、実行します。これらのバックアップファイルは通常、前節で解説したmysqldumpを使用して作成されたものです。

1. リストアプロセスの理解

データベースのリストアは、本質的にデータベース構造を再作成し、保存された状態のデータでそれを満たす（ポピュレートする）ことを意味します。これは通常、CREATE DATABASE、CREATE TABLE、INSERT、およびその他のDDL/DMLステートメントを含むスクリプトを実行することによって行われます。mysqlクライアントは、まさにこのようなスクリプトを直接実行するように設計されています。

mysqldumpファイルからデータベースをリストアするための基本的なコマンドは以下の通りです：

mysql -u [username] -p [database_name] < [backup_file.sql]

このコマンドの各コンポーネントを分解してみましょう：

  • mysql: これはMySQLサーバーと対話するためのコマンドラインクライアントです。サーバーにSQLコマンドを送信するために使用されます。
  • -u [username]: サーバーに接続するMySQLのユーザー名を指定します。このユーザーは、データベースを作成し（存在しない場合）、そこにデータを書き込むための十分な権限を持っている必要があります。通常、rootユーザーがこれらの権限を保持しています。
  • -p: 指定されたユーザー名に関連付けられたパスワードの入力を促すプロンプトを表示します。コマンドに直接パスワードを記述するよりも、プロンプトでパスワードを入力する方が安全です。
  • [database_name]: これはデータがリストアされるターゲットデータベースの名称です。バックアップファイルにCREATE DATABASEステートメントが含まれている場合、このデータベースは自動的に作成または選択される可能性があります。含まれていない場合は、既存のデータベースを事前に指定するか、新しいデータベースを作成する必要があります。
  • < [backup_file.sql]: これは標準のUnix/Linux Shellのリダイレクト（Redirect）演算子です。mysqlクライアントに対して、キーボードからではなく、指定されたbackup_file.sqlから入力を読み込むように指示します。mysqldumpの出力は一連のSQLコマンドであり、mysqlクライアントはそれらを順次実行します。

2. リストアの前提条件

データベースのリストアを試みる前に、以下の条件が満たされていることを確認してください：

  • MySQL サーバーが稼働していること： mysqlクライアントが接続できるように、MySQLサーバーがアクティブでアクセス可能な状態である必要があります。
  • バックアップファイルが利用可能であること： mysqlクライアントのコマンドを実行する場所から、mysqldumpによって生成された.sqlバックアップファイルにアクセスできなければなりません。
  • ユーザー権限： -uで指定されたMySQLユーザーは、ターゲットデータベースに対してCREATE、ALTER、DROP、INSERT、UPDATE、およびDELETE権限を持っている必要があります。データベースが存在せず、作成する必要がある場合は、ユーザーにCREATE DATABASE権限も必要です。
  • ターゲットデータベースの状態： 既存のデータベースにリストアする（コンテンツを上書きする）か、新しいデータベースにリストアするかを決定します。

3. 実践ケースとデモンストレーション

サンプルのworldデータベースを使用してリストアプロセスをデモンストレーションしましょう。まず、バックアップファイルが手元にあることを確認します（前節でworld_backup.sqlを作成したと仮定します）。

3.1 シナリオ 1：既存のデータベースへのリストア（データの上書き）

データ破損や誤操作による削除の後、データベースを以前の状態に戻す必要がある場合、このシナリオは非常に一般的です。既存のデータベースにリストアする場合、バックアップファイル内のCREATE TABLEステートメント（存在する場合）はテーブルの再作成を試みます。テーブルがすでに存在する場合、バックアップファイルにDROP TABLE IF EXISTSステートメントが含まれていない限り、エラーが発生する可能性があります（mysqldumpはデフォルトでこれを含みます）。

オペレーション手順：

1. データ消失/変更のシミュレーション： 誤ってworldデータベースのCityテーブルからすべてのアフガニスタン（AFG）の都市を削除してしまったと仮定します。

USE world;
DELETE FROM City WHERE CountryCode = 'AFG';
SELECT COUNT(*) FROM City WHERE CountryCode = 'AFG';
-- COUNT(*) は現在 0 を表示するはずです。

2. world_backup.sql から world データベースをリストアする：world_backup.sqlがカレントディレクトリにあると仮定します。

mysql -u root -p world < world_backup.sql

システムからMySQLのrootパスワードの入力を求められます。入力してください。

3. リストアの検証： コマンド完了後、MySQL Workbenchまたはコマンドラインに接続し、データを再度チェックします。

USE world;
SELECT COUNT(*) FROM City WHERE CountryCode = 'AFG';
-- カウントは元の数値に回復し、都市がリストアされたことを示しているはずです。

解説： mysqlクライアントはworldデータベースに接続します。その後、world_backup.sqlを1行ずつ読み込み、各SQLステートメントを実行します。mysqldumpはデフォルトでDROP TABLE IF EXISTSとCREATE TABLEステートメントを含んでいるため、既存のテーブルは削除され、オリジナルデータを使用して再作成されます。

3.2 シナリオ 2：新しいデータベースへのリストア

このシナリオは、テスト環境の構築、データのマイグレーション、または既存のデータベースの複製（レプリケーション）に使用できます。

オペレーション手順：

1. 新しいデータベースの作成： リストアする前に、リストアされたデータを保持するための空の新しいデータベースを作成します。これをworld_testと呼びましょう。

mysql -u root -p -e "CREATE DATABASE world_test;"

-eオプションを使用すると、mysqlクライアントのインタラクティブShellに入ることなく、コマンドラインから直接SQLステートメントを実行できます。

2. world データベースのバックアップを world_test にリストアする：

mysql -u root -p world_test < world_backup.sql

プロンプトが表示されたら、MySQLのrootパスワードを入力します。

3. リストアの検証：world_test内のデータをチェックし、元のデータベースと一致していることを確認します。

解説： このケースでは、mysqlクライアントは新しく作成されたworld_testデータベースに接続します。その後、バックアップファイル内のすべてのSQLステートメントを実行し、この新しいデータベースをバックアップデータで満たします。

3.3 シナリオ 3：単一テーブルのリストア

データベース全体ではなく、特定のテーブルのみをリストアする必要がある場合があります。これには、該当テーブルのデータのみを含むバックアップファイルが必要です。以前に単一テーブルを対象としたmysqldumpコマンド（例：mysqldump -u root -p world City > city_table_backup.sql）を実行していた場合、次のようにリストアできます：

mysql -u root -p world < city_table_backup.sql

4. 注意事項とベストプラクティス

データベースをリストアする際は、以下の点に留意してください：

  • データベースの存在有無： バックアップファイルにCREATE DATABASEステートメントが含まれていない場合（例えば、mysqldump --no-create-dbを使用した場合）、リストアの前に手動でデータベースを作成する必要があります。
  • 文字セットと照合順序 (Collation)： ターゲットデータベースまたはサーバーの文字セットと照合順序が、バックアップを行った元のデータベースと一致していることを確認してください。特に非ASCIIデータの場合は重要です。不一致があると、データの破損や文字化けが発生する可能性があります。
  • 大規模バックアップのパフォーマンス最適化： 非常に大きなバックアップファイルの場合、リストアプロセスに長時間がかかり、サーバーリソースを大量に消費する可能性があります。バックアップファイルの先頭で外部キーチェック（SET FOREIGN_KEY_CHECKS = 0;）および一意性チェック（SET UNIQUE_CHECKS = 0;）を一時的に無効化し、末尾で再度有効化することを検討してください。これにより、インサート速度を大幅に高速化できます（mysqldumpは通常、これらをデフォルトで追加します）。
  • モニタリング： リストア中は、サーバーのリソース使用状況（CPU、メモリ、ディスクI/O）をモニタリングしてください。
  • ポイントインタイムリカバリ (PITR)： mysqldumpはフルバックアップを提供しますが、真のポイントインタイムリカバリには通常、フルバックアップとMySQLのバイナリログ（Binary Logs）を組み合わせて使用する必要があります。これは、2回のフルバックアップ間の任意の特定の時点にリストアできるようにするための高度なテクニックです。