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

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

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)

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

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)

Execute again after modification and export successfully.

';

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

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

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)

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)

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!