#はじめに
本記事は、MySQL8の環境でCSVファイルのインポート・エクスポート処理を行った際に、うまくいかずにはまってしまったので、その内容を備忘録として書いたものである。
#実行環境
- OS情報
# cat /etc/centos-release
CentOS Linux release 8.1.1911 (Core)
- MySQLバージョン
# mysql -V
mysql Ver 8.0.17 for Linux on x86_64 (Source distribution)
#目次
(1)CSVファイルのインポート
(2)CSVファイルのエクスポート
#(1)CSVファイルのインポート
MySQL8では、CSVファイルのインポートに関して変更があったようで、デフォルトの設定のままではインポートに失敗します。
MySQL8でCSVファイルのインポートをおこなう際は、以下の条件を満たしている必要がある。
- 「--local-infile」 で明示的に1を指定(デフォルト値は0で設定されている)
- mysqld, mysql コマンドラインクライアント毎に 「--local-infile」 オプションを持っているので、両方とも1を指定。
上記の文言は、この記事から参照したものである。
実際にデフォルトの設定で、CSVファイルのインポートを実施すると以下のようなエラーが発生する。
mysql> LOAD DATA LOCAL INFILE '/usr/data/test.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' ESCAPED BY '"';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
以下の手順で、CSVファイルのインポートができるようになります。
###①クライアント側の「--local_infile」を設定
接続時に「--local_infile=1」を指定し、ログインする。
# mysql -u root -p --local_infile=1
※接続時に「--local_infile=1」を指定して接続すると、クライアント側のほうで設定ができるらしいが、確認するコマンドはないらしい。
###②サーバー側の「--local_infile」を設定
#mysql側の「local_infile」の現在の設定値(デフォルト値)を確認
mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
#mysqlで、「local_infile=1」を設定
mysql> SET PERSIST local_infile= 1;
Query OK, 0 rows affected (0.00 sec)
#設定後の確認
mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
###CSVデータのインポート
mysql> LOAD DATA LOCAL INFILE '/usr/data/test.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' ESCAPED BY '"';
Query OK, 50 rows affected, 450 warnings (0.01 sec)
Records: 50 Deleted: 0 Skipped: 0 Warnings: 450
①と②の設定をおこなうことでCSVファイルのインポートができるようになりました！！
#(2)CSVファイルのエクスポート
次は、CSVファイルのエクスポートについて書いていきます。
MySQLのデータをCSVファイルとしてエクスポートする際は、ファイルの作成権限があるディレクトリでしか実行ができないようです。
その対象ディレクトリの場所の確認及び、所定ディレクトリの変更をおこなう際の備忘録を以下に記述します。
###(2.1)デフォルトの設定内容で、CSVデータをエクスポート
▼ファイルの作成権限があるディレクトリを確認
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/ |
+---------------------------+
1 row in set (0.00 sec)
※デフォルトでは、「/var/lib/mysql-files/ 」ディレクトリがその対象ディレクトリにあたる。
▼テーブルデータをCSVファイルとしてエクスポート
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 50 rows affected (0.00 sec)
対象ディレクトリで、CSVファイルのエクスポートを実行すると問題なく実行ができます。
###(2.2)ファイルの作成権限があるディレクトリを変更して、CSVデータをエクスポート
▼「/etc/my.cnf」で、対象ディレクトリを指定
[mysqld]
secure-file-priv = "/var/lib/mysql-files2"
※「/var/lib/mysql-files2」というディレクトリにエクスポートする想定で記述します。
▼設定反映のため、mysqlをリスタート
# systemctl restart mysqld
▼CSVファイルのエクスポート
・出力先を「/var/lib/mysql-files/test.csv」で指定
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
・出力先を「/var/lib/mysql-files2/test.csv」で指定
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files2/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 50 rows affected (0.00 sec)
※現時点の設定では、「/var/lib/mysql-files2」ディレクトリへのエクスポートのみが許可されているので、それ以外のディレクトリへのエクスポートは失敗する。
###(3.3)ファイルの作成権限を全ディレクトリで許可
▼「/etc/my.cnf」で、全ディレクトリでファイルの作成権限の許可をする
[mysqld]
secure-file-priv = ""
▼設定反映のため、mysqlをリスタート
# systemctl restart mysqld
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 50 rows affected (0.01 sec)
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files2/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 50 rows affected (0.00 sec)
mysql> SELECT * FROM test_table INTO OUTFILE '/var/lib/mysql-files3/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ;
Query OK, 50 rows affected (0.00 sec)
上記のように、全部のディレクトリでCSVファイルのエクスポートができるようになりました。
この設定は、ローカル検証するとかならいいかもだけど本番サーバとかでは考えたほうがいいかも...
#最後に
普段、mysqlはそんなに使わないから久しぶりに触って、だいぶはまりました。
自分が今後困らないように書かせてもらった記事なので、他サイトと類似している点が多いとは思いますがご了承ください。
#参考URL一覧