I don't understand the responses that others have provided to similar questions except for the most obvious ones, such as the one below:

我不理解其他人对类似问题的回答，除了最明显的问题，比如下面的一个：

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';

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

By this I mean the exact code was provided. I'd greatly appreciate if someone could walk me through, step by step, what I need to do to enable local data on the "client" side and "server" side. It seems like I've enabled local data on the client side, but I don't know what instructions I need to give my computer to enable the "server side". I'm not tech savvy at all, and I just want to be able to get to the point where the data has been uploaded into MySQL workbench.

我的意思是提供了确切的代码。如果有人能一步一步地指导我，我将非常感激，我需要做些什么才能在“客户端”和“服务器”端启用本地数据。看起来我已经在客户端启用了本地数据，但我不知道我需要给我的计算机什么指令才能启用“服务器端”。我根本不懂技术，我只想能够到达数据已经上传到MySQL工作台的那一点。

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

CREATE TABLE toys (
uniq_id VARCHAR(1000),
product_name VARCHAR(1000),
manufacturer VARCHAR(1000),
price VARCHAR(1000),
number_available_in_stock VARCHAR (1000),
number_of_reviews INT,
number_of_answered_questions INT,
average_review_rating VARCHAR(1000),
amazon_category_and_sub_category VARCHAR(1000),
customers_who_bought_this_item_also_bought VARCHAR(1000),
description VARCHAR(1000),
product_information VARCHAR(1000),
product_description VARCHAR(1000),
items_customers_buy_after_viewing_this_item VARCHAR(1000),
customer_questions_and_answers VARCHAR(1000),
customer_reviews VARCHAR(1000),
sellers VARCHAR(1000)
);

LOAD DATA LOCAL INFILE ‘/Users/BruddaDave/Desktop/amazonsample.csv’ INTO TABLE toys
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES
(uniq_id, product_name, manufacturer, price, number_available_in_stock, number_of_reviews, number_of_answered_questions, average_review_rating, amazon_category_and_sub_category, customers_who_bought_this_item_also_bought, description, product_information, product_description, items_customers_buy_after_viewing_this_item, customer_questions_and_answers, customer_reviews, sellers)
;

I just want to be able to import a .csv file into MySQL using the command line shell.

我只希望能够使用命令行外壳将.csv文件导入到MySQL中。

优秀答案推荐

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

如果在服务器或客户端禁用本地功能，则尝试发出LOAD DATA LOCAL语句的客户端将收到以下错误消息：

ERROR 3950 (42000): Loading local data is disabled; this must be
enabled on both the client and server side

I met the same issue when I want to load the text file pet.txt into the pet table following a tutorial of Mysql:https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

在学习了Mysql:https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html教程之后，当我想要将文本文件pet.txt加载到宠物表中时，我遇到了同样的问题

After searching online, I fixed it by these steps:

在网上搜索后，我通过以下步骤进行了修复：

1. set the global variables by using this command:

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

2. quit current server:

mysql> quit
Bye

3. connect to the server with local-infile system variable :

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

This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side.
To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime.

此变量控制LOAD DATA语句的服务器端本地功能。根据LOCAL_INFILE设置，服务器拒绝或允许在客户端启用了LOCAL的客户端加载本地数据。要显式地导致服务器拒绝或允许LOAD DATA LOCAL语句(无论客户端程序和库在构建时或运行时如何配置)，请分别在禁用或启用LOCAL_INFILE的情况下启动mySQLD。也可以在运行时设置LOCAL_INFILE。

4. use your Database and load the file into the table:

mysql> use menagerie
Database changed
mysql> load data local infile '/path/pet.txt' into table pet;
Query OK, 8 rows affected, 7 warnings (0.00 sec)

Does it work?

它起作用了吗？

References:

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html
https://dev.mysql.com/doc/refman/8.0/en/source-configuration-options.html#option_cmake_enabled_local_infile
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

Https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html https://dev.mysql.com/doc/refman/8.0/en/source-configuration-options.html#option_cmake_enabled_local_infile https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

You may check the local_infile is disabled or enable. So, you try this-

您可以检查LOCAL_INFILE是否已禁用或启用。所以，你试试这个-

mysql> show global variables like 'local_infile';

