MySQL & MariaDBの世界

【完全ガイド】mysqldumpでMySQLデータベースを安全にエクスポートする方法と復元手順

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

1. はじめに

MySQLは、さまざまなWebサービスや業務システムで利用されている代表的なリレーショナルデータベース管理システムです。そのMySQLで構築されたデータベースは、日々の業務やサービスの中核を担っていることが多く、もし障害やミスによってデータが失われてしまった場合、事業に大きな影響を及ぼす可能性があります。

そこで重要となるのが、「バックアップ」です。MySQLでは公式ツールとして「mysqldump（マイエスキューエルダンプ）」というコマンドラインツールが提供されており、データベースの内容を簡単にエクスポート（書き出し）してバックアップファイルとして保存することができます。

このツールを活用することで、システム障害時のリカバリーや他環境への移行、定期的なアーカイブ保存など、多様な運用ニーズに応えることが可能です。

本記事では、「mysqldump」を使ったMySQLデータベースのエクスポート方法について、基本的な使い方から応用的な設定までを詳しく解説していきます。初心者の方でも実践できるよう、具体的なコマンド例を交えながら丁寧に紹介していきますので、ぜひ参考にしてみてください。

2. mysqldumpの基本的な使い方

mysqldumpは、MySQLデータベースの内容をSQLファイルとしてエクスポートするためのコマンドラインツールです。基本的な構文は非常にシンプルで、数行のコマンドでバックアップが可能です。このセクションでは、よく使われる基本的なエクスポート方法について解説します。

単一データベースのエクスポート

もっとも一般的なのが、1つのデータベースを丸ごとエクスポートするケースです。

mysqldump -u ユーザー名 -p データベース名 > backup.sql

実行後、パスワードの入力が求められ、指定したデータベースの内容が backup.sql というファイルに出力されます。

複数データベースのエクスポート

複数のデータベースを同時にバックアップしたい場合は、--databases オプションを使用します。

mysqldump -u ユーザー名 -p --databases db1 db2 db3 > multi_backup.sql

この形式では、対象のデータベースごとに CREATE DATABASE 文が含まれるため、インポート時も再現性が高く便利です。

全データベースのエクスポート

サーバー上の全データベースをバックアップしたい場合は、--all-databases オプションを使用します。

mysqldump -u ユーザー名 -p --all-databases > all_backup.sql

このコマンドはMySQLサーバーに存在するすべてのデータベース（mysql、information_schema、performance_schemaなども含む）を一括してエクスポートします。

出力ファイルの命名に日付を含める例

定期バックアップなどでは、出力ファイル名に日付を含めると管理がしやすくなります。以下はUNIXシェルでの日付付き出力例です。

mysqldump -u ユーザー名 -p データベース名 > backup_$(date +%F).sql

このようにすることで、backup_2025-04-13.sql のようなファイルが自動生成され、バックアップの管理が容易になります。

3. エクスポートのバリエーション

mysqldumpは、単にデータベース全体を出力するだけでなく、状況に応じた柔軟なエクスポートも可能です。この章では、特定のテーブルだけを抽出する方法や、スキーマだけ・データだけを出力する方法、条件を指定したデータのエクスポートなど、さまざまな応用的な使い方を紹介します。

特定のテーブルだけをエクスポートする

あるデータベースの中で、特定のテーブルだけをバックアップしたい場合は、テーブル名を明示することで実現できます。

mysqldump -u ユーザー名 -p データベース名 テーブル1 テーブル2 > selected_tables.sql

例：

mysqldump -u root -p mydb users orders > users_orders.sql

このコマンドは、mydbというデータベースのうち、usersとordersテーブルだけをエクスポートします。

データのみ、スキーマのみのエクスポート

mysqldumpには、スキーマ定義のみ、またはデータのみを出力するオプションがあります。

  • スキーマ（構造）のみを出力する：
  mysqldump -u ユーザー名 -p --no-data データベース名 > schema_only.sql
  • データのみを出力する（CREATE TABLE文を含めない）：
  mysqldump -u ユーザー名 -p --no-create-info データベース名 > data_only.sql

