MySQL 5.8 导出文件 报错 secure-file-priv

By 刘志军 , 2019-06-03, 分类: qa

mysql

在执行一个批量杀进程的SQL时,本来我想将执行结果导出到文件,结果出现错误提示:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 SELECT CONCAT('KILL ',id,';') AS run_this FROM information_schema.processlist WHERE Host LIKE 'xxxx%'  ORDER BY id into outfile '/tmp/kill_process.txt'

大致意思是MySQL启动的指定了参数 secure-file-priv,所以你要将文件导入到指定的目录下面去。

可以先查下该目录在什么位置

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

重新执行:

mysql> SELECT CONCAT('KILL ',id,';') AS run_this FROM information_schema.processlist WHERE Host LIKE 'xxxx%'  ORDER BY id into outfile '/var/lib/mysql-files/kill_process.txt';
Query OK, 127 rows affected (0.14 sec)

再愉快的把进程杀掉

mysql> source /var/lib/mysql-files/kill_process.txt

大功告成


关注公众号「Python之禅」,回复「1024」免费获取Python资源

python之禅

猜你喜欢

2016-08-11
CentOS7 环境 MySQL5.7 安装
2014-02-16
系统学习MySQL
2014-02-17
MySQL分表分区
2016-03-03
MySQL启动报错问题排查:InnoDB: Unable to lock ./ibdata1 error
2019-06-05
我也来记一次 关于 MySQL Waiting for table metadata lock 的问题
2017-04-27
基于Mycat分布式MySQL数据库部署实践指南
2014-04-03
MySQL慢查询配置
2016-05-13
CentOS重新安装MySQL5.6