Search code examples
mysqlsqlmy.cnf

MySQL - Change secure_file_priv from NULL to either path or empty

My OS: Mac Big Sur 11.4

MySQL Workbench Version 8.0.28

As I need to import bulk CSV files to MySQL, my goal here is to NOT receive NULL when run:

SHOW VARIABLES LIKE 'secure_file_priv';

I have tried this guide as it seems recent by:

  • Creating my.cnf in text editor
  • Inputing in my.cnf:

[mysqld] secure_file_priv = ""

  • Saving my.cnf in either /etc/ or /usr/local/mysql-8.0.28-macos11-x86_64/support-files
  • Restarting MySQL

But I still received NULL in secure-file-priv

Any suggestions on how to fix this problem is appreciated.

Solution

  • After researching, I was able to create my.cnf with secure_file_priv + path , connect Configuration file to my.cnf and import cvs files using LOAD DATA INFILE.

    Here's how I did it:

    A. CREATING my.cnf:

    1. Create my.cnf file using text editor
    2. In my.cnf, input the below and save on folder of choice (ie. Desktop):

    [mysqld] secure_file_priv = "/usr/local/”

    *When save, untick for If not extension provided, use “.txt” so your "Kind" of file is Document.

    1. Move my.cnf file to /etc/

    B. IN MYSQL WORKBENCH:

    1. Tab ADMINISTRATION (top left)
    2. Click Manage Server Connections (next to INSTANCES, screenshot can be found here)
    3. Change Installation Type to: macOS (MySQL Package)
    4. Next to Configuration file > Choose /etc/my.cnf
    5. Test Connection to check. If succeed, Close.
    6. Click Options File under INSTANCES to check if still “...not specified” (it shouldnt be)
    7. Quit Workbench
    8. RESTART SERVER by System Preferences > My SQL > Stop Server and then Start Server again
    9. Open Workbench to check

    `

    SHOW VARIABLES LIKE 'secure_file_priv';

Expected result: 
Variable_Name: secure_file_priv
Value: /usr/local/

    `

    C. IMPORTING:

    1. Move my csv file to /usr/local/
    2. Use

    LOAD DATA INFILE '/usr/local/filename.csv' [...]