MySQL:加载数据文件 [英] MySQL : Load data infile

问题描述

使用加载数据插入查询时出现错误.

I am getting error when using load data to insert the query .

"load data infile '/home/bharathi/out.txt' into table Summary"

此文件位于的位置.但是mysql抛出以下错误. 错误29(HY000):找不到文件'/home/bharathi/out.txt'(错误代码:13)

This file is there in the location . But mysql throws the below error . ERROR 29 (HY000): File '/home/bharathi/out.txt' not found (Errcode: 13)

show variables like 'data%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

Data Dir指向根许可文件夹.我无法更改此变量，因为它是readonly.

Data Dir is pointing to root permissioned folder . I can't change this variable because it's readonly .

如何执行文件中的加载数据?

How can I do the load data infile operation ?

我尝试更改文件权限，将数据加载到本地infile中.它不会工作.

I tried changing file permissions , load data local infile . It wont work .

推荐答案

如 LOAD DATA INFILE语法中所述:

As documented under LOAD DATA INFILE Syntax:

出于安全原因，在读取服务器上的文本文件时，这些文件必须位于数据库目录中，或者所有人都可以读取.另外，要在服务器文件上使用 LOAD DATA INFILE ，您必须具有第6.2.1节"MySQL提供的特权" .对于非LOCAL加载操作，如果 secure_file_priv 系统变量设置为非空目录名称，则要加载的文件必须位于该目录中.

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, "Privileges Provided by MySQL". For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

因此，您应该:

• 确保您的MySQL用户具有FILE特权，并假定未设置secure_file_priv系统变量:

• Ensure that your MySQL user has the FILE privilege and, assuming that the secure_file_priv system variable is not set:

• 使文件对所有人可读；或

• make the file readable by all; or

将文件移动到数据库目录中.

move the file into the database directory.

否则，使用LOCAL关键字使文件由客户端读取并传输到服务器.但是，请注意:

Or else, use the LOCAL keyword to have the file read by your client and transmitted to the server. However, note that:

LOCAL仅在服务器和客户端都已配置为允许它时才起作用.例如，如果 mysqld 以第6.1.6节"LOAD DATA LOCAL的安全性问题" .

LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, "Security Issues with LOAD DATA LOCAL".

这篇关于MySQL:加载数据文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！