blocks|key|346215|text|我在MySQL+8.0.11上用mysql终端命令解决了这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|346216|SET+GLOBAL+local_infile+=+true;|code-block|syntax|javascript|346217|我的意思是，我首先用通常的方式登录：|346218|mysql+-u+user+-p*|346219|在此之后，您可以使用以下命令查看状态：|346220|SHOW+GLOBAL+VARIABLES+LIKE+'local_infile';|346221|应该是开着的。我不会在这里写关于将本地文件加载到数据库中的安全性问题。|346222|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I solved this problem on MySQL 8.0.11 with the mysql terminal command:

<pre><code>SET GLOBAL local_infile = true;
</code></pre>

I mean I logged in first with the usual:

<pre><code>mysql -u user -p*
</code></pre>

After that you can see the status with the command:

<pre><code>SHOW GLOBAL VARIABLES LIKE 'local_infile';
</code></pre>

It should be ON. I will not be writing about security issued with loading local files into database here.

blocks|key|561820|text|将驱动程序php5-mysql替换为本地驱动程序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|561821|关于debian|561822|apt-get+install+php5-mysqlnd|code-block|syntax|javascript|561823|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Replace the driver php5-mysql by the native driver 

On debian 

<pre><code>apt-get install php5-mysqlnd
</code></pre>

blocks|key|345858|text|如果你的mysql+on+ubuntu在任何情况下都不能工作，你仍然会得到1148错误，你可以通过命令行运行load+data+infile命令|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|345859|打开终端窗口|345860|运行mysql+-u+YOURUSERNAME+-p+--local-infile+YOURDBNAME|345861|系统将要求您插入mysqluser密码|345862|您将运行MySQLMonitor，命令提示符将为mysql>|345863|运行load+data+infile命令(不要忘记以分号;结尾)|345864|如下所示：|345865|load+data+local+infile+'/home/tony/Desktop/2013Mini.csv'+into+table+Reading_Table+FIELDS+TERMINATED+BY+','+ENCLOSED+BY+'"'+LINES+TERMINATED+BY+'\n';|code-block|syntax|javascript|345866|entityMap^0|1I|G|0|0|2|1E|0|0|O|6|0|2|G|S|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|15|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|N|3|O|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1E|8|@]|D|@]|E|$]]|$1|R|3|S|5|T|7|1F|8|@]|D|@]|E|$U|V]]|$1|W|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|X|$]]

in case your flavor of mysql on ubuntu does NOT under any circumstances work and you still get the 1148 error, you can run the <code>load data infile</code> command via command line

open a terminal window

run <code>mysql -u YOURUSERNAME -p --local-infile YOURDBNAME</code>

you will be requested to insert mysqluser password

you will be running MySQLMonitor and your command prompt will be <code>mysql&gt;</code>

run your <code>load data infile</code> command (dont forget to end with a semicolon <code>;</code> )

like this:

<pre><code>load data local infile '/home/tony/Desktop/2013Mini.csv' into table Reading_Table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
</code></pre>

blocks|key|562214|text|在client和mysqld部分中添加local_infile。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|562215|[client]
local_infile=1
...

[mysqld]
local_infile=1
...|code-block|syntax|javascript|562216|在Windows和Linux下的MySQL+8.x中测试。|562217|entityMap^0|1|6|8|6|J|C|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Add <code>local_infile</code> in both <code>client</code> and <code>mysqld</code> section.

<pre><code>[client]
local_infile=1
...

[mysqld]
local_infile=1
...
</code></pre>

Tested in MySQL 8.x both in Windows and Linux.

blocks|key|561953|text|另一种方法是使用mysqlimport客户端程序。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|561954|您可以按如下方式调用它：|561955|mysqlimport+-uTheUsername+-pThePassword+--local+yourDatabaseName+tableName.txt|code-block|syntax|javascript|561956|这将生成一条LOAD+DATA语句，将tableName.txt加载到tableName表中。|561957|请记住以下几点：|561958|mysqlimport根据您提供的文件确定表名；使用从文件名开头到第一个句点的所有文本作为表名。因此，如果您希望将多个文件加载到同一个表中，则可以像tableName.1.txt、tableName.2.txt等一样区分它们。|561959|entityMap^0|8|B|0|0|0|6|9|J|D|Z|9|0|0|0|B|22|F|2I|F|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|17|8|@$9|18|A|19|B|C]|$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|T|$]]

