mysqldumpコマンドからCSVを作成する

こんにちは。
開発チームのワイルド担当、まんだいです。

MySQLのデータをエクセルで編集したい時はないですか？
今回は、そう言った時にMySQLのデータをCSVに変換して、エクセルで読める形式にする方法をまとめてみました。

mysqlの内容をファイルに書き落とす場合によく使うのが、mysqldumpコマンドですが、特に指定しない場合は、SQLの状態で出力されます。

別のDBへデータを移す際は、これで問題ないですが、このファイルをエクセルで加工したい場合はどうでしょう？

mysqldumpコマンドには、この要望にも応えるべく、--tabオプションが用意されています。

今回検証用に用意したMySQLのバージョンは、MySQL Community Server 5.6.35で、mysqlの公式リポジトリを経由してインストールしたものです。

 

mysqldumpコマンド単品で取得してみる

mysqldumpコマンドでcsvを取得する場合、よくお目にかかるエラーがあります。
それが次のようなもの。

mysqldump -uroot -p --tab=/tmp hogehoge fugafuga
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

rootで書き出ししているのに、なぜかエラー。
これは何かというと、MySQLサーバーの起動オプションに「--secure-file-priv」が付いているので、指定のディレクトリ以外にファイルの書き出しはできないよというもの。

これの確認方法を見ていきます。
まず、MySQLにログインします。

mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

 
公式リポジトリのMySQLサーバーの場合、既定値で、/var/lib/mysql-files/ というディレクトリにのみ、ファイルの書き出しができるようになっています。

いやいや、そんな馬鹿な。
今までもSQLの書き出しなら出来ていたし。

私もこれは思いましたが、それって、mysqldumpのコマンド結果をファイルにリダイレクトしているだけですよね。
あくまでも、mysqldumpはファイルに書いてはいないと言うわけです。

--tabオプションは、「=」の後に指定したパスにmysqldump自身が書き込むので、「@@global.secure_file_priv」で指定されているパス以外への書き出しはエラーになる訳です。

ということで、この環境では、--tabオプションを使った、mysqldumpコマンドのファイル出力は、以下のようになります。

mysqldump -uroot -p --tab=/var/lib/mysql-files/ hogehoge fugafuga

 
これでタブ区切りのCSV（TSV）が /var/lib/mysql-files/ に出力できました。

 

@@global.secure_file_privのパスにアクセスできない、@@global.secure_file_privを変更できない

こういう時もありましょう。

その場合は、どうにもならないので、以下のような手順が思いつきます。
1. ひとまずmysqldumpでSQLを取得
2. 出力したSQLをコントロール可能なMySQLサーバーにインポート
3. CSVを出力

時間がある場合は、これでも良いでしょう。
時間がない、手間が惜しい。
そんな場合は、grepとsedを使って、SQLをCSVに変換する、という手もあります。

# tsv
mysqldump -uroot -p -t [対象スキーマ] [対象テーブル] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/);$//' -e 's/^(//' -e 's/),(/)\n(/g' -e 's/)\n/\n/g'  -e 's/\n(/\n/g' -e 's/,/\t/g' -e "s/'//g" > [出力先ファイル名]

 

# CSV （カンマ区切り、ダブルクォーテーション囲み）
mysqldump -uroot -p -t [対象スキーマ] [対象テーブル] | grep 'INSERT INTO' | sed -e 's/INSERT INTO `.*` VALUES //' -e 's/^(/"/' -e 's/);$/"/' -e 's/),(/)\n(/g' -e "s/'//g" -e 's/)\n/"\n/g' -e 's/\n(/\n"/g' -e 's/,/","/g' > [出力先ファイル名]

 
上記のコマンドは、GNU sed 4.2.1（CentOS 6.8）で動作検証しました。
sedの置換する順番が重要だったりする訳ですが、データの内容によっては、上手く変換できないケースもあるかも知れません（特に文字列で「),(」を持っている場合）。
ワンライナーで加工できるので、一度お試しあれ。

 
以上です。