[Solved] MYSQL Export Sheet Error: –secure-file-priv option so it cannot execute this statement

When MySQL exports a file, the following is displayed:

mysql> select * from users into outfile '/root/users.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The reason is not clear. It is said on the Internet that operations like into are only allowed to users with file permission. In short, it is about permissions. Here we need to use a table that stores the system parameters of MySQL:

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

Here, my secure_file_priv field has a value and is fixed in /var/lib/mysql-files/, which means that Mysql export data can only be exported to this directory, and export is not authorized in other places:

mysql> select * from users into outfile '/var/lib/mysql-files/users.txt';
Query OK, 6 rows affected (0.00 sec)

The value of the secure_file_priv field here can be set (I don’t know how to change it, Baidu myself)

  • If it is empty, the variable is invalid and can be exported without restriction. This is not a safe setting
  • If set to the name of a directory, the server will restrict import and export operations to only process files in that directory. The directory must exist; the server will not create it.
  • If set to NULL, the server will disable import and export operations.

Modify system variables

mysql> set global secure_file_priv = ''; 
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable //
Directly modify the display read only, so you can only start from the configuration file

Open another window and directly modify the configuration file my.cnf. I have omitted a lot of comments here. When making changes, please note that you cannot directly add secure_file_priv=” to the last line. This is incorrect, because the last line is in the [mysql] column. Variables need to be added under [mysqld]

[root@VM-0-7-centos mysql-files]# vim /etc/my.cnf
……
[mysqld]
……

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
……
secure_file_priv = ''   //correct position added here, [ mysqld ] in which
[mysqld_safe] 
…… 
[mysql]
……

Restart MySQL service after adding

[root@VM-0-7-centos mysql-files]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

After restarting, log in to MySQL again and find that it has been changed:

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

Similar Posts: