MySQL into outfile 问题解决：ERROR 1 (HY000): Can't create/write to file_into outfile 报错

• 任务：从MySQL导数据导出数据到本地——select * into outfile '/home/spark/data.csv' fields terminated by ',' lines terminated by '\n' from mytable;
• 错误信息：ERROR 1 (HY000): Can't create/write to file '/home/spark/data.csv' (Errcode: 2 - No such file or directory)
• 错误原因：由于Ubuntu版本的原因，有些版本在默认情况下是强制限制MySQL导出文件到除了MySQL的数据库文件（datadir）之外的其他路径。就是说，默认情况下只能导出到指定数据库文件。

<1>查看指定数据库文件路经命令：

#进入MySQL
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

<2>查看自己Ubuntu版本是否有上述强制限制：

$ sudo aa-status
apparmor module is loaded.
23 profiles are loaded.
23 profiles are in enforce mode.
   /sbin/dhclient
   /usr/bin/evince
   /usr/bin/evince-previewer
   /usr/bin/evince-previewer//sanitized_helper
   /usr/bin/evince-thumbnailer
   /usr/bin/evince-thumbnailer//sanitized_helper
   /usr/bin/evince//sanitized_helper
   /usr/bin/ubuntu-core-launcher
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/lib/cups/backend/cups-pdf
   /usr/lib/telepathy/mission-control-5
   /usr/lib/telepathy/telepathy-*
   /usr/lib/telepathy/telepathy-*//pxgsettings
   /usr/lib/telepathy/telepathy-*//sanitized_helper
   /usr/lib/telepathy/telepathy-ofono
   /usr/sbin/cups-browsed
   /usr/sbin/cupsd
   /usr/sbin/cupsd//third_party
   /usr/sbin/ippusbxd
   /usr/sbin/mysqld      #若存在该选项表示该版本存在默认强制限制
   /usr/sbin/tcpdump
0 profiles are in complain mode.
8 processes have profiles defined.
8 processes are in enforce mode.
   /sbin/dhclient (948) 
   /usr/lib/telepathy/mission-control-5 (1633) 
   /usr/sbin/cups-browsed (845) 
   /usr/sbin/cupsd (4588) 
   /usr/sbin/cupsd (4590) 
   /usr/sbin/cupsd (4591) 
   /usr/sbin/cupsd (4592) 
   /usr/sbin/mysqld (916) 
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

• 解决方法：修改文件/etc/apparmor.d/usr.sbin.mysqld

#打开终端
$ sudo vim /etc/apparmor.d/usr.sbin.mysqld  

#添加以下内容到/usr/sbin/mysqld {}
/home/spark/ r,
/home/spark/** rwk,

#退出MySQL，重新加载文件
$  sudo /etc/init.d/apparmor reload 
[ ok ] Reloading apparmor configuration (via systemctl): apparmor.service.
1
2
3
4
5
6
7
8
9
10