Another way is to use the <code>mysqlimport</code> client program.

You invoke it as follows:

<pre><code>mysqlimport -uTheUsername -pThePassword --local yourDatabaseName tableName.txt
</code></pre>

This generates a <code>LOAD DATA</code> statement which loads <code>tableName.txt</code> into the <code>tableName</code> table.

Keep in mind the following:

<code>mysqlimport</code> determines the table name from the file you provide; using all text from the start of the file name up to the first period as the table name. So, if you wish to load several files to the same table you could distinguish them like <code>tableName.1.txt</code>, <code>tableName.2.txt</code>,..., etc, for example.

blocks|key|562030|text|这对我来说有点奇怪，从一天到另一天，脚本一直在工作，只是停止了工作。没有更新版本的mysql或任何类型的升级，但我得到了相同的错误，所以我最后一次尝试CSV文件，并注意到行尾使用的是\n而不是预期的(根据我的脚本)+\r\n所以我使用正确的EOL保存它并再次运行脚本，没有任何问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|562031|我认为mysql告诉我这个MySQL版本不允许使用这个命令有点奇怪，因为原因完全不同。|562032|我的工作命令如下所示：|562033|LOAD+DATA+LOCAL+INFILE+'file-name'+IGNORE+INTO+TABLE+table-name+CHARACTER+SET+latin1+FIELDS+TERMINATED+BY+','+OPTIONALLY+ENCLOSED+BY+'\"'+LINES+TERMINATED+BY+'\r\n'+IGNORE+1+LINES.|code-block|syntax|javascript|562034|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This went a little weird for me, from one day to the next one the script that have been working since days just stop working. There wasn´t a newer version of mysql or any kind of upgrade but I was getting the same error, so I give a last try to the CSV file and notice that the end of lines were using \n instead of the expected ( per my script ) \r\n so I save it with the right EOL and run the script again without any trouble.

I think is kind of odd for mysql to tell me The used command is not allowed with this MySQL version since the reason was completely different.

My working command looks like this:

<pre><code>LOAD DATA LOCAL INFILE 'file-name' IGNORE INTO TABLE table-name CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES.
</code></pre>

blocks|key|562091|text|如果是MySQL5.7，你可以使用"show+global+variable+like+"local_infile“;”来给出本地文件的状态，你可以使用"set+global+local_infile=ON；“打开它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|562092|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In case if Mysql 5.7 you can use "show global variables like "local_infile" ;" which will give the local infile status ,You can turn it on using "set global local_infile=ON ; ".

blocks|key|562266|text|我使用的是xampp+v3.2.4和mysql+server+8.0.20。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|562267|我将local-infile=1添加到[mysql]中，并将[mysqld]添加到"my.ini“文件中。该文件位于"C:\xampp\mysql\bin\my.ini“。|offset|length|style|CODE|562268|然后，我使用以下代码LOAD+DATA+INFILE+...插入csv文件中的数据。移动到本地是很重要的。否则它就不会起作用。|562269|感谢以上所有的建议。一个组合终于对我起作用了。|562270|entityMap^0|0|2|E|J|7|U|8|0|A|K|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]|$D|R|E|S|F|G]|$D|T|E|U|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|M|$]]

I am using xampp v3.2.4 and mysql server 8.0.20.

I added <code>local-infile=1</code> to <code>[mysql]</code> and <code>[mysqld]</code> in the file "my.ini". The file is located at "C:\xampp\mysql\bin\my.ini".

Then I inserted the data from csv file using the following code <code>LOAD DATA INFILE ...</code>. It is important to move LOCAL. Otherwise it won't work. 

Thanks for all suggestions above. A combination finally worked out for me.

blocks|key|345795|text|好吧，这里发生了一些奇怪的事情。为此，不需要在/etc/mysql/my.cnf中进行任何配置更改。您需要做的就是在终端中重新启动当前的mysql服务：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|345796|sudo+service+mysql+restart|code-block|syntax|javascript|345797|然后，如果我想“重新创建”这个bug，我只需重新启动apache服务：|345798|sudo+service+apache2+restart|345799|然后可以通过输入以下命令再次修复该问题：|345800|sudo+service+mysql+restart|345801|因此，apache2似乎正在做一些事情，在启动时不允许此功能(如果重新启动mysql服务，则会反转/更正)。|345802|在基于Debian的发行版中有效。|345803|service+mysqld+restart
service+httpd+restart|345804|在基于RedHat的发行版中有效|345805|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Ok, something odd is happening here. To make this work, do NOT need to make any configuration changes in /etc/mysql/my.cnf . All you need to do is to restart the current mysql service in terminal:

