mysql出先erroy_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,"导弹专业"