if it shows-

如果它显示-

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  |  OFF  |
+---------------+-------+
(this means local_infile is disable)

then enable with this-

然后使用此选项启用-

mysql> set global local_infile=true;

Then check again and quit from mysql server with this-

然后再次检查并退出MySQL服务器，并显示以下内容-

mysql> exit

Now you have to connect/login server with local_infile. For this run this code from terminal command line-

现在您必须使用LOCAL_INFILE连接/登录服务器。为此，从终端命令行运行以下代码-

mysql --local_infile=1 -u root -ppassword DB_name

now load the data from local file-

现在从本地文件加载数据-

mysql> load data local infile 'path/file_name.extention' into table table_name;

It's work on my pc. you may try this. thanks.

这是在我的电脑上工作。你可以试试这个。谢谢。

This is what I had to do fix this issue on Ubuntu 20.04 / MySQL 8:

这是我不得不在Ubuntu 20.04/MySQL 8上解决这个问题的方法：

1. nano /etc/mysql/mysql.conf.d/mysqld.cnf


2. in the [mysqld] section add this line:

local_infile    = 1

3. add to the bottom of the file these two lines:

[client]

local_infile    = 1

4. run this command from my client: SET GLOBAL local_infile=1;

NOTE: revised the above to include extra step to make sure local_infile=1 is added to the [mysqld] section. This saves me having to run SET GLOBAL local_infile=1; after each reboot.

注意：修改了上面的内容，增加了额外的步骤，以确保将local_infile=1添加到[mySQLD]部分。这使我不必在每次重新引导后运行set global local_infile=1；。

Type the following command on the terminal -

在终端上键入以下命令-

SHOW GLOBAL VARIABLES LIKE 'local_infile';

If it's 0 oe false then type the following command -

如果为0 OE FALSE，则键入以下命令-

SET GLOBAL local_infile = TRUE;

my.cnf file:

My.cnf文件：

[client]  
local_infile=1

From the official MySQL 8.0 documentation.

来自官方MySQL8.0文档。

For default installation of MySQL 8.0.20:

对于MySQL 8.0.20的默认安装：

i) Look for the initialization file located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.

I)查找位于C：\ProgramData\MySQL\MySQL Server 8.0\my.ini的初始化文件。

ii) Open my.ini file with notepad.

Ii)用记事本打开my.ini文件。

iii) Look for the headers [client], [mysql] under the CLIENT section & [mysqld] under the SERVER section.

Iii)在客户端节下查找标题[CLIENT]、[MySQL]和服务器节下的[mySQLD]。

iv) Add the following statement under each header:

四)在每个标题下添加以下声明：

local_infile=ON

Local_Infile=打开

v) Save the file and restart MySQL80 service under the Windows local services.

V)保存文件，重启Windows本地服务下的MySQL80服务。

It should work.

应该能行得通。

as the question said this must be enable in client and server both, so in my case i was able to enabled that in server side

正如问题所说，这必须在客户端和服务器端都启用，所以在我的例子中，我能够在服务器端启用它

SET GLOBAL local_infile = true;

using above(it was not permanent when restarting server again it was OFF state) so i add below in my.cnf

使用上面的(重新启动服务器时它不是永久性的，它处于关闭状态)，所以我在my.cnf中添加了下面的内容

[mysqld]
local-infile 

this works and server side is okay with that, so in the client side(mine was a spring application) i have to add

这样可以工作，服务器端也可以，所以在客户端(我的是一个Spring应用程序)，我必须添加

allowLoadLocalInfile=true

this at the end of connection string. after adding this worked fine
below is my connection string (you can see that in the end of string)

这位于连接字符串的末尾。添加后，下面是我的连接字符串(您可以在字符串的末尾看到)

jdbc.url=jdbc:mysql://127.0.0.1:<port>/<dbname>?autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true

how global variables like 'local_infile';

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

set global local_infile=true;


exit

if you don't have any password for your mysql

$->>   sudo mysql --local_infile=1 -u root -p

if you have password for your mysql

$->>   sudo mysql --local-infile=1 -u root

now done your work

