如何使用 SELECT INTO OUTFILE 绕过 MySQL Errcode 13？

Question

提问by Ryan Olson

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do:

我正在尝试使用 MySQL SELECT INTO OUTFILE 语句将表的内容转储到 csv 文件。如果我做：

SELECT column1, column2
INTO OUTFILE 'outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

outfile.csv will be created on the server in the same directory this database's files are stored in.

outfile.csv 将在服务器上存储此数据库文件的同一目录中创建。

However, when I change my query to:

但是，当我将查询更改为：

SELECT column1, column2
INTO OUTFILE '/data/outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

I get:

我得到：

ERROR 1 (HY000): Can't create/write to file '/data/outfile.csv' (Errcode: 13)

Errcode 13 is a permissions error, but I get it even if I change ownership of /data to mysql:mysql and give it 777 permissions. MySQL is running as user "mysql".

Errcode 13 是权限错误，但即使我将 /data 的所有权更改为 mysql:mysql 并赋予它 777 权限，我也会得到它。MySQL 以用户“mysql”的身份运行。

Strangely I can create the file in /tmp, just not in any other directory I've tried, even with permissions set such that user mysql should be able to write to the directory.

奇怪的是，我可以在 /tmp 中创建文件，但不能在我尝试过的任何其他目录中创建文件，即使权限设置为用户 mysql 应该能够写入该目录。

This is MySQL 5.0.75 running on Ubuntu.

这是在 Ubuntu 上运行的 MySQL 5.0.75。

Answer 1

回答by Vin-G

Which particular version of Ubuntu is this and is this Ubuntu Server Edition?

这是哪个特定版本的 Ubuntu，这是 Ubuntu 服务器版？

Recent Ubuntu Server Editions (such as 10.04) ship with AppArmor and MySQL's profile might be in enforcing mode by default. You can check this by executing sudo aa-statuslike so:

最近的 Ubuntu 服务器版本（例如 10.04）随 AppArmor 一起提供，并且默认情况下 MySQL 的配置文件可能处于强制模式。您可以sudo aa-status像这样执行来检查这一点：

# sudo aa-status
5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.

