mysqldump Got error 1290

在用mysqldump备份时候遇到1290的错误

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  query_rewrite
mysqldump: [Warning] Using a password on the command line interface can be insecure.
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'

从提示看到是因为mysql服务启用了–secure-file-priv,所以才无法执行。这个选项或系统变量用被来限制通过load data和select … into outfile语句和load_file()函数所执行导入和导出操作的数据量。

secure_file_priv有以下设置:
.如果为空,变量不生效。没有安全设置。
.如果设置为一个目录名,服务器对导入和导出操作只对这个目录中的文件生效,但目录必须存在,服务器不会创建目录。
.如果设置NULL,服务器禁止导入和导出操作。

查看数据库当前设置:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。

查看配置文件my.cnf发现没有设置secure_file_priv参数,那么这个参数默认应该是为NULL。

[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置文件my.cnf参加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重启mysql数据库

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次检查secure_file_priv参数设置

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

再执行导出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.