Solution to mysql secure-file-priv option problem

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';

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

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)

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

Solution

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

secure_file_priv=''

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)

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)

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';

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!