If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messageswhen AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqldand add /data/and /data/*near the bottom like so:

如果 mysqld 包含在强制模式中，那么它可能会拒绝写入。/var/log/messages当 AppArmor 阻止写入/访问时，也会写入条目。您可以做的是在底部附近编辑/etc/apparmor.d/usr.sbin.mysqld和添加/data/，/data/*如下所示：

...  
/usr/sbin/mysqld  {  
    ...  
    /var/log/mysql/ r,  
    /var/log/mysql/* rw,  
    /var/run/mysqld/mysqld.pid w,  
    /var/run/mysqld/mysqld.sock w,  
    **/data/ r,  
    /data/* rw,**  
}

And then make AppArmor reload the profiles.

然后让 AppArmor 重新加载配置文件。

# sudo /etc/init.d/apparmor reload

WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.

警告：上述更改将允许 MySQL 读取和写入 /data 目录。我们希望您已经考虑了这对安全的影响。

Answer 2

回答by rook

Ubuntu uses AppArmor and that is whats preventing you from accessing /data/. Fedora uses selinux and that would prevent this on a RHEL/Fedora/CentOS machine.

Ubuntu 使用 AppArmor，这是阻止您访问 /data/ 的原因。Fedora 使用 selinux，这会在 RHEL/Fedora/CentOS 机器上阻止这种情况。

To modify AppArmor to allow MySQL to access /data/ do the follow:

要修改 AppArmor 以允许 MySQL 访问 /data/，请执行以下操作：

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

add this line anywhere in the list of directories:

将此行添加到目录列表中的任何位置：

/data/ rw,

then do a :

然后做一个：

sudo /etc/init.d/apparmor restart

Another option is to disable AppArmor for mysql altogether, this is NOT RECOMMENDED:

另一种选择是完全禁用 mysql 的 AppArmor，这是不推荐的：

sudo mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

Don't forget to restart apparmor:

不要忘记重新启动apparmor：

sudo /etc/init.d/apparmor restart

Answer 3

回答by basilikode

I know you said that you tried already setting permissions to 777, but as I have an evidence that for me it was a permission issue I'm posting what I exactly run hoping it can help. Here is my experience:

我知道你说过你已经尝试将权限设置为 777，但是我有证据表明对我来说这是一个权限问题，我正在发布我正在运行的内容，希望它可以提供帮助。这是我的经验：

tmp $ pwd
/Users/username/tmp
tmp $ mkdir bkptest
tmp $ mysqldump -u root -T bkptest bkptest
mysqldump: Got error: 1: Can't create/write to file '/Users/username/tmp/bkptest/people.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
tmp $ chmod a+rwx bkptest/
tmp $ mysqldump -u root -T bkptest bkptest
tmp $ ls bkptest/
people.sql  people.txt
tmp $

Answer 4

回答by vimdude

MySQL is getting stupid here. It tries to create files under /tmp/data/.... So what you can do is the following:

MySQL在这里变得愚蠢。它尝试在 /tmp/data/... 下创建文件。因此您可以执行以下操作：

mkdir /tmp/data
mount --bind /data /tmp/data

Then try your query. This worked for me after hours of debugging the issue.

然后尝试您的查询。经过数小时的调试后，这对我有用。

Answer 5

回答by Navrattan Yadav

You can do this :

你可以这样做：

mysql -u USERNAME --password=PASSWORD --database=DATABASE --execute='SELECT `FIELD`, `FIELD` FROM `TABLE` LIMIT 0, 10000 ' -X > file.xml

Answer 6

回答by fanchyna

This problem has been bothering me for a long time. I noticed that this discussion does not point out the solution on RHEL/Fecora. I am using RHEL and I do not find the configuration files corresponding to AppArmer on Ubuntu, but I solved my problem by making EVERY directory in the directory PATH readable and accessible by mysql. For example, if you create a directory /tmp, the following two commands make SELECT INTO OUTFILE able to output the .sql AND .sql file

这个问题困扰我很久了。我注意到这个讨论没有指出 RHEL/Fecora 上的解决方案。我正在使用 RHEL，我在 Ubuntu 上没有找到与 AppArmer 对应的配置文件，但是我通过使目录 PATH 中的每个目录都可以被 mysql 读取和访问来解决我的问题。例如，如果您创建目录 /tmp，则以下两个命令使 SELECT INTO OUTFILE 能够输出 .sql AND .sql 文件

chown mysql:mysql /tmp
chmod a+rx /tmp

If you create a directory in your home directory /home/tom, you must do this for both /home and /home/tom.

如果您在主目录 /home/tom 中创建目录，则必须为 /home 和 /home/tom 执行此操作。

Answer 7

回答by mdma

Some things to try:

一些尝试：

is the secure_file_privsystem variable set? If it is, all files must be written to that directory.
ensure that the file does not exist - MySQL will only create new files, not overwrite existing ones.

是secure_file_priv系统变量设定的？如果是，则所有文件都必须写入该目录。
确保文件不存在 - MySQL 只会创建新文件，不会覆盖现有文件。

Answer 8

回答by Sunny S.M

I have same problem and I fixed this issue by following steps:

我有同样的问题，我通过以下步骤解决了这个问题：

Operating system : ubuntu 12.04
lamp installed
suppose your directory to save output file is : /var/www/csv/

操作系统：ubuntu 12.04
灯已安装
假设您保存输出文件的目录是：/var/www/csv/

Execute following command on terminal and edit this file using gedit editor to add your directory to output file.

在终端上执行以下命令并使用 gedit 编辑器编辑此文件以将您的目录添加到输出文件。

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

须藤 gedit /etc/apparmor.d/usr.sbin.mysqld

now file would be opened in editor please add your directory there
/var/www/csv/* rw,
likewise I have added in my file, as following given image :

现在文件将在编辑器中打开，请在那里添加您的目录
/var/www/csv/* rw,
同样，我已添加到我的文件中，如下所示：

Execute next command to restart services :

执行下一个命令重启服务：

sudo /etc/init.d/apparmor restart

须藤 /etc/init.d/apparmor 重启

For example I execute following query into phpmyadmin query builder to output data in csv file

例如，我在 phpmyadmin 查询构建器中执行以下查询以在 csv 文件中输出数据

SELECT colName1, colName2,colName3
INTO OUTFILE '/var/www/csv/OUTFILE.csv'
FIELDS TERMINATED BY ','
FROM tableName;

It successfully done and write all rows with selected columns into OUTPUT.csv file...

它成功完成并将所有具有选定列的行写入 OUTPUT.csv 文件...

Answer 9

回答by Matthew McMillan

I just ran into this same problem. My issue was the directory that I was trying to dump into didn't have write permission for the mysqld process. The initial sql dump would write out but the write of the csv/txt file would fail. Looks like the sql dump runs as the current user and the conversion to csv/txt is run as the user that is running mysqld. So the directory needs write permissions for both users.

我刚刚遇到了同样的问题。我的问题是我试图转储到的目录没有 mysqld 进程的写权限。最初的 sql 转储会写出，但 csv/txt 文件的写入会失败。看起来 sql 转储以当前用户身份运行，而到 csv/txt 的转换以运行 mysqld 的用户身份运行。所以目录需要两个用户的写权限。

Answer 10

回答by Alsciende

In my case, the solution was to make every directory in the directory path readable and accessible by mysql(chmod a+rx). The directory was still specified by its relative path in the command line.

就我而言，解决方案是使目录路径中的每个目录都可以通过mysql( chmod a+rx)读取和访问。该目录仍由其在命令行中的相对路径指定。

chmod a+rx /tmp
chmod a+rx /tmp/migration
etc.

如何使用 SELECT INTO OUTFILE 绕过 MySQL Errcode 13？

提问by Ryan Olson

回答by Vin-G

回答by rook

回答by basilikode

回答by vimdude

回答by Navrattan Yadav

回答by fanchyna

回答by mdma

回答by Sunny S.M

回答by Matthew McMillan

回答by Alsciende

相关推荐

最近更新

标签

如何使用 SELECT INTO OUTFILE 绕过 MySQL Errcode 13？

提问by Ryan Olson

回答by Vin-G

回答by rook

回答by basilikode

回答by vimdude

回答by Navrattan Yadav

回答by fanchyna

回答by mdma

回答by Sunny S.M

回答by Matthew McMillan

回答by Alsciende

相关推荐

MySQL 如何在特定数据库中创建表？

Mysql 日期功能不工作少于

MySQL 在mysql sproc中使用表名变量

MySQL 在mysql中测试查询的性能

相关推荐

最近更新

标签