概要
mysqldumpを使ったデータベースのバックアップ・リストア手順をまとめます。
実際に作業中に遭遇したエラーとその対処法も合わせて記載します。同じ環境で作業する方の参考になれば幸いです。
バックアップ（dump）→ ファイル転送・保管 → リストア（restore） → 件数確認
お知らせ（採用情報）
最後にお知らせとなりますが、AppTime では一緒に働くメンバーを募集しております。
詳しくは採用情報ページをご確認ください。
みなさまからのご応募をお待ちしております。
手順
1. バックアップ（ダンプファイルの作成）
mysqldump コマンドでデータベースの内容をSQLファイルに書き出します。
以下は、指定したデータベースの内容を /root/backup/backup.sql として保存するシンプルな例です。
mysqldump -u [ユーザー名] -p [データベース名] > /root/backup/backup.sql
コマンド実行後
パスワードの入力が求められます。Enter password: と表示されたら MySQL ユーザーのパスワードを入力してください。
出力先のディレクトリが存在しない場合はあらかじめ作成しておきます。
mkdir -p /root/backup
2. 既存 DB に上書きしたい場合
上記の基本コマンドで作成したダンプファイルをそのままリストアしても、既存のテーブルに上書き反映されません。
既存テーブルのデータ・構造を完全に置き換えたい場合は、--add-drop-table オプションを付けてダンプを作成します。
このオプションを付けると、SQLファイルの各CREATE TABLE文の前に DROP TABLE IF EXISTS 文が挿入されます。リストア時にテーブルが一度削除されてから再作成されるため、データと構造が完全に置き換わります。
mysqldump -u [ユーザー名] -p --add-drop-table [データベース名] > /root/backup/backup.sql
注意
リストア実行時に既存テーブルがDROPされます。本番環境では必ずバックアップを確認してから実行してください。
3. リストア
作成したダンプファイルを対象のデータベースにリストアします。事前にリストア先のデータベースが存在していることを確認してください。
mysql -u [ユーザー名] -p [データベース名] < /root/backup/backup.sql
データベースが存在しない場合
先にCREATE DATABASE [データベース名]; でデータベースを作成してから実行してください。
4. 確認
リストア完了後は、ダンプ元とリストア先でテーブル数・レコード数が一致しているか必ず確認します。
-- テーブル一覧の確認
SHOW TABLES;
-- 特定テーブルのレコード数確認
SELECT COUNT(*) FROM [テーブル名];
作成時のエラーと対処
DEFINER に指定されたユーザーが存在しないエラー
The user specified as a definer ('ユーザー名'@'localhost') does not exist when using
上記のエラーが発生したことがあります。
原因： ダンプファイル内のトリガー・ビュー・ストアドプロシージャに DEFINER として指定されているユーザーが、リストア先のMySQLサーバーに存在しないためです。
対処： エラーメッセージに表示されているユーザーをリストア先のサーバーに作成します。
CREATE USER 'ユーザー名'@'localhost' IDENTIFIED BY '任意のパスワード';
-- 必要に応じて権限も付与する
GRANT ALL PRIVILEGES ON [データベース名].* TO 'ユーザー名'@'localhost';
FLUSH PRIVILEGES;
補足
権限は用途に応じて最小限に設定するのが望ましいです。読み取り専用であれば SELECT 権限のみでも問題ありません。
参考