<pre><code>sudo service mysql restart
</code></pre>

Then if I want to "recreate" the bug, I simply restart the apache service:

<pre><code>sudo service apache2 restart
</code></pre>

Which can then be fixed again by entering the following command:

<pre><code>sudo service mysql restart
</code></pre>

So, it appears that the apache2 is doing something to not allow this feature
when it starts up (which is then reversed/corrected if restart the mysql service).

Valid in Debian based distributions.

<pre><code>service mysqld restart
service httpd restart
</code></pre>

Valid in RedHat based distributions

blocks|key|561875|text|对于那些正在寻找答案以使LOAD+DATA+LOCAL+INFILE像我一样工作的人来说，这可能是可行的。好吧，它对我很有效，所以它开始了。按照链接中的步骤安装percona作为您的mysql服务器和客户机。在安装过程中将提示输入密码，因此请提供一个您将记住并在以后使用的密码。安装完成后，重新启动系统并测试服务器是否已启动并运行，方法是转到terminal，键入mysql+-u+root+-p，然后输入密码。现在尝试运行命令LOAD+DATA+LOCAL+INFILE。希望它能正常工作:)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|561876|顺便说一句，我在Ubuntu+12.04上使用Ruby+1.9.3开发Rails+2.3。|561877|entityMap|0|LINK|mutability|MUTABLE|url|http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html^0|M|5|28|7|4R|8|52|G|5Y|M|28|7|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@$9|10|A|11|1|12]]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

For those of you looking for answers to make LOAD DATA <code>LOCAL</code> INFILE work like me, this might probably work. Well it worked for me, so here it goes. Install <a href="http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html" rel="nofollow"><code>percona</code></a> as your mysql server and client by following the steps from the link. A password will be prompted for during the installation, so provide one that you'll remember and use it later. One the installation is done, reboot your system and test if the server is up and running by going to the <code>terminal</code> and typing <code>mysql -u root -p</code> and then the password. Try running the command <code>LOAD DATA LOCAL INFILE</code> now.. Hope it works :)

BTW I was working on Rails 2.3 with Ruby 1.9.3 on Ubuntu 12.04.

blocks|key|562328|text|我知道这不完全是OP所要求的，但由于这个帖子很老，这里提出的解决方案对我来说都不起作用，所以我决定分享这篇文章。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|562329|如果有人在MySql版本8中启用local_infile时遇到问题，这里的命令可以帮我解决这个问题：|562330|SET+PERSIST+local_infile+=+1;|code-block|syntax|javascript|562331|它将配置保存在"mysqld-auto.cnf“配置文件中，然后在服务或服务器重新启动后会记住所做的更改。|562332|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I know this is not exactly what the OP is asking, but as this thread is quite old and none of the solutions proposed here worked for me, I decided to share this.
If someone is having trouble enabling local_infile in the version 8 of MySql, this command here did the trick for me:
<pre><code>SET PERSIST local_infile = 1;
</code></pre>
It persists the configuration on the &quot;mysqld-auto.cnf&quot; config file and then the change will be remembered after service or server restart.

I'm running Mysql 5.5 on Ubuntu 12 LTS. How should I enable LOAD DATA LOCAL INFILE in my.cnf?

I've tried adding local-infile in my config at various places but I'm still getting the "The used command is not allowed with this MySQL version"

MySQL: Enable LOAD DATA LOCAL INFILE

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我在Ubuntu 12 LTS上运行MySQL5.5。如何在my.cnf中启用LOAD DATA LOCAL INFILE？我在不同的地方尝试在我的配置中添加本地文件，但是我仍然得到“使用的命令在这个MySQL版本中是不允许的”

问MySQL:启用LOAD DATA LOCAL INFILE
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:启用LOAD DATA LOCAL INFILEEN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:启用LOAD DATA LOCAL INFILE
EN