Home > Database > Mysql Tutorial > body text

Mysql5.7 export data prompt --secure-file-priv option problem solution

jacklove
Release: 2018-06-08 15:59:50
Original
16175 people have browsed it

mysql can use the into outfile parameter to export the data in the table to csv. For example, the following command can be used to export the data of the user table to user.csv

select * from user into outfile '/tmp/user.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n
Copy after login

After execution, The data of the user table will be exported to /tmp/user.csv.
Parameter description:

into outfile 'Exported directory and file name'
Specify the exported directory and file name

fields terminated by 'Field delimiter'
Define the delimiter between fields

optionally enclosed by 'Field wrapper'
Define the characters surrounding the field ( Numerical fields are invalid)

lines terminated by 'interline separator'
Define the separator for each line
Problem analysis

Above The command runs without problem under mysql5.6, but the following error occurs when running under mysql5.7.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Copy after login
Copy after login

View the official documentation, the secure_file_priv parameter is used to limit the specified directory to which LOAD DATA, SELECT...OUTFILE, LOAD_FILE() is passed.

  • secure_file_priv When it is NULL, it means that mysqld is restricted from importing or exporting.

  • secure_file_priv When it is /tmp, it means that mysqld is restricted to only perform import and export in the /tmp directory, and cannot execute it in other directories.

  • secure_file_priv When there is no value, it means that mysqld is not restricted from importing and exporting in any directory.

Check the value of secure_file_priv. The default is NULL, which means the restriction cannot be imported or exported.

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+| Variable_name    | Value |
+------------------+-------+| secure_file_priv | NULL  |
+------------------+-------+1 row in set (0.00 sec)
Copy after login
Copy after login

Because the secure_file_priv parameter is a read-only parameter and cannot be modified using the set global command.

mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
Copy after login
Copy after login

Solution

Open my.cnf or my.ini, add the following statement and restart mysql.

secure_file_priv=''
Copy after login
Copy after login

View the modified value of secure_file_priv

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

Execute again after modification and export successfully.

';
Copy after login

After execution, the data of the user table will be exported to /tmp/user.csv.
Parameter description:

into outfile 'Exported directory and file name'
Specify the exported directory and file name

fields terminated by 'Field delimiter'
Define the delimiter between fields

optionally enclosed by 'Field wrapper'
Define the characters surrounding the field ( Numerical fields are invalid)

lines terminated by 'interline delimiter'
Define the delimiter for each line
Problem analysis

Above The command runs without problem under mysql5.6, but the following error occurs when running under mysql5.7.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Copy after login
Copy after login

View the official documentation, the secure_file_priv parameter is used to limit the specified directory to which LOAD DATA, SELECT...OUTFILE, LOAD_FILE() is passed.

  • secure_file_priv is NULL, indicating that mysqld is restricted from importing or exporting.

  • secure_file_priv When it is /tmp, it means that mysqld is restricted to only perform import and export in the /tmp directory, and cannot perform it in other directories.

  • secure_file_priv When there is no value, it means that mysqld is not restricted from importing and exporting in any directory.

Check the value of secure_file_priv. The default is NULL, which means the restriction cannot be imported or exported.

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+| Variable_name    | Value |
+------------------+-------+| secure_file_priv | NULL  |
+------------------+-------+1 row in set (0.00 sec)
Copy after login
Copy after login

Because the secure_file_priv parameter is a read-only parameter and cannot be modified using the set global command.

mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
Copy after login
Copy after login

Solution

Open my.cnf or my.ini, add the following statement and restart mysql.

secure_file_priv=''
Copy after login
Copy after login

View the modified value of secure_file_priv

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

Execute again after modification and export successfully.

mysql> select * from user into outfile '/tmp/user.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';
Query OK, 15 rows affected (0.00 sec)
Copy after login

The above is the detailed content of Mysql5.7 export data prompt --secure-file-priv option problem solution. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!