Disable secure priv for data loading on MySQL
I can't reproduce the problem.
mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 5.7.13 |+-----------+1 row in set (0,00 sec)mysql> SELECT @@GLOBAL.secure_file_priv;+---------------------------+| @@GLOBAL.secure_file_priv |+---------------------------+| NULL |+---------------------------+1 row in set (0,00 sec)-- USE ...mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv' -> INTO TABLE `test_files` -> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"' -> LINES TERMINATED BY '\n';ERROR 1290 (HY000): The MySQL server is running with the --secure-file-privoption so it cannot execute this statement
Change file: /etc/mysql/my.cnf
[mysqld]...secure_file_priv=/var/lib/mysql-files/...
Restart MySQL.
mysql> SELECT @@GLOBAL.secure_file_priv;+---------------------------+| @@GLOBAL.secure_file_priv |+---------------------------+| /var/lib/mysql-files/ |+---------------------------+1 row in set (0,00 sec)mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv' -> INTO TABLE `test_files` -> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"' -> LINES TERMINATED BY '\n';Query OK, 3 rows affected (0,00 sec)Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
This works in MacOs Sierra 10.12.6:
use the command
mysql --help | more
and look for a line where it is written:
Default options are read from the following files in the given order:/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
In my case I tried to create a my.cnf in the home directory but it did not work. The only solution I found is to create the file in the folder etc with
sudo vim /etc/my.cnf
and put inside it
[mysqld_safe][mysqld]secure-file-priv = ""
Then you can check that everything works with
select @@GLOBAL.secure_file_priv;
inside mysql and check that the value is empty and different from NULL.
Finally save files in the directory /tmp and then move them in the directory you want. Alternatively (but possibly unsafe) use
chmod 1777 dir
where dir is the name of the directory in which you are writing the files.
On MACOSX add .my.cnf to your home directory with content:
[mysqld_safe][mysqld]secure_file_priv=""