mysqldumpの使い方 バックアップ&リストア編 tabオプションの章


20141021_01

こんにちは。システムエンジニアのリョータです。

前回は、基本的なダンプでバックアップする方法をまとめました。

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
これ以降は基本的にLinuxのコマンドで記載します。

なお、tab(T)オプションの省略形の場合は、=は不要です。
mysqldump -uroot -p -T /backup/ database_name table_name

そうすると、対象としたテーブルのDDL文とデータが、指定のディレクトリに出力されます。
なお、ディレクトリは先に生成しておく必要があり、ディレクトリがないと出力エラーとなります。
出力ファイル名は、DDLが[テーブル名].sql、データが[テーブル名].txtです。

私のおすすめとして、コマンドで行うデータベースのバックアップには、verbose(v)オプションをつけておきましょう。
どのテーブル出力中か確認できて、待つ時間のストレスが軽減されます。

DDLの編集

さて、このコマンドでやりたいことは完了してしまいました。
DDL文とデータのファイル別出力と、テーブルごとにファイルを分割してディレクトリに出力がされていると思います。

DDL文に関しては、データを出力しない単一テーブルのmysqldumpと同じように出力されます。
DDL文の出力だけでいい場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ -d database_name table_name

また、オプションを出力せず、CREATE TABLEのみ出力する場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ -d -compact -compatible=mysql323 database_name table_name

他のDLLの変更は前回の基本の章に詳しく載っていますので、省略してデータ出力の加工についてみていきたいと思います。


区切り文字と囲み文字

デフォルトでは、フィールドの区切り文字はタブ文字、囲み文字はなし、行の区切りはLFとなっています。
つまり、特に指定しない場合、タブ文字で区切った改行コードがLFのファイルが出力されます。

○フィールドの区切り文字

まずは、フィールドの区切り文字から、指定します。
フィールドの区切り文字は、fields-terminated-byオプションで指定します。
カンマ区切りにしたい場合は、以下の通りです。
mysqldump -uroot -p -T /backup/ --fields-terminated-by="," database_name table_name

「,」のところを、半角スペース区切りなら「 」、タブ区切りなら「\t」としてください。
区切り文字は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、BINARY、TEXT、または ENUM のような)文字列タイプのカラムの値を囲む」と記載がありますが、DATEやDATETIMEタイプのデータも囲まれていました。
よく使う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

「\r\n」のところを、MacでCRにしたい場合は「\r」、Linux等で明示したい場合は「\n」としてください。
最近はどの改行文字でも問題ないことが多いですが、テキストエディタがなくWindowsのメモ帳で開かないといけない場合などに便利です。

○固定長出力

フィールドの区切り文字と囲み文字に空文字(“”)を指定すると、固定長での出力ができます。
mysqldump -uroot -p -T /backup/ --fields-terminated-by="" --fields-enclosed-by="" database_name table_name

注意していただきたいのは、INTやNUMBER、VARCHAR、TEXTが最大長で出力されること、また数値のデータが左寄せで出力されることです。
固定長とはいえ、フィールドの桁数はテーブル定義に依存しますので、それ専用のテーブルが必要になるでしょう。

○データのエスケープ

今まで出力してきた中で、区切り文字や囲み文字が\でエスケープされていましたが、それ以外の文字でもエスケープできます。
エスケープ文字はfields-escaped-byオプションで指定できます。
エスケープ文字に空文字(“”)を指定すると、NULL値がデフォルトでは「\N」と出力されていましたが、「NULL」という文字で出力されるようになります。
mysqldump -uroot -p -T /backup/ --fields-escaped-by="" database_name table_name

またバイナリデータがある場合は、hex-blobオプションが使えます。
mysqldump -uroot -p -T /backup/ --hex-blob database_name table_name
こちらを指定すると、バイナリデータを持つカラムのデータが16進数に変換されてダンプされます。

最後に

最後に、オプションを組み合わせて、カンマ区切り(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
フィールドの区切り文字をカンマとして、文字列のデータをダブルクォーテーションでくくり、改行コードはWindowsに合わせてCRLFとしました。

いかがでしたでしょうか?
一工夫加えれば、便利なダンプが取得できます。
ぜひぜひ、いろんなダンプの仕方を試してみてください!

株式会社ライズウィル

株式会社ライズウィル

〒110-0016
東京都台東区台東1-27-11 やわらぎビル2F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : http://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
面接予約・スタッフ管理システム「WebWORKS.」
WebWORKS.