[mysql] How should I tackle --secure-file-priv in MySQL?

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

At macOS Catalina, I followed this steps to set secure_file_priv

1.Stop MySQL service

 sudo /usr/local/mysql/support-files/mysql.server stop

2.Restart MYSQL assigning --secure_file_priv system variables

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=YOUR_FILE_DIRECTORY

Note: Adding empty value fix the issue for me, and MYSQL will export data to directory /usr/local/mysql/data/YOUR_DB_TABLE/EXPORT_FILE

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=

Thanks

MySQL use this system variable to control where you can import you files

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

So problem is how to change system variables such as secure_file_priv.

1. shutdown mysqld
2. sudo mysqld_safe --secure_file_priv=""

now you may see like this:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+

I created a NodeJS import script if you are running nodeJS and you data is in the following form (double quote + comma and \n new line)

INSERT INTO <your_table> VALUEs( **CSV LINE **)

This one is configured to run on http://localhost:5000/import.

I goes line by line and creates query string

"city","city_ascii","lat","lng","country","iso2","iso3","id"
"Tokyo","Tokyo","35.6850","139.7514","Japan","JP","JPN","1392685764",
...

server.js

const express = require('express'),
   cors = require('cors'),
   bodyParser = require('body-parser'),
   cookieParser = require('cookie-parser'),
   session = require('express-session'),
   app = express(),
   port = process.env.PORT || 5000,
   pj = require('./config/config.json'),
   path = require('path');

app.use(bodyParser.json());
app.use(cookieParser());
app.use(cors());


app.use(
   bodyParser.urlencoded({
      extended: false,
   })
);

var Import = require('./routes/ImportRoutes.js');

app.use('/import', Import);
if (process.env.NODE_ENV === 'production') {
   // set static folder
   app.use(express.static('client/build'));

   app.get('*', (req, res) => {
      res.sendFile(path.resolve(__dirname, 'client', 'build', 'index.html'));
   });
}

app.listen(port, function () {
   console.log('Server is running on port: ' + port);
});

ImportRoutes.js

const express = require('express'),
   cors = require('cors'),
   fs = require('fs-extra'),
   byline = require('byline'),
   db = require('../database/db'),
   importcsv = express.Router();

importcsv.use(cors());

importcsv.get('/csv', (req, res) => {

   function processFile() {
      return new Promise((resolve) => {
         let first = true;
         var sql, sqls;
         var stream = byline(
            fs.createReadStream('../PATH/TO/YOUR!!!csv', {
               encoding: 'utf8',
            })
         );

         stream
            .on('data', function (line, err) {
               if (line !== undefined) {
                  sql = 'INSERT INTO <your_table> VALUES (' + line.toString() + ');';
                  if (first) console.log(sql);
                  first = false;
                  db.sequelize.query(sql);
               }
            })
            .on('finish', () => {
               resolve(sqls);
            });
      });
   }

   async function startStream() {
      console.log('started stream');
      const sqls = await processFile();
      res.end();
      console.log('ALL DONE');
   }

   startStream();
});

module.exports = importcsv;

db.js is the config file

const Sequelize = require('sequelize');
const db = {};
const sequelize = new Sequelize(
   config.global.db,
   config.global.user,
   config.global.password,
   {
      host: config.global.host,
      dialect: 'mysql',
      logging: console.log,
      freezeTableName: true,

      pool: {
         max: 5,
         min: 0,
         acquire: 30000,
         idle: 10000,
      },
   }
);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Disclaimer: This is not a perfect solution - I am only posting it for devs who are under a timeline and have lots of data to import and are encountering this ridiculous issue. I lost a lot of time on this and I hope to spare another dev the same lost time.

This worked for me (had the additional problem of not being able to use LOCAL with my current MySQL version in the statement LOAD DATE INFILE ... )

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv='' --local-infile

The above works for that given path on my machine; you may have to adjust your path.

Then use:

mysql -u root -p

One important point is that you should have the CSV in the MySQL data folder. In my machine it is located at: /usr/local/mysql-8.0.18-macos10.14-x86_64/data

You can change the folder permission if needed to drop a CSV in the data folder.

Setup:
macOS Catalina version 10.15.5
MySQL version 8.0.18

I had all sorts of problems with this. I was changing my.cnf and all sorts of crazy things that other versions of this problem tried to show.

What worked for me:

The error I was getting

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

I was able to fix it by opening /usr/local/mysql/support-files/mysql.server and changing the following line:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" -- $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

to

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="" $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

If the file is local to your machine use the LOCAL in your command

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

On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. To disable it you need to add secure-file-priv = "" to your my.cnf file under the mysqld config group. eg:-

[mysqld]
secure-file-priv = ""

For MacOS Mojave running MySQL 5.6.23 I had this problem with writing files, but not loading them. (Not seen with previous versions of Mac OS). As most of the answers to this question have been for other systems, I thought I would post the my.cnf file that cured this (and a socket problems too) in case it is of help to other Mac users. This is /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
secure-file-priv = ""
skip-external-locking

