如何通过SELECT INTO OUTFILE解决MySQL Errcode 13? [英] How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

问题描述

我正在尝试使用MySQL SELECT INTO OUTFILE语句将表的内容转储到csv文件中.如果我这样做:

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

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

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

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

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

However, when I change my query to:

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

我得到:

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

错误代码13是一个权限错误，但是即使我将/data的所有权更改为mysql:mysql并赋予它777权限，我也得到了它. MySQL以用户"mysql"的身份运行.

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".

奇怪的是，我可以在/tmp中创建该文件，只是不能在我尝试过的任何其他目录中创建该文件，即使设置了权限，使得用户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.

这是在Ubuntu上运行的MySQL 5.0.75.

This is MySQL 5.0.75 running on Ubuntu.

推荐答案

这是Ubuntu的哪个特定版本，并且这是Ubuntu Server Edition?

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

AppArmor附带的最新Ubuntu Server Edition(例如10.04)和MySQL的配置文件默认情况下处于强制模式.您可以像这样执行sudo aa-status来检查它:

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-status like so:

# 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.

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

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

...  
/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,**  
}

然后使AppArmor重新加载配置文件.

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

警告:上面的更改将使MySQL可以读写/data目录.我们希望您已经考虑了此问题的安全性.

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.

这篇关于如何通过SELECT INTO OUTFILE解决MySQL Errcode 13?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！