MySQLのSELECT結果をファイル出力する方法とよくあるエラー

MySQLを学び直し中。
  • macOS 10.13.6
  • MySQL 5.6.39
まずは基本 

SELECT * FROM [table] INTO OUTFILE [filepath]



# 例文
SELECT * FROM sample_db.sample_table INTO OUTFILE 'sample_data.tsv'

よくあるエラー

–secure-file-privエラー


ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
設定を確認してみます。 

SELECT @@secure_file_priv;
この結果がNULLだと上記エラーが出るようです。 対応としては、my.cnfに
secure-file-priv=""
の一行を追加してMySQLを再起動します。

my.cnfの場所がわからない


mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
読み込みの優先順に表示されるのでパスを確認しましょう。

生成されたファイルの場所がわからない


SELECT * FROM sample_table INTO OUTFILE 'sample_data.tsv'
のように、出力ファイルをファイル名のみにした場合、
/var/lib/mysql/[db_name]/ や /usr/local/var/mysql/[db_name]/ といった場所に生成されます。 環境によるので、フルパスで指定するのが無難かもしれません。 

# 例
SELECT * FROM sample_table INTO OUTFILE '/Users/xxx/yyy/zzz/sample_data.tsv'

Access deniedとなる


ERROR 1045 (28000): Access denied for user 'sample_user'@'localhost' (using password: YES)
ユーザーにFILE権限(ファイルの書き込み権限)が無いことが理由。(すでにGRANT ALL〜をしていても) 権限を追加します。 

GRANT FILE ON *.* TO sample_user@localhost IDENTIFIED BY 'password';
ちなみに、dbを指定してFILE権限を付与しようとしてもできません。 

GRANT FILE ON sample_db.* TO sample_user@localhost IDENTIFIED BY 'password';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGESと出ます。
FILEはグローバルな権限なため、DBを指定して設定することができないためです。

参考

開発MySQL