これらは、開発環境と本番環境でスキーマだけを共有したい場合や、差分データを投入したい場合などに有効です。

条件付きでデータをエクスポート（–where）

データの一部だけを抽出してエクスポートしたい場合は、--whereオプションが使えます。SQLのWHERE句と同様の構文を使用します。

mysqldump -u ユーザー名 -p データベース名 テーブル名 --where="条件式" > filtered_data.sql

例：

mysqldump -u root -p mydb users --where="created_at >= '2025-01-01'" > users_2025.sql

この例では、usersテーブルの中で、2025年以降に作成されたレコードだけをエクスポートします。

4. よく使うオプションとその解説

mysqldumpはシンプルなコマンドでありながら、多数のオプションを組み合わせることで、より安全かつ効率的なバックアップが可能になります。この章では、実務で特に使用頻度の高いオプションを厳選し、その役割と使いどころを解説します。

–single-transaction：トランザクションの整合性を保つ

mysqldump -u ユーザー名 -p --single-transaction データベース名 > backup.sql

このオプションは、InnoDBのようなトランザクション対応ストレージエンジンを使用している場合に有効です。ダンプ処理を1つのトランザクションとして実行することで、エクスポート中の整合性を保ち、読み取りロックをかけずに済みます。バックアップ中もサービスを止めずに運用を続けたい場合に非常に便利です。

※MyISAMなどのトランザクション非対応エンジンでは効果がありません。

–quick：メモリ使用量を抑える

mysqldump -u ユーザー名 -p --quick データベース名 > backup.sql

このオプションを付けることで、mysqldumpは全データを一括で読み込まず、1行ずつ逐次取得するようになります。これにより、メモリ消費を抑えながらエクスポートでき、大規模なテーブルでも処理が安定しやすくなります。

–routines、–events：ストアドプロシージャやイベントの出力

デフォルトでは、ストアドプロシージャやイベントはエクスポート対象外です。以下のオプションを使うことで、これらも含めたバックアップが可能になります。

mysqldump -u ユーザー名 -p --routines --events データベース名 > backup_with_logic.sql
  • --routines：ストアドプロシージャとファンクションを含める
  • --events：スケジュールイベントを含める

業務ロジックがデータベース側に多く組み込まれている場合は、これらを忘れずに出力しましょう。

–add-drop-table：テーブル上書き時に便利

mysqldump -u ユーザー名 -p --add-drop-table データベース名 > backup.sql

このオプションを付けると、各テーブルのエクスポート前に DROP TABLE IF EXISTS 文が追加されます。インポート先にすでに同名のテーブルがある場合、自動的に削除されてから再作成されるため、上書きインポート時に重宝します。

–lock-tables：MyISAMのロック制御に有効

mysqldump -u ユーザー名 -p --lock-tables データベース名 > backup.sql

InnoDBでは通常使用されませんが、MyISAMを使用している場合は、書き込みを防止するためにテーブルをロックしてからエクスポートします。整合性を重視するバックアップ時に使用します。

5. エクスポートファイルのインポート方法

mysqldumpでエクスポートしたSQLファイルは、MySQLの標準的なインポート方法を使って復元（リストア）できます。この章では、バックアップファイルを用いたインポートの基本から、実務で役立つリストアの応用例、注意点までを詳しく解説します。

基本的なインポートコマンド

エクスポートされたSQLファイルは、mysqlコマンドを使って簡単にインポートできます。基本構文は以下の通りです。

mysql -u ユーザー名 -p データベース名 < backup.sql

例：

mysql -u root -p mydb < backup.sql

このコマンドを実行すると、backup.sqlに含まれるSQL文が順に実行され、データベースが元の状態に復元されます。

新しいデータベースへのインポート

バックアップファイルには CREATE DATABASE 文が含まれていないこともあるため、別の名前のデータベースにインポートしたい場合は、事前に新しいデータベースを作成しておく必要があります。

例：新しいデータベース「mydb_restore」を作成してインポート

CREATE DATABASE mydb_restore;
mysql -u root -p mydb_restore < backup.sql

