前回は、基本的なダンプでバックアップする方法をまとめました。
mysqldumpの使い方 バックアップ＆リストア編 基本の章
今回は、前回予告した通り、DDLファイルとデータのファイルを別々に分けてバックアップする方法をまとめたいと思います。
この方法で、データをカンマ区切り(CSV)や、タブ区切り(TSV)などの形式で出力することもできます。
最初に
今回使うオプションは、tab(T)オプションです。オプションには、ディレクトリパスを指定します。
このオプションの使用条件として、取得できるダンプは1つのデータベースのみです。
DDLとデータが分かれて出力されるため、リストアの方法も異なります。
そして、一番重要なことですが、MySQLが立ち上がっている(mysqldが動作している)マシンで、mysqldumpコマンドを使わないといけません。
※内部でSELECT INTO OUTFILEを使っているためです。
つまりは、host(h)オプションが自身のマシンでなければなりません。
ですので、今回はhost(h)オプションは省略しています。
※localhostや127.0.0.1、もちろん自分自身のIPを指定しても動作します。
コマンドと出力
まずは、なにも指定せずルート(WindowsではCドライブ)直下のテーブルをダンプするコマンドは以下の通りです。
Linux
mysqldump -uroot -p --tab=/backup/ database_name table_name
Windows
mysqldump -uroot -p -tab=C:\backup\ database_name table_name
なお、tab(T)オプションの省略形の場合は、=は不要です。
mysqldump -uroot -p -T /backup/ database_name table_name
なお、ディレクトリは先に生成しておく必要があり、ディレクトリがないと出力エラーとなります。
出力ファイル名は、DDLが[テーブル名].sql、データが[テーブル名].txtです。
私のおすすめとして、コマンドで行うデータベースのバックアップには、verbose(v)オプションをつけておきましょう。
どのテーブル出力中か確認できて、待つ時間のストレスが軽減されます。
DDLの編集
さて、このコマンドでやりたいことは完了してしまいました。DDL文とデータのファイル別出力と、テーブルごとにファイルを分割してディレクトリに出力がされていると思います。
DDL文に関しては、データを出力しない単一テーブルのmysqldumpと同じように出力されます。
DDL文の出力だけでいい場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ -d database_name table_name
mysqldump -uroot -p -T /backup/ -d -compact -compatible=mysql323 database_name table_name
区切り文字と囲み文字
デフォルトでは、フィールドの区切り文字はタブ文字、囲み文字はなし、行の区切りはLFとなっています。つまり、特に指定しない場合、タブ文字で区切った改行コードがLFのファイルが出力されます。
○フィールドの区切り文字
まずは、フィールドの区切り文字から、指定します。フィールドの区切り文字は、fields-terminated-byオプションで指定します。
カンマ区切りにしたい場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ --fields-terminated-by="," database_name table_name
区切り文字は1文字である必要はないので、「separate」という文字列を指定することもできます。
なお、囲み文字がなしの状態で、データに区切り文字がある場合は、エスケープされて出力されます。
○囲み文字
続いて、囲み文字です。囲み文字は、fields-enclosed-byオプションで指定します。
デフォルトは囲み文字は指定されていないため、文字も数値もそのまま出力されています。
フィールドの文字や数値だという属性はDDLに持っているので不要ということでしょう。
囲み文字をダブルクォーテーションにしたい場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ --fields-enclosed-by="\"" database_name table_name
「\”」のところを、シングルクォーテーションなら「’」としてください。
こちらも1文字である必要はないため、「quote」という文字列を指定することもできます。
さて、出力データを見ていただければわかりますが、全てのデータが囲まれてしまっています。
数値のカラムは囲みたくない場合は、fields-optionally-enclosed-byオプションを使用します。
mysqldump -uroot -p -T /backup/ --fields-optionally-enclosed-by="\"" database_name table_name
よく使うCHAR、VARCHAR、TEXT、DATE、DATETIME、TIMESTAMPが囲まれることは覚えておきましょう。
○行の区切り文字
最後に行の区切り文字です。行の区切り文字はlines-terminated-byオプションで指定します。
行の区切り文字をWindows用にCRLFを指定する場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ --lines-terminated-by="\r\n" database_name table_name
最近はどの改行文字でも問題ないことが多いですが、テキストエディタがなくWindowsのメモ帳で開かないといけない場合などに便利です。
○固定長出力
フィールドの区切り文字と囲み文字に空文字(“”)を指定すると、固定長での出力ができます。
mysqldump -uroot -p -T /backup/ --fields-terminated-by="" --fields-enclosed-by="" database_name table_name
固定長とはいえ、フィールドの桁数はテーブル定義に依存しますので、それ専用のテーブルが必要になるでしょう。
○データのエスケープ
今まで出力してきた中で、区切り文字や囲み文字が\でエスケープされていましたが、それ以外の文字でもエスケープできます。エスケープ文字はfields-escaped-byオプションで指定できます。
エスケープ文字に空文字(“”)を指定すると、NULL値がデフォルトでは「\N」と出力されていましたが、「NULL」という文字で出力されるようになります。
mysqldump -uroot -p -T /backup/ --fields-escaped-by="" database_name table_name
mysqldump -uroot -p -T /backup/ --hex-blob database_name table_name
最後に
最後に、オプションを組み合わせて、カンマ区切り(CSV)を出力します。
mysqldump -uroot -p -T /backup/ --fields-terminated-by="," --fields-optionally-enclosed-by="\"" --lines-terminated-by="\r\n" --fields-escaped-by="" database_name table_name
いかがでしたでしょうか？
一工夫加えれば、便利なダンプが取得できます。
ぜひぜひ、いろんなダンプの仕方を試してみてください！
