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 post¹, 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):

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

First, we need to create a table to load the csv:

CREATE TABLE my_table (
  col1 VARCHAR(255),
  col2 INT
);

The SQL query I use to load the data is:

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

References#

• Connect with local-infile Option in MySQL Workbench: https://stackoverflow.com/a/59408954/6064933
• mysql 3948 error: https://stackoverflow.com/a/60717467/6064933
• mysql LOAD DATA manual: https://dev.mysql.com/doc/refman/8.0/en/load-data.html