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

2021-11-20 00:00:00 sql mysql 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)

Errcode 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 服务器版?

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

最近的 Ubuntu 服务器版本(例如 10.04)随 AppArmor 一起提供，默认情况下 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.

相关文章