Home > Backend Development > PHP Tutorial > Solution to mysql secure-file-priv option problem

Solution to mysql secure-file-priv option problem

jacklove
Release: 2023-04-01 12:16:01
Original
8869 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
Copy after login

After execution, the user table Data is 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


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

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

Solution

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

secure_file_priv=''
Copy after login

Check 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

After modification, execute again 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

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
Copy after login

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

This article explains the solution to the problem of the MySQL5.7 export data prompt --secure-file-priv option. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

php uses the debug_backtrace method to track code calls

mysql executes sql in the terminal and writes the results File method

#php uses token bucket algorithm to implement flow control based on redis

The above is the detailed content of Solution to mysql secure-file-priv option problem. 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