mysqlデータのバックアップとリストア

バックアップがあるかどうかでどれだけのプロジェクトが助けらたか。。。過去には痛い経験もある私ヨナハです。
とても単純ですが、とても重要な処理なので重宝してほしい処理です。

僕は、本番のデータをローカルの開発環境に持ってくるときによく使います。
phpMyAdmminとかで、本番から「エクスポート」して、開発環境に「インポート」という作業とおんなじです。
ですが、コマンドラインで行うと劇的に速いので、今回はコマンドで行うやり方を書きます。

データベースごとフルバックアップを取る方法

特定のデータベースのみのバックアップ

$ mysqldump -u root -p DB名 > dump_data.sql

ホスト名が「localhost」でない場合は、「-h ホスト名」を付けてあげる
$ mysqldump -u root -p -h ホスト名 DB名 > dump_data.sql

フルバックアップの際に出力されるSQL
上記MySQLをdumpした際に出力されるSQLの内容には、「DROP(削除)」「CREATE(作成)」「INSERT(データ作成)」という手順でSQLが作成されるので、リストアの際にはいったんすべて削除され新しく作成されます。

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

$ mysqldump -u root -x --all-databases > dump_all_database.sql

テーブルごとのバックアップ

$ mysqldump -u root -p DB名 users > table_data.dump

リストアでデータを反映する

データベースのリストア

$ mysql -u root -p DB名 < dump_data.sql 

テーブルのリストア

$ mysql -u root -p DB名 < table_data.sql 

リモートでバックアップできない場合
テーブルロック権限(LOCK TABLES)がない場合に
Access denied for user … when using LOCK TABLES
というエラーが出て、リモート接続時のデータバックアップが出来ない場合があります。
その際の解決方法としては、InnoDBデータベースのバックアップ時にロックを取得しないというオプションを付けると解決する場合があります。

–single-transaction

というオプションです。

Windows環境にあるMySQLにリストアする場合は、コマンドが聞かない場合などあると思いますので、コマンドでMySQLにログインした後に、リストアしたほうが楽かもしれないです。
MySQLにログイン後にリストア

$ mysql -uroot -p 
> USE DB名
> SOURCE dump_data.sql

バックアップデータをローカルにダウンロードする

Windwos環境の人は、コマンドとかではなく「WinSCP」等のソフトからダウンロードしたほうがいいかもです。
MacだとデフォルトでUnixのコマンドが使えるので、コマンド一つでサーバからファイルをダウンロードすることが可能です。

リモート → ローカルへのダウンロード

# scp [ユーザ名]@[ホスト名]:[ファイル名] [ローカルパス(ダウンロード先)]
$ scp tanaka@123.123.123.123:/home/tanaka/dump_date.sql /Users/yonaha/Desktop/

ローカル → リモートへのアップロード

# scp [ローカルのファイル名] [ユーザ名]@[ホスト名]:[アップロード先のファイルパス]
$ scp local_data.zip tanaka名@123.123.123.123:/home/tanaka/upload_data.zip

こんな感じでメモ書きでした。


プロに聞いてみる
株式会社adanはエンジニアのキャリアに真剣に向き合っています。
企業の情報エンジニアとしてのキャリアアップの方法、現在の待遇(給料・報酬)未経験からのステップアップ方法などのご相談を受け付けています。
お気軽にお問合せください。