(The internationalization is irrelevant to the question.)

Nothing else required. Just turn the MySQL server off and then on again in Preferences (we are talking Mac) for this to take.

I'm working on MySQL5.7.11 on Debian, the command that worked for me to see the directory is:

mysql> SELECT @@global.secure_file_priv;

For mysql 8.0 version you can do this:

mysql.server stop
mysql.server start --secure-file-priv=''

It worked for me on Mac High Sierra.

Without changing any of the configuration files..

1. look for the value of secure_file_priv using the command posted by @vhu: SHOW VARIABLES LIKE "secure_file_priv".
2. define the full path for your query such as: select * from table into outfile 'secure_file_priv_PATH/OUTPUT-FILE' ... rest of your query

this worked for my in mysql-shell on ubuntu 18.04 LTS mysql 5.7.29

This thread has been viewed 570k times at the time of this post. Honestly when did MySQL become our over protective unreasonable mom? What a time consuming attempt at security - which really only serves to shackle us!

After many searches and many attempts everything failed. My solution:

What worked for me was:

1. Import the .csv file via PhpMyAdmin import on older box (if large do at cmd line)
2. Generate a .sql file.
3. Download .sql file.
4. Import .sql file via MySQL Workbench.

The thing that worked for me:

1. Put your file inside of the folder specified in secure-file-priv.

   To find that type:

   mysql> show variables like "secure_file_priv";  
   

2. Check if you have local_infile = 1.

   Do that typing:

   mysql> show variables like "local_infile";
   

   If you get:

   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | local_infile  | OFF   |
   +---------------+-------+
   

   Then set it to one typing:

   mysql> set global local_infile = 1;
   

3. Specify the full path for your file. In my case:

   mysql> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.txt" into table test;
   

If you're running on Ubuntu, you may also need to configure Apparmor to allow MySQL to write to your folder, e.g. here's my configuration:

Add this line to file /etc/apparmor.d/usr.sbin.mysqld :

/var/lib/mysql-files/* rw

Then add these 2 config lines to /etc/mysql/my.cnf sections:

[client]
loose-local-infile = 1

[mysqld]
secure-file-priv = ""

Here's my SQL:

select id from blahs into outfile '/var/lib/mysql-files/blahs';

It worked for me. Good luck!

@vhu I did the SHOW VARIABLES LIKE "secure_file_priv"; and it returned C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ so when I plugged that in, it still didn't work.

When I went to the my.ini file directly I discovered that the path is formatted a bit differently:

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads

Then when I ran it with that, it worked. The only difference was the direction of the slashes.

I had this problem on windows 10. "--secure-file-priv in MySQL" To solve this I did the following.

1. In windows search (bottom left) I typed "powershell".
2. Right clicked on powershell and ran as admin.
3. Navigated to the server bin file. (C:\Program Files\MySQL\MySQL Server 5.6\bin);
4. Typed ./mysqld
5. Hit "enter"

The server started up as expected.

Here is what worked for me in Windows 7 to disable secure-file-priv (Option #2 from vhu's answer):

1. Stop the MySQL server service by going into services.msc.
2. Go to C:\ProgramData\MySQL\MySQL Server 5.6 (ProgramData was a hidden folder in my case).
3. Open the my.ini file in Notepad.
4. Search for 'secure-file-priv'.
5. Comment the line out by adding '#' at the start of the line. For MySQL Server 5.7.16 and above, commenting won't work. You have to set it to an empty string like this one - secure-file-priv=""
6. Save the file.
7. Start the MySQL server service by going into services.msc.

in Linux you have to edit my.cnf file in

/etc/mysql/my.cnf

and change 26 line number param like this :

secure-file-priv= <your data path directory like /home/user/data>

then restart your MySQL and try again.

in docker you have to mount your my.cnf file with my.cnf file in your container with this command in docker-compose or add manually :

volumes:
  - ./persistent:/var/lib/mysql
  - ./conf/my.cnf:/etc/mysql/my.cnf

next change /conf/my.cnf in your host and config secure-file-priv param like the upper approach, in addition, you have to mount your data in mysql container and set that path for secure-file-priv param and restart your services and finally, you can load your data.

you can check your config with this command :

SHOW VARIABLES LIKE "secure_file_priv";

I added the LOCAL to the command, but it made another problem:

Loading local data is disabled - this must be enabled on both the client and server sides

For solving this I simply followed three steps in here

I solved it using the LOCAL option in the command:

LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;

You can find more info here.

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

I had the same problem with 'secure-file-priv'. Commenting in the .ini file didn't work and neither did moving file in directory specified by 'secure-file-priv'.

Finally, as dbc suggested, making 'secure-file-priv' equal to an empty string worked. So if anyone is stuck after trying answers above, hopefully doing this will help.