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

Question

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

User · Answer

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

User · Answer

Without changing any of the configuration files      look for the value of secure file priv using the command posted by  vhu  SHOW VARIABLES LIKE  secure file priv   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

User · Answer

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

User · Answer

I solved it using the LOCAL option in the command  LOAD DATA LOCAL INFILE  quot text txt quot  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

User · Answer

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  gt  dev null  amp    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  gt  dev null  amp    wait for pid created        mysqld pid file path   return value

User · Answer

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

User · Answer

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

User · Answer

MySQL use this system variable to control where you can import you files  mysql gt  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    shutdown mysqld sudo mysqld safe --secure file priv      now you may see like this   mysql gt  SHOW VARIABLES LIKE  secure file priv    ------------------ -------    Variable name      Value    ------------------ -------    secure file priv            ------------------ -------

User · Answer

Here is what worked for me in Windows 7 to disable secure-file-priv  Option  2 from vhu s answer     Stop the MySQL server service by going into services msc  Go to C  ProgramData MySQL MySQL Server 5 6  ProgramData was a hidden folder in my case   Open the my ini file in Notepad  Search for  secure-file-priv   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    Save the file  Start the MySQL server service by going into services msc

User · Answer

I m working on MySQL5 7 11 on Debian  the command that worked for me to see the directory is   mysql gt  SELECT   global secure file priv

User · Answer

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  lt your table gt  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    gt          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    gt        function processFile           return new Promise  resolve    gt             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  lt your table gt  VALUES      line toString                             if  first  console log sql                     first   false                    db sequelize query sql                                                on  finish        gt                   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

User · Answer

I had this problem on windows 10   --secure-file-priv in MySQL  To solve this I did the following     In windows search  bottom left  I typed  powershell    Right clicked on powershell and ran as admin    Navigated to the server bin file   C  Program Files MySQL MySQL Server 5 6 bin    Typed   mysqld Hit  enter    The server started up as expected

User · Answer

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

User · Answer

The thing that worked for me   Put your file inside of the folder specified in secure-file-priv  To find that type  mysql gt  show variables like  quot secure file priv quot       Check if you have local infile   1  Do that typing  mysql gt  show variables like  quot local infile quot    If you get   --------------- -------    Variable name   Value    --------------- -------    local infile    OFF      --------------- -------   Then set it to one typing  mysql gt  set global local infile   1    Specify the full path for your file  In my case  mysql gt  load data infile  quot C  ProgramData MySQL MySQL Server 8 0 Uploads file txt quot  into table test

User · Answer

If the file is local to your machine use the LOCAL in your command  LOAD DATA LOCAL INFILE  text txt  INTO table mytable

User · Answer

vhu I did the SHOW VARIABLES LIKE  quot secure file priv quot   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

User · Answer

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   lt your data path directory like  home user data gt   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  quot secure file priv quot

User · Answer

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    Move your file to the directory specified by secure-file-priv  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

User · Answer

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

User · Answer

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    quot  quot  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

User · Answer

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

User · Answer

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   Import the  csv file via PhpMyAdmin import on older box  if large do at cmd line  Generate a  sql file  Download  sql file  Import  sql file via MySQL Workbench

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

Examples related to mysql

Examples related to database