mysql导出文件，error：1290

       感觉有毒，从昨晚开始，文件导出一直出错，结果今早发现，只要加上绝对路径就可以了。


MySQL导出文件时出现下面错误

首先你得查看你是否指定了目录

mysql> show global variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.00 sec)

如果没有，就要在my.cnf或者my.ini配置文件中添加一行

[mysqld]
secure_file_priv=/var/lib/mysql-files/

#然后重启mysql
[root@bogon ~]# systemctl restart mysqld

接下来再次尝试导出文件

mysql> select * from Student into outfile '/var/lib/mysql-files/it_Student.sql';
Query OK, 6 rows affected (0.00 sec)

导出成功，去查看导出的数据文件

[root@bogon ~]# cat /var/lib/mysql-files/it_Student.sql
1       陆亚    男      24      计算机网络
2       tom     男      26      英语
3       张阳    男      21      物流管理
4       alex    女      22      电子商务
5       xumubin 男      29      中文专业
6       wangzhao        男      21      导弹专业

还有一种导出方式，将空格去掉，换成别的符号。
同样，文件还是要加上绝对路径。

mysql> select * from Student into outfile '/var/lib/mysql-files/it1_Student.sql' fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
Query OK, 6 rows affected (0.00 sec)

去查看导出的数据文件

[root@bogon ~]# cat /var/lib/mysql-files/it1_Student.sql
1,"陆亚","男",24,"计算机网络"
2,"tom","男",26,"英语"
3,"张阳","男",21,"物流管理"
4,"alex","女",22,"电子商务"
5,"xumubin","男",29,"中文专业"
6,"wangzhao","男",21,"导弹专业"