1186
1089

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

mysqldumpまとめ

Last updated at Posted at 2015-10-06

あまり見やすくて、わかりやすい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

# whereで指定したデータのみ
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME --where 'deleted_at IS NULL' > 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名].[テーブル名] 指定されたテーブルをダンプしない

参考文献

1186
1089
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1186
1089

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?