※ --databases オプション付きでエクスポートされたSQLには CREATE DATABASE 文が含まれるため、その場合は上記手順と異なる点に注意しましょう。

圧縮されたファイル（.gz）のインポート

バックアップファイルを gzip などで圧縮している場合、展開しながら直接インポートすることも可能です。

gunzip < backup.sql.gz | mysql -u ユーザー名 -p データベース名

この方法を使うことで、ディスク容量を節約しつつ迅速に復元を行うことができます。

インポート時のよくあるエラーと対処法

エラー内容原因対処法
ERROR 1049 (42000): Unknown database対象のデータベースが存在しない事前にCREATE DATABASEで作成しておく
Access deniedパーミッション不足や誤った認証情報ユーザー名やパスワード、権限を再確認
ERROR 1064 (42000): You have an error in your SQL syntaxSQL文の形式がバージョン間で不整合使用しているMySQLのバージョンに適合しているか確認

まとめ：インポートはエクスポートとセットで考える

mysqldumpで取得したバックアップファイルは、ただ取得するだけでは意味がありません。いざというときに復元できる状態にしておくことが、バックアップの本当の価値です。そのため、定期的にインポートのテストを行い、ファイルが正しく読み込めるかどうか確認しておくことをおすすめします。

6. 実践的なTipsと注意点

mysqldumpは手軽に使える一方で、大規模なデータベースや実運用環境では、慎重な運用や工夫が必要になるケースもあります。この章では、実務で役立つテクニックや、トラブルを未然に防ぐための注意点を紹介します。

大規模データベースはgzipで圧縮する

mysqldumpで出力されるSQLファイルは、テキスト形式のため非常に大きくなることがあります。特に数GBを超えるような大規模データベースの場合は、gzipと組み合わせて出力ファイルを圧縮するのが一般的です。

mysqldump -u ユーザー名 -p データベース名 | gzip > backup.sql.gz

この方法により、ディスク容量を大幅に節約できるほか、リモート転送時の負荷も軽減されます。

エクスポートとインポートの検証を習慣化する

バックアップは取得できても、いざというときに「インポートできない」状況では意味がありません。以下のような運用をおすすめします：

  • 定期的にバックアップファイルを別環境でリストアしてテストする
  • ファイルが破損していないかをmd5sumやsha256sumでチェックする
  • 重要なデータベースは複数世代でバックアップを保存する

バージョン差異による不整合に注意

MySQLのバージョンがエクスポート元とインポート先で異なる場合、構文や内部仕様の違いにより、SQLファイルが正常に実行できないことがあります。

  • 可能であれば同一バージョンで運用する
  • バージョンを跨ぐ場合はオプションで制御する（例：--set-gtid-purged=OFF）
  • バージョンアップ前後ではスキーマ定義の互換性を必ず確認する

自動化にはcronとスクリプトを活用

日次や週次で自動的にバックアップを取得したい場合、シェルスクリプトとcronを使うことで効率的に管理できます。

#!/bin/bash
DATE=$(date +%F)
mysqldump -u root -p[パスワード] mydb | gzip > /backup/mydb_$DATE.sql.gz

このようなスクリプトを /etc/cron.daily/ に配置することで、毎日自動でバックアップを取得できます。

※セキュリティの観点から、パスワードの直接記述は避け、.my.cnfファイルで安全に管理する方法が推奨されます。

セキュリティにも配慮を

バックアップファイルには機密情報が含まれる可能性があります。以下の対策を講じましょう：

  • ファイルの保存先には適切なアクセス制限を設定
  • 暗号化して保存・転送（例：GPGやSFTPを活用）
  • クラウド上での保管時には自動バックアップの設定やライフサイクル管理も検討

7. よくある質問（FAQ）

ここでは、mysqldumpを使用する際に多くの人が疑問に思いやすいポイントや、実際に発生しやすいトラブルについて、Q&A形式でまとめて解説します。

Q1. エクスポート時に「Access denied」のエラーが出るのはなぜですか？

