多くの場合,MySQLはファイル出力(つまりファイルの新規作成&ファイルへの書き込み)を許可していません.
理由はファイル出力を許可するとセキュリティ上の問題が出てくるからです.MySQLを使う場合は,先ず mysqld に接続して,mysqld 上でクエリを実行することになります.つまりファイルを出力するのは mysqld であり,生成されたファイルの所有者は mysqld になってしまいます.ファイルを作成してもそれを削除できるのは mysqld ユーザだけ,これはセキュリティ上何かと問題を起こします.
この欠点を無視して無理やりファイル出力を許可するためには,次の二つの条件を満たす必要があります.
以下,具体的方法をまとめます.
条件1) ファイル出力の許可
以下のSQLを実行して
SELECT @@global.secure_file_priv;
NULL と表示される場合は,ファイル出力が許可されていません.
許可には,mysqlのサーバ設定の変更が必要です.
設定ファイルは /etc/mysql/my.cnf です.ただし debian の場合は /etc/mysql/conf.d/以下のファイルもincludeするようになっているので
/etc/mysql/conf.d/mysqld.cnf などの設定ファイルを新しく作った方が無難です.
設定ファイルに以下の設定を追加します
[mysqld] secure-file-priv="/tmp"
これで "/tmp/" ディレクトリにファイルが出力できるようになります.
"/tmp"は誰でもアクセス可能なディレクトリなのでセキュリティ上リスクがあります.しかし今回は動作確認ということで /tmp を使います.
mysql を再起動して設定を反映させます.
以下のSQLを実行して確認しましょう
SELECT @@global.secure_file_priv;
"/tmp”と表示されれば成功です.NULLの場合はsecure-file-privの設定が有効になっていません.
条件2) FILE権限の付与
ユーザ hogehoge@localhost にファイル出力の権限を付与します
権限を付与するので管理者権限で mysql に接続して,GRANT文を実行します.
$ sudo mysql -u root GRANT FILE ON *.* TO 'hogehoge'@'localhost';
MYSQLの仕組み上,データベース単位,テーブル単位での細かな権限設定は出来ません.
CSVファイルに保存してみる.
条件1) 条件2)が満たせれば以下のような形で "INTO OUTFILE ファイル名” でクエリの出力をファイルに保存できるようになります.
SELECT * FROM テーブル名 INTO OUTFILE '/tmp/filename.csv'
/tmp/filename.csv というファイルが出来上がります.ファイルのowner を確認してみましょう
$ ls -lha /tmp/filename.csv -rw-rw-rw- 1 mysql mysql 4.9K Mar 18 00:00 /tmp/filename.csv
所有者は mysql になっています.パーミッションも微妙です.
このようにファイル出力はセキュリティ上問題があるので注意しましょう.
CSVファイルの形式の調整
カンマ区切り,ダブルクオートで囲む場合
SELECT * FROM テーブル名 INTO OUTFILE '/tmp/filename.csv' fields terminated by ',' optionally enclosed by '"';
エラーが出る場合
ERROR 1290 The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
条件1) を見直しましょう
ERROR 1045 Access denied for user 'hogehoge'@'localhost' (using password: YES)
条件2)を見直しましょう