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

mysql database
Mohit Bhasi picture Mohit Bhasi · Sep 23, 2015 · Viewed 604k times · Source

I am learning MySQL and tried using a LOAD DATA clause. When I used it as below:

LOAD DATA INFILE "text.txt" INTO table mytable;

I got the following error:

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

How do I tackle this error?

I have checked another question on the same error message, but still can’t find a solution.

I am using MySQL 5.6

Answer

vhu picture vhu · Sep 23, 2015

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

You may 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.

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More