mysqldumpコマンドの基本的な使い方から、特定のデータベース、複数のデータベース、全データベースのバックアップ、そして復元方法までを解説します。
MySQLデータベースのバックアップコマンドを忘れてしまうので、コマンドラインツールmysqldumpを使ったバックアップと復元方法についてまとめます。
mysqldumpは、MySQLデータベースの構造とデータをSQL形式のファイルとしてエクスポートするためのクライアントプログラムです。
このSQLファイルでデータベースを復元できます。
バックアップの前MySQLの基本的なコマンド一覧。
mysql -u [ユーザー名] -p
mysql -u root -p
パスワードの入力を求められたら入力します。
現在存在するデータベースの一覧を表示します。
SHOW DATABASES;
新しいデータベースを作成します。
CREATE DATABASE [データベース名] CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE new_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
操作したいデータベースを選択します。
USE [データベース名];
USE new_database;
選択したデータベース内のテーブル一覧を表示します。
SHOW TABLES;
新しいMySQLユーザーを作成し、パスワードを設定します。
CREATE USER '[ユーザー名]'@'localhost' IDENTIFIED BY '[パスワード]';
例: new_userというユーザーをlocalhostからアクセス可能にし、パスワードをnew_passwordにする場合
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';
既存ユーザーのパスワードを変更します。
ALTER USER '[ユーザー名]'@'localhost' IDENTIFIED BY '[新しいパスワード]';
例: new_userのパスワードをchanged_passwordに変更する場合
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'changed_password';
作成したユーザーに特定のデータベースへのアクセス権を付与します。
GRANT ALL PRIVILEGES ON [データベース名].* TO '[ユーザー名]'@'localhost';
例: new_userにnew_databaseへの全ての権限を付与する場合
GRANT ALL PRIVILEGES ON new_database.* TO 'new_user'@'localhost';
権限の変更をMySQLに反映させます。
FLUSH PRIVILEGES;
GRANT文で付与できる主なアクセス権の種類は以下の通りです。
| 権限の種類 | 説明 |
|---|---|
ALL PRIVILEGES | 全ての権限を付与します。 |
SELECT | データの読み取りを許可します。 |
INSERT | データの挿入を許可します。 |
UPDATE | データの更新を許可します。 |
DELETE | データの削除を許可します。 |
CREATE | データベースやテーブルの作成を許可します。 |
ALTER | テーブル構造の変更を許可します。 |
DROP | データベースやテーブルの削除を許可します。 |
INDEX | インデックスの作成・削除を許可します。 |
REFERENCES | 外部キー制約の作成を許可します。 |
GRANT OPTION | 他のユーザーに権限を付与する権限を許可します。 |
最も一般的なコマンド、データベース全体をバックアップする方法です。 基本的にはデータベース名を指定して実行します。
mysqldump -u [ユーザー名] -p [データベース名] > [出力ファイル名].sql
例: mydatabaseというデータベースをbackup.sqlにバックアップする場合
mysqldump -u root -p mydatabase > mydatabase_backup.sql
※コマンド実行後、パスワードの入力を求められます。
複数のデータベースを一度にバックアップしたい場合は、--databasesオプションを使用します。
mysqldump -u [ユーザー名] -p --databases [データベース名1] [データベース名2] > [出力ファイル名].sql
例: db1とdb2をmulti_db_backup.sqlにバックアップする場合
mysqldump -u root -p --databases db1 db2 > multi_db_backup.sql
MySQLサーバー上の全てのデータベース（mysql、information_schema、performance_schemaなどのシステムデータベースを含む）をバックアップするには、--all-databasesオプションを使用します。
システムデータベースも含まれるため、ファイルサイズが大きくなる可能性があります。
mysqldump -u [ユーザー名] -p --all-databases > all_databases_backup.sql
バックアップしたSQLファイルからデータベースを復元するには、mysqlコマンドを使用します。
バックアップしたSQLファイルからデータベースを復元するには、mysqlコマンドを使用します。
既存のデータベースに復元する場合、または新しいデータベースを作成して復元する場合。
mysql -u [ユーザー名] -p [データベース名] < [入力ファイル名].sql
例: mydatabase_backup.sqlをmydatabaseに復元する場合
mysql -u root -p mydatabase < mydatabase_backup.sql
既存データベースへの復元 既存のデータベースに復元する場合、データが上書きされる可能性があります。事前に確認してください。
--all-databasesでバックアップしたファイルを復元する場合、データベース名を指定せずにmysqlコマンドを実行します。
mysql -u [ユーザー名] -p < all_databases_backup.sql
| オプション | 説明 |
|---|---|
-u [username] | MySQLに接続するユーザー名を指定します。 |
-p | パスワードの入力を求めます。-p[パスワード]のように直接記述することも可能 |
-h [ホスト名] | MySQLサーバーのホスト名を指定します。デフォルトはlocalhostです。 |
--no-data | テーブル構造のみをバックアップし、データは含めません。 |
--add-drop-table | 各CREATE TABLEステートメントの前にDROP TABLE IF EXISTSステートメントを追加します。復元時に既存のテーブルを削除してから作成します。 |
定期的に自動バックアップを実行するには、Linuxのcronジョブを使います。
例: 毎日午前3時にmydatabaseをバックアップするcron設定
# crontab -e で編集
0 3 * * * mysqldump -u root -p'your_password' mydatabase > /path/to/backup/mydatabase_$(date +\%Y\%m\%d).sql
世代管理する場合はバックアップ作成->古いものを削除するコマンドで対応させます。
本番前にテストすることをお勧めします。
0 3 * * * mysqldump -u root -p'your_password' mydatabase > /path/to/backup/mydatabase_$(date +\%Y\%m\%d).sql && ls -1t /path/to/backup/mydatabase_*.sql | tail -n +4 | xargs -r rm --
世代バックアップのテスト用コマンドです。何が削除されるのか確認できます。
ls -1t /path/to/backup/mydatabase_*.sql | tail -n +4
cronでパスワードを直接記述する場合は、シングルクォーテーションで囲むなど、シェルスクリプトの記述に注意してください。セキュリティを考慮し、パスワードファイルを使用する方法も検討してください。
mysqldumpコマンドはMySQLデータベースのバックアップと復元を行うための強力なツールです。これらのコマンドを理解し、定期的なバックアップを習慣づけることで、データの損失リスクを大幅に軽減できます。
