ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot exec

在做mysql 大批量导出数据的时候，所遇到的问题如下。

select * from shop_user into outfile '/tmp/mysql/backup/shop_user.sql';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

应该是mysql设置的权限，查看 secure-file-priv 当前的值是什么

show variables like '%secure%';

+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| secure_auth      | ON                 |
| secure_file_priv | NULL             |
+------------------+--------------------+

secure_file_priv默认有可能是NULL就代表禁止导出，所以需要设置一下，
导出的数据必须是这个值的指定路径才可以导出，因此我们修改一下my.cnf即可，这里特别强调一下，secure_file_priv的目录权限一定要给mysql

secure_file_priv = /tmp/mysql/backup 

修改后要重启mysql服务，使配置生效

mysql> select * from shop_user into outfile '/tmp/mysql/backup/shop_user.sql';
Query OK, 2 rows affected (0.00 sec)