Webサービスを運用していると、本番からデータを取ってきてローカルやステージング環境で再現したい、という場面がよくあります。
そんなときに使えるmysqldumpのTipsです。
基本形
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] > [ダンプファイル名]
[テーブル名]はスペースで区切って複数のテーブルを記載可能です。
[テーブル名]を省略するとデータベース内の全テーブルを作成する構文を作ります。
取得データを取り込む
ダンプしたデータは以下で反映します。
$ mysql -u [ユーザー名] -p -h [ホスト名] [データベース名] < [ダンプファイル名]
既存のテーブルにデータを追加する
既存であるデータを壊さずに、データだけ追加したいときです。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] -t --insert-ignore > [ダンプファイル名]
-tでCREATE TABLE文を作りません。
--insert-ignoreはinsert時に主キーやユニークインデックスの値が同じデータがすでにある場合、該当データのinsertをスキップします。
既存のテーブルのデータを置き換える
ローカルにあるテーブルをまるっと本番に置き換えたい時に便利です。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] -t --replace > [ダンプファイル名]
--replaceで主キーやユニークインデックスの値が同じデータがすでにある場合、上書きします。
ある条件を満たすデータのみダンプする
select文のwhere構文と似たオプションを仕様してダンプするデータを絞込できます。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] --where'id=1' > [ダンプファイル名]
--whereにWHERE構文で使用する条件を設定します。
ダンプデータを圧縮する
gzipでファイルサイズを圧縮し扱いやすくします。ダンプファイル名の拡張子はgzで。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] | gzip > [ダンプファイル名]
ダンプしたsqlを実行したらエラーが出たときのデバッグ用ダンプ
データの取り込み先でエラーが出た場合はエラー箇所をエディタで調べますが、そんなときはダンプデータが見やすい形式になっていると良いです。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] -t -c --extended-insert=FALSE --where='id=1' > [ダンプファイル名]
-cでINSERTにカラム名を含めます。
--extended-insert=FALSEデフォルトだとINSERT構文は全テーブルデータを1つのINSERTでやってしまいますが、1行が長くなってしまいます。
これを防ぐためにします。
さらに、エラーになったレコードのみ--whereで絞り込みます。
テーブル定義のみ出力する
データを作成したくない時は-dです。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] -d > [ダンプファイル名]
データの件数を制限する
テスト用に最初の10件だけ欲しい場合。--whereを使って制限します。
--where=trueでwhereをスキップするとLIMIT句が使えます。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] --where='true LIMIT 10' > [ダンプファイル名]
csvで出力する
$ mysqldump -u [ユーザー名] -p -h [ホスト名] [データベース名] [テーブル名] --tab=/tmp --fields-terminated-by=,
--tab=ディレクトリ名 ディレクトリ指定構文で、csvのときは必須。
--fields-terminated-by=, 区切り文字を指定します。
--tabで指定したディレクトリ内に[テーブル名].txtのファイルが置かれます。リダイレクトは指定しません。
tsv形式で出力したい場合は--fields-terminated-by=,の指定をなくせばOKです。
また、値を囲いたいときは--fields-enclosed-byを使用します。
--fields-enclosed-by="\""と指定してダブルクウォートで値を囲みます。
データベース全体をすべてダンプする
定義も含めてすべてダンプします。
$ mysqldump -u [ユーザー名] -p -h [ホスト名] –all-databases > [ダンプファイル名]