はじめに
エンジニアとして、現場に入ると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名].[テーブル名] | 指定されたテーブルをダンプしない |