mysqldumpまとめ

あまり見やすくて、わかりやすい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)|テーブルの作成を行わずにダンプ|

[※その他よく使うオプション](#その他よく使うオプション)

# 使用例

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

## 定義とデータのダンプ

```sh
# データベース
$ 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 
```

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

```sh
# データベース
$ 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
```

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

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

# テーブル（データベースを１つ選んでダンプ）
mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME
```

## 定義のみダンプ

```sh
# データベースとテーブル定義をダンプ
$ 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
```

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

```sh
# データベースとテーブル
$ 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
```

## データのみのダンプ

```sh
# データベースのデータ
$ 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に反映させるには以下のコマンドを実行します


```sh
# 出力されたスクリプトファイルの実行
$ 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|１つのINSERT文で複数のVALUEを構文を利用する。これにより、ダンプで出力されるファイルサイズが減り、インポート時間も短縮される| 
|--lock-tables |テーブルをダンプする前にロックする|
|--set-charset|	SET NAMES default_character_setを出力に追加する|
|--lock-all-tables|データベース内のテーブル全てをロックする|
|--master-data|バイナリログファイルの名前と場所を出力に含める。--single-transactionが指定されてない場合は、--lock-all-tablesが有効になる。|
|--ignore-table=[DB名].[テーブル名]|指定されたテーブルをダンプしない|


---
#### 参考文献
* [MySQL 5.6 リファレンスマニュアル ](https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html)
* [mysqlのバックアップ(mysqldump)のロック問題](http://blog.seeds-std.co.jp/entry/3405.html)
* [よく使うmysqldumpのオプションと使用例](http://qiita.com/ryounagaoka/items/7be0479a36c97618907f)