Edited at

mysqldumpまとめ

More than 1 year has passed since last update.

あまり見やすくて、わかりやすい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名].[テーブル名]
指定されたテーブルをダンプしない



参考文献