mysqldump 使用--tab=path参数时提示mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

报错：

[root@zedu test]# mysqldump -h127.0.0.1 -uroot -p --single-transaction --add-drop-database --tab="/root/test/" cacti Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0;

-- -- GTID state at the beginning of the backup --

SET @@GLOBAL.GTID_PURGED='7f308b50-7701-11e8-8ee3-000c2923f3c9:1-31115'; mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

 

查看配置文件：

[root@zedu /]# grep "secure" /data/mysql/mysql3306/my3306.cnf
secure_file_priv                    =/tmp                               #      null

解决：

[root@tedu /]# mysqldump -h127.0.0.1 -uroot -p --single-transaction --add-drop-database --tab="/tmp" cacti Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0;

-- -- GTID state at the beginning of the backup --

SET @@GLOBAL.GTID_PURGED='7f308b50-7701-11e8-8ee3-000c2923f3c9:1-31115'; SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

原因：

secure-file-priv参数是用来限制LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE()传到哪个指定目录的。

当secure_file_priv的值为null ，表示限制mysqld 不允许导入|导出

当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下

当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

查看数据库当前该参数的值

root@localhost:mysql.sock  00:14:52 [(none)]>show global variables like '%secure%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| secure_auth      | ON    |

| secure_file_priv | NULL  |

+------------------+-------+

2 rows in set (0.00 sec)

清楚地看到secure_file_priv 的值是NULL，说明此时限制导入导出的

所以应该改变该参数

可是查看了mysql.cnf中居然没有对这个参数进行设定，就说明这个参数默认便是null

所以再mysql.cnf中的[mysqld]加入secure_file_priv = 

再重启mysql服务

然后再查一下此时参数的值

root@localhost:mysql.sock  00:28:30 [(none)]>show global variables like '%secure%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| secure_auth      | ON    |

| secure_file_priv |       |

+------------------+-------+

2 rows in set (0.00 sec)

 

可以看到成功了