MySQL 入門

MySQL データベースのバックアップ

MySQLデータベースを管理する上で、最も重要なタスクの一つが定期的なバックアップの作成です。ハードウェア障害、偶発的なデータ削除、あるいは悪意のある攻撃によるデータ消失など、その結果は壊滅的なものになる可能性があります。バックアップはセーフティネットであり、データベースを以前の正常な状態に復元（リストア）することを可能にします。

mysqldumpユーティリティは、MySQLデータベースのロジカルバックアップ（logical backups）を作成するための標準コマンドラインツールです。このツールは、データベース、テーブル、およびそのデータを再構築するために再実行できるSQLステートメントを生成します。

1. mysqldumpを深く理解する

mysqldumpの動作原理は、MySQLサーバーに接続し、データベースの構造（スキーマ / schema）とそのデータを読み取り、CREATE TABLE、INSERT、その他のSQLステートメントを含むプレーンテキストファイルに書き出すというものです。これにより、バックアップのポータビリティ（移植性）が非常に高くなります。ほぼすべてのMySQLサーバー上で復元できるだけでなく、テキストエディタでコンテンツを検査することすら可能です。

mysqldumpの基本的な構文は以下の通りです：

mysqldump -u [username] -p [database_name] > [backup_file_name].sql

これらのコンポーネントを分解して見ていきましょう：

  • -u [username]: 接続に使用するMySQLユーザーを指定します。このユーザーには、データベースを読み取るための十分な権限が必要です。
  • -p: 指定したユーザーのパスワードを入力するようにプロンプトを表示します。コマンドライン履歴にパスワードが保存される可能性があるため、コマンドラインに直接パスワードを含めるよりも、mysqldumpにプロンプトを表示させる方が通常は安全です。
  • [database_name]: バックアップしたいデータベースの名称です。
  • >: これはShellのリダイレクト（Redirect）演算子です。mysqldumpコマンドの出力（つまりSQLステートメントのストリーム）を指定したファイルに送信します。
  • [backup_file_name].sql: バックアップファイルのパスと名称です。.sql拡張子は、SQLダンプファイルの標準フォーマットです。

2. 単一のデータベースをバックアップする

よく知られているworldデータベースをバックアップするには、以下のようなコマンドを実行します：

mysqldump -u root -p world > world_backup_20231027.sql

このコマンドを実行すると、システムはrootユーザーのパスワード入力を求めます。認証（Authentication）に成功すると、mysqldumpはworldデータベースに接続し、そのスキーマとデータを読み取り、必要なすべてのSQLコマンドをworld_backup_20231027.sqlファイルに書き込みます。

バックアップファイルの最初の数行を表示することで、その内容を検証できます：

head -n 20 world_backup_20231027.sql

コメント、CREATE TABLEステートメント、およびINSERTステートメントが表示されるはずです。

-- MySQL dump 10.13  Distrib 8.0.34, for Linux (x86_64)
-- ... (一部のコメントヘッダーを省略) ...
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `city`
--

DROP TABLE IF EXISTSおよびCREATE TABLEステートメントに注目してください。これらのステートメントにより、リストアを実行する際にテーブルの構造が正しく再作成されることが保証されます。

3. 複数のデータベースと特定のテーブルをバックアップする

3.1 複数のデータベースをバックアップする

特定の複数のデータベースをバックアップする必要がある場合は、--databasesオプションの後にデータベース名を指定して使用できます：

mysqldump -u root -p --databases world sys mysql > multi_db_backup.sql

これにより、world、sys、mysqlデータベースのスキーマとデータを含む単一の.sqlファイルが作成されます。出力ファイルには、正しくリストアできるように、各データベースのCREATE DATABASEおよびUSEステートメントも含まれます。

3.2 全てのデータベースをバックアップする

MySQLサーバー上のすべてのデータベース（通常はこの方法でバックアップされない内部のパフォーマンススキーマ / performance schema を除く）をバックアップするには、--all-databasesオプションを使用します：

mysqldump -u root -p --all-databases > all_databases_backup.sql

これは通常、サーバー全体のフルバックアップに使用されます。すべてのデータベースをバックアップすると、データ量によっては非常に大きなファイルが作成される可能性があることに注意してください。

3.3 特定のテーブルをバックアップする

データベース内の特定のテーブルのみをバックアップしたい場合があります。その場合は、データベース名に続けてテーブル名を指定できます：

mysqldump -u root -p world city country > world_cities_countries_backup.sql

このコマンドは、worldデータベース内のcityテーブルとcountryテーブルのみをバックアップします。

4. mysqldumpの重要な高度なオプション

mysqldumpには、バックアップを微調整するための多くのオプションが用意されています。最も一般的に使用されるオプションをいくつか紹介します：

  • --no-data: このオプションは、データを含まず、データベーススキーマ（テーブル構造、ビュー / view、ストアドプロシージャ / stored procedureなど）のみを含むバックアップを作成します。これは、スキーマのテンプレートを作成したり、データベース構造をマイグレーションしたりする際に非常に有用です。