A. 指定したMySQLユーザーに、対象データベースへの「SELECT」や「LOCK TABLES」などの権限が不足している可能性があります。必要な権限を確認し、不足している場合は管理者に追加してもらいましょう。また、テーブルロックに失敗している場合は、--single-transactionオプションを使うことで回避できることもあります。

Q2. バックアップファイルのサイズが非常に大きくなります。軽量化する方法はありますか？

A. 大きなテーブルや大量のデータがある場合、SQLファイルはGB単位に達することもあります。以下の方法で軽量化が可能です：

  • gzipなどで圧縮して保存（例：mysqldump ... | gzip > backup.sql.gz）
  • --no-dataや--no-create-infoオプションで必要な部分だけ出力
  • --whereオプションでデータを絞って出力（特定期間のデータなど）

Q3. 特定のテーブルだけをエクスポートしたいのですが、どうすればいいですか？

A. エクスポートするテーブル名をコマンド末尾に列挙することで、任意のテーブルのみ出力できます。

mysqldump -u root -p mydb users orders > selected.sql

複数のテーブルに対応しているので、必要なテーブルだけを選んでバックアップしたい場合に便利です。

Q4. ストアドプロシージャやイベントがエクスポートされません。なぜですか？

A. mysqldumpはデフォルトではストアドプロシージャ（ルーチン）やスケジュールイベントを含みません。出力対象に含めたい場合は、以下のオプションを追加してください。

--routines --events

また、対象のユーザーがそれらのオブジェクトにアクセスできる権限を持っているかも確認しておきましょう。

Q5. 別サーバーにファイルを転送してインポートしたいときの注意点は？

A. 主な注意点は以下の通りです：

  • 文字コード：サーバー間で文字コードが異なる場合、文字化けの原因になります。--default-character-set=utf8などで明示すると安全です。
  • バージョン差異：エクスポート元とインポート先のMySQLバージョンに互換性があるかを確認してください。
  • ネットワーク転送：scpやrsync、もしくはSFTPなどを使って安全に転送しましょう。
  • ファイル破損のチェック：md5sumやsha256sumで転送後のファイル整合性を確認することを推奨します。

Q6. WindowsとMac/Linuxでコマンドに違いはありますか？

A. 基本的なコマンド構文は同じですが、シェルの仕様やバッチ処理の方法、日付コマンドの書き方などに差異があります。特に日付付きファイル名の自動生成では、WindowsではPowerShellや%DATE%変数を、LinuxやMacではdateコマンドを使用します。

8. まとめ

本記事では、MySQLデータベースのバックアップ・移行で活躍するツール「mysqldump」について、基本的な使い方から応用テクニック、トラブル時の対処法までを幅広く解説してきました。

mysqldumpは非常にシンプルな構文で使える反面、目的に応じて適切なオプションやコマンドの工夫をしなければ、バックアップの質や復元の確実性に大きな差が出てしまいます。

✅ 本記事で学んだ主要ポイント

  • mysqldumpの基本構文と3つのエクスポート方法（単一・複数・全データベース）
  • スキーマだけ、データだけ、特定テーブルのみの出力など多彩なバリエーション
  • –single-transactionや–routinesなど、実務で役立つ主要オプションの解説
  • 復元時の基本コマンドと、インポートエラーの対処法
  • gzipとの組み合わせやcron自動化など、実務向けTips
  • FAQによる実用的なトラブルシューティングの知識

🛡 mysqldump活用のベストプラクティス

  1. バックアップは「取得」するだけでなく「復元できる」ことを確認する
  2. バージョン差異や文字コードなど、環境差による不具合に備える
  3. 運用現場では圧縮・自動化・権限管理まで一貫して設計する
  4. ストアドプロシージャ・イベントなども含めて本番同様の構成でバックアップする

適切に設計・運用されたmysqldumpによるバックアップ体制は、万一の障害時にも迅速な復旧を可能にし、信頼性の高いシステム運用に寄与します。特に企業システムやWordPressサイトなど、データの損失が致命的になりかねない場面では、mysqldumpの理解と活用が極めて重要です。

本記事を参考に、ぜひ自社や自分の運用に最適なバックアップ戦略を構築してみてください。