在用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.