Disable Secure Priv for Data Loading on MySQL

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-priv
option 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

See 6.1.4 Server System Variables :: secure_file_priv

How should I resolve --secure-file-priv in MySQL?

It's working as intended. Your MySQL server has been started with --secure-file-priv option which limits from which directories you can load files using LOAD DATA INFILE.

Use SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured.

You have two options:

1. Move your file to the directory specified by secure-file-priv.
2. Disable secure-file-priv. This must be removed from startup and cannot be modified dynamically. To do this check your MySQL start up parameters (depending on platform) and my.ini.

How to disable secure_file_priv in MySQL 8.0 in windows 10 machine?

you can only change it in my.inio file and then restart he server

You find my.ini in a hidden folder

C:\ProgramData\MySQL\MySQL Server 8.0

There you find under the section

[mysqld]

secure-file-priv = ""

MySQL ERROR 1290 (HY000) --secure-file-priv option

Ubuntu 16.04 (EASY): Find out where you are allowed to write

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

Then, just write there

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Mac OSX: Mysql installed via MAMP

Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

NULL means you're screwed so you have to create the file "~/.my.cnf"

Enable read/write for MySQL installed via MAMP (on Mac):

1. open "MAMP" use spotlight
2. click "Stop Servers"

3. edit ~/.my.cnf (using vi or your favorite editor) and add the following lines:

   $ vi ~/.my.cnf

[mysqld_safe]
[mysqld]
secure_file_priv="/Users/russian_spy/"

4. click "Start Servers" (in MAMP window)

Now check if it works:

a. start mysql (default MAMP user is root, password is also root)

$ /Applications/MAMP/Library/bin/mysql -u root -p 

b. in mysql look at the white-listed paths

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /Users/russian_spy/          |
+---------------------------+
1 row in set (0.00 sec)

c. Finally, test by exporting a table train into a CSV file

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

---

Related Topics