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

mysql can use theinto outfileparameter 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 undermysql5.6, but the following error occurs when running undermysql5.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, thesecure_file_privparameter is used to limit the specified directory to whichLOAD DATA, SELECT...OUTFILE, LOAD_FILE()is passed.

• secure_file_priv When it isNULL, 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_privWhen there is no value, it means that mysqld is not restricted from importing and exporting in any directory.

Check the value ofsecure_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 thesecure_file_privparameter 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

Openmy.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 undermysql5.6, but the following error occurs when running undermysql5.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, thesecure_file_privparameter is used to limit the specified directory to whichLOAD DATA, SELECT...OUTFILE, LOAD_FILE()is passed.

• secure_file_priv isNULL, 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_privWhen there is no value, it means that mysqld is not restricted from importing and exporting in any directory.

Check the value ofsecure_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 thesecure_file_privparameter 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

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