edy hub

プログラミングやライフスタイルについて書き綴っています

MySQLのデータをダンプする〜mysqldumpコマンド〜

はじめに

エンジニアとして、現場に入るとdumpファイルをもらうことがあります。 特に入社直後は開発環境の構築からスタートすると思うので、まず最初にdump.sqlっぽい名前のファイルを付与されるんじゃないでしょうか?

しかし、その後dumpファイルをただ貰うだけでなく、新メンバーに付与したり、エンジニア以外のメンバーの文責用にDBのデータを抽出する機会があります。 その際に、mysqldumpコマンドを使用することがありましたので、まとめておきたいと思います。

基本的なオプション

オプション 意味 説明
-u ユーザー名(user) サーバに接続するユーザー名
-p パスワード(password) パスワードを指定してログイン
-h ホスト名(host) 接続するサーバのホスト名(ex. localhost, 127.0.0.1)指定しないとlocalhostになる
-B データベース(dababase) 複数のデータベースを名前を指定してダンプ
-A すべてのデータベース(all) 複数のデータベースをまとめてダンプ
-d 定義のみ(no-data) 定義のみダンプを取りたいときに指定
-n データベースは無視(no-create-db) データベースを作成せずにダンプ
-t テーブルは無視(no-create-info) テーブルの作成を行わずにダンプ

※その他よく使うオプション

使用例

以下オプションの値をそれぞれの環境にあわせて、使用してください

定義とデータのダンプ

# データベース
$ mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME

#テーブル
$ mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME TABLE_NAME > OUTPUT_FILE_NAME

#テーブルの定義とデータのダンプ
$ mysqldump -u USER_NAME -p -h HOST_NAME -A -n > OUTPUT_FILE_NAME 

複数のデータベース・テーブルのダンプ(定義とデータ)

#データベース
$ mysqldump -u USER_NAME -p -h HOST_NAME -B DB_NAME1 [DB_NAME2 ...] > OUTPUT_FILE_NAME

#テーブル
$ mysqldump -u USER_NAME -p -h HOST_NAME TABLE_NAME1 [TABLE_NAME2 ...] > OUTPUT_FILE_NAME

全てのデータベース・テーブルのダンプ(定義とデータ)

# データベース
$ mysqldump -u USER_NAME -p -h HOST_NAME -A > OUTPUT_FILE_NAME

# テーブル(データベースを1つ選んでダンプ)
# mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME

定義のみダンプ

#データベースとテーブル定義をダンプ
$ mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME -d > OUTPUT_FILE_NAME

#データベースの定義のみダンプ
$ mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME -d -t > OUTPUT_FILE_NAME

#テーブルの定義のみダンプ
$ mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME -d -n > OUTPUT_FILE_NAME

全てデータベース・テーブルの定義をダンプ

#データベースとテーブル
$ mysqldump -u USER_NAME -p -h HOST_NAME -A -d > OUTPUT_FILE_NAME

#データベース
$ mysqldump -u USER_NAME -p -h HOST_NAME -A -d -t > OUTPUT_FILE_NAME

#テーブル
$ mysqldump -u USER_NAME -p -h HOST_NAME -A -d -n > OUTPUT_FILE_NAME

データのみのダンプ

#データベースのデータ
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME > OUTPUT_FILE_NAME

#テーブルのデータ
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME > OUTPUT_FILE_NAME

#全てのデータ
$ mysqldump -u USER_NAME -p -h HOST_NAME -A -t > OUTPUT_FILE_NAME

出力ファイルの実行

出力したOUTPUT_FILE_NAMEをMySQLに反映させるには以下のコマンドを実行します

#出力されたスクリプトファイルの実行
$ mysql -u USER_NAME -p -h HOST_NAME DB_NAME < OUTPUT_FILE_NAME

その他よく使うオプション

保存データ容量が多かったり、複数人数で開発を行っているDBに対してdumpを行う場合には、消費メモリを抑えたり、DBをロックしないようにする必要があります。 適宜必要なオプションを取捨選択して使用してください。

MySQLのエンジンとしてInno DBを想定しています

オプション 説明
--single-transaction ダンプを行う前にBEGINステートメントを発行する。内部的にスナップショットを取ってダンプを行うので、DBをロックせずに整合性の取れたダンプを取ることが可能
--quick テーブルの全レコードをメモリにバッファせずに、1行ずつダンプを行う。データ量の大きいテーブルのダンプ時にメモリを逼迫せずにダンプを行える。
--opt --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset の短縮形。
--add-drop-table CREATE TABLEの前にDROP TABLEを含める
--add-locks 各テーブルのINSERT前後にLOCK_TABLE文とUNLOCK_TABLE分を含めることでINSERT速度が向上する
--create-options MySQLに固有なテーブルオプションを CREATE TABLEに含める
--disable-keys 各テーブルで、全てのレコードのインポートが完了するまでインデックスを作らないようにする
--extended-insert 1つのINSERT文で複数のVALUEを構文を利用する。これにより、ダンプで出力されるファイルサイズが減り、インポート時間も短縮される
--lock-tables テーブルをダンプする前にロックする
--set-charset SET NAMES default_character_setを出力に追加する
--lock-all-tables データベース内のテーブル全てをロックする
--master-data バイナリログファイルの名前と場所を出力に含める。--single-transactionが指定されてない場合は、--lock-all-tablesが有効になる。
--ignore-table=[DB名].[テーブル名] 指定されたテーブルをダンプしない

参考文献