mysqldump -u root -p --no-data world > world_schema_only.sql
  • --no-create-info (または -t): このオプションはデータのみをダンプし、CREATE TABLEステートメントを含みません。これは、リストア時に該当テーブルが既に存在していることを前提としています。
mysqldump -u root -p --no-create-info world city > world_city_data_only.sql
  • --single-transaction: InnoDBテーブル（デフォルトかつ推奨のストレージエンジン）において、整合性バックアップ（Consistent backup）を保証するために極めて重要なオプションです。単一のトランザクション（transaction）内でダンプを実行し、バックアップ中のデータベースに頻繁な書き込みが発生している場合でも、特定時点の状態を反映した一貫性のあるデータを確保します。これは、トランザクションに対して一貫性読み取りビューを設定することで実現されます。注意：このオプションはMyISAMテーブルには無効です。
mysqldump -u root -p --single-transaction world > world_consistent_backup.sql
  • --add-drop-database: ダンプ内の各CREATE DATABASEステートメントの前に、DROP DATABASE IF EXISTSステートメントを追加します。これはリストア時にデータベースを完全に再構築する際に便利ですが、同名の既存データベースが完全に削除されるため、慎重に使用してください。
mysqldump -u root -p --add-drop-database --databases world > world_recreate_backup.sql
  • --routines (または -R): ストアドルーチン（stored routine：ストアドプロシージャおよび関数）をバックアップに含めます。デフォルトでは、mysqldumpはテーブルとビューのみをバックアップします。
mysqldump -u root -p --routines world > world_with_routines.sql
  • --events (または -E): MySQLのイベントスケジューラ（Event Scheduler）のイベントをバックアップに含めます。
mysqldump -u root -p --events world > world_with_events.sql

5. オプションの組み合わせとGzip圧縮

多くの場合、mysqldumpを他のコマンドラインユーティリティと組み合わせて使用します。特に圧縮目的で併用されます。gzipを使用することは、より軽量なバックアップファイルを作成するための一般的なプラクティスです：

mysqldump -u root -p --single-transaction world | gzip > world_backup_compressed.sql.gz

ここで、|（パイプ / pipe）演算子はmysqldumpの出力を直接gzipコマンドに送信し、そこで圧縮が行われて.gzファイルとして書き出されます。これにより、ディスクスペースを大幅に節約でき、またディスクI/Oがボトルネックになっている環境では、バックアッププロセスを高速化することができます。

6. 自動化バックアップの実践 (Linux Cron)

本番環境（プロダクション環境）では、毎回手動でmysqldumpを実行することはありません。代わりに、Linux/Unixシステム上のcronやWindows上のタスクスケジューラなどのジョブスケジューラ（Job scheduler）を使用して自動化を図ります。

日次バックアップのための典型的なcronエントリーは以下のようになります（crontab -eで追加する必要があります）：

0 3 * * * /usr/bin/mysqldump -u backup_user -p'YourSecurePassword' --single-transaction world | gzip > /var/backups/mysql/world_$(date +\%Y\%m\%d_\%H\%M\%S).sql.gz 2>> /var/log/mysql_backup.log

このcronコマンドを分解して見てみましょう：

  • 0 3 * * *: スケジュール時間を指定します：毎日午前3時0分に実行。
  • /usr/bin/mysqldump ...: mysqldump実行ファイルの絶対パスです。
  • -u backup_user -p'YourSecurePassword': 指定されたパスワードを使用して、backup_userとして接続します。
    • セキュリティ上の注意点： 履歴に残るという理由から、コマンドラインでパスワードを直接渡すことは通常推奨されませんが、対話型のパスワードプロンプトを利用できない自動化されたcronジョブにおいては、往々にして必要となります。cronファイルに極めて厳格な権限（パーミッション）が設定されていることを確認してください。自動化スクリプトにおけるより優れた代替手段は、クレデンシャル（Credential）を含む.my.cnfファイルを使用することです。
  • --single-transaction world: worldデータベースを一貫性を保ってダンプします。
  • | gzip: 出力をパイプでgzipに渡し、圧縮します。
  • > /var/backups/mysql/world_$(date +\%Y\%m\%d_\%H\%M\%S).sql.gz: 圧縮された出力を特定ディレクトリのファイルにリダイレクトします。$(date +\%Y\%m\%d_\%H\%M\%S)の部分はファイル名にタイムスタンプを動的に生成し、各バックアップを一意なものにします。%文字の前のバックスラッシュ\に注意してください。cronは%を異なる意味で解釈するため、このエスケープが必要です。
  • 2>> /var/log/mysql_backup.log: mysqldumpまたはgzipコマンドからのエラーメッセージ（標準エラー出力 / stderr）をログファイルにリダイレクトし、追記（>>）方式で書き込みます。これは自動化タスクのモニタリングにおいて不可欠です。

mysqldumpを使用して定期的に一貫性のあるバックアップを作成することは、データベース管理の基礎的な部分です。最小権限の原則（Principle of least privilege）に従い、バックアップ用のユーザーには、バックアップ対象となるデータベースに対する必要最低限のSELECT、LOCK TABLES、およびEVENT権限のみが付与されていることを常に確認してください。