LOAD DATA LOCAL INFILE  
'/home/soyanswartz/Documents/Study/My_Course/Learn_SQL/05_Day/machine-learning-with-sql/data/weather_data.csv'
INTO TABLE TBL_WEATHER  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I just had this problem with PyMySQL and SQLAlchemy. Here are some things that got this to work...

我刚刚在使用PyMySQL和SQLAlChemy时遇到了这个问题。以下是让这件事起作用的一些事情。

1. Here is how I connect to the DB Engine...

   
   

    connect_args = ssl_args
    connect_args['local_infile'] = True
    qry_engine = create_engine('mysql+pymysql://' + db_uid + ':' + db_pw + '@' + db_addr, connect_args=connect_args,
                               pool_recycle=3600, echo=False)
   

   
   

Note that ssl_args is a dictionary that contains my ssl parameters (you can leave this out if not connecting with SSL but connect_args is expecting a dictionary.)

请注意，ssl_args是一个包含我的SSL参数的字典(如果没有使用SSL进行连接，但CONNECT_args需要一个字典，则可以省略这一点)。

2. When loading data, I have to run the following...

   
   

    sql_stmt = 'SET GLOBAL local_infile = TRUE'
    common.qry_engine.execute(text(sql_stmt))
    sql_stmt = "LOAD DATA LOCAL INFILE '" + filepath + "' INTO TABLE " + self.tst_tbl.tbl.fullname
    common.qry_engine.execute(text(sql_stmt))
   

   
   

When I try this without that SET GLOBAL command I get that "must be enabled on both the client and server side" message.

当我尝试在不使用set global命令的情况下执行此操作时，我收到“必须在客户端和服务器端都启用”消息。

After trying all the steps outlined above, I was still getting Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. The solution was to eliminate the word "LOCAL" in the SQL statement:

在尝试上述所有步骤后，我仍然收到错误代码：2068。由于访问限制，加载数据本地INFILE文件请求被拒绝。解决方案是删除SQL语句中的“local”一词：

load data infile...

加载数据...

For those who are stuck at step 3:

对于那些被困在第三步的人：

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

like I was, this is what I did:

就像我一样，我是这样做的：

1. type

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

in the command prompt.

在命令提示符下。

2. type

mysql --local_infile=1 -u root -pABC menagerie

where ABC is your password for MySQL and menagerie is the database you're working on
(replace ABC & menagerie with whatever is your password & database name respectively)

其中abc是您的MySQL密码，menagerie是您正在处理的数据库(分别用您的密码和数据库名称替换abc和menagerie)

3. MySQL will open in command prompt. You can type your commands like:

mysql> use menagerie

Database changed

mysql> load data local infile '/path/pet.txt' into table pet;

Query OK, 8 rows affected, 7 warnings (0.00 sec)

and it should work...

它应该能行得通。

on Ubuntu 20.04 / MySQL 8:
This approach resolved the issue for me:

在Ubuntu 20.04/MySQL 8上：这种方法为我解决了问题：

1. systemctl stop mysql


2. nano /etc/mysql/mysql.conf.d/mysqld.cnf


3. unter [mysqld] add local_infile =1


4. systemctl start mysql or restart mysql if you did stop it.

Executing this global setting change in SQL script should help

在SQL脚本中执行此全局设置更改应该会有所帮助

SET GLOBAL local_infile=1;

The solution is simple:
use the code:
(LOAD DATA INFILE 'data.csv'
INTO TABLE TABLE_THAT_WAS_CREATED)

解决方案很简单：使用代码：（LOAD DATA INFILE 'data.csv' INTO TABLE_THAT_WAS_CREATED）

istead of load local data, don't use "LOCAL"

不使用“local”而不是加载本地数据

and save the data.csv in C:\ProgramData\MySQL\MySQL Server 8.0\Data\TABLE_THAT_WAS_CREATED

并将data.csv保存在C：\ProgramData\MySQL\MySQL Server 8.0\Data\TABLE_THAT_WAS_CREATED中

Just set this below command in MySQL console

只需在MySQL控制台中设置下面的命令即可

SET GLOBAL local_infile=1;

Then exit the console.

然后退出控制台。

exit;

Now start the MySQL console with this below command

现在使用以下命令启动MySQL控制台

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

It worked or me. I hope It will work for you too.

不是我就是成功了。我希望它也能为你工作。