I am using MySQL server version 8.0.30 on my macOS. I try to read a local csv file into a mysql table using LOAD DATA LOCAL INFILE following some post1, but unfortunately I get the following error:

Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides

Following the guide of some posts on the Internet, I tried to change the variable local_infile to 1:

SET GLOBAL local_infile=1;

Then I got the following error when loading csv file:

Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

The right way to load local csv file

Here I will summarize what is working for me on macOS.

change the variable local_infile

First we need to run the following command when we are still connecting to the server:

SET GLOBAL local_infile=1;

Then use SHOW GLOBAL VARIABLES LIKE '%local_infile%'; to check if the change has taken effect. You should see the following output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.01 sec)

Restart the server and establish new client connection

Then stop the mysql server and restart the server. If you are using macOS and install mysql via HomeBrew, this is easy to do

brew services stop mysql
brew services start mysql

Then stop the client connection.

Reconnect client to server

If you are using mysql command line client to connect to the server, run this:

mysql --local-infile=1 -u root -p

If you are using other SQL client to connect to the mysql server, the settings may vary. For MySQL Workbench, go to Database --> Manage Connections, select the connection, under Connection --> Advanced, there is a Others text box, add the following conf (in new line):

OPT_LOCAL_INFILE=1

Then click the button Reconnect to DBMS in the toolbar. You should be able to load csv files into a table.

Load the csv file

Finally, we can load the local csv file. The csv file looks like this:

field1,field2
foobar,1
hello,2
great,3

The SQL query I use to load the data is:

LOAD DATA LOCAL INFILE '/path/to/my_csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','  -- field separator
LINES TERMINATED BY '\n'  -- line ending
IGNORE 1 ROWS;  -- ignore the header line

References

Author jdhao

LastMod

2022-10-15

License CC BY-NC-ND 4.0

Reward