Shulou(Shulou.com)06/01 Report--

The following mainly brings you the emergence of mysql5.7 export data-- secure-file-priv option response method. I hope that when mysql5.7 exports data-- secure-file-priv option response method can bring you practical use, which is also my main purpose of editing this article. All right, don't talk too much nonsense, let's just read the following.

Mysql can use the into outfile parameter to export data from a table to csv. For example, you can export data from a user table to user.csv using the following command

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

After execution, the data from the user table is exported to / tmp/user.csv.

Parameter description:

Into outfile 'exported directories and filenames'

Specify the exported directory and file name

Fields terminated by 'field delimiter'

Define delimiters between fields

Optionally enclosed by 'field bounding character'

Define the characters that surround the field (numeric field is not valid)

Lines terminated by 'line delimiter'

Define the delimiter for each line

Analysis of problems

The above command has no problem running under mysql5.6, but the following error occurred 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

Check the official documentation. The secure_file_priv parameter is used to restrict LOAD DATA, SELECT... OUTFILE, LOAD_FILE () to which specified directory.

When secure_file_priv is NULL, it means that the restriction mysqld does not allow import or export.

When secure_file_priv is / tmp, it restricts mysqld to perform import and export only in the / tmp directory, but not in other directories.

When secure_file_priv has no value, it does not restrict the import and export of mysqld in any directory.

Check the value of secure_file_priv. The default is NULL, which means that the limit 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 read-only, it 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 method

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

Secure_file_priv=''

View the modified values 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 it again and export it successfully.

'

After execution, the data from the user table is exported to / tmp/user.csv.

Parameter description:

Into outfile 'exported directories and filenames'

Specify the exported directory and file name

Fields terminated by 'field delimiter'

Define delimiters between fields

Optionally enclosed by 'field bounding character'

Define the characters that surround the field (numeric field is not valid)

Lines terminated by 'line delimiter'

Define the delimiter for each line

Analysis of problems

The above command has no problem running under mysql5.6, but the following error occurred 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

Check the official documentation. The secure_file_priv parameter is used to restrict LOAD DATA, SELECT... OUTFILE, LOAD_FILE () to which specified directory.

When secure_file_priv is NULL, it means that the restriction mysqld does not allow import or export.

When secure_file_priv is / tmp, it restricts mysqld to perform import and export only in the / tmp directory, but not in other directories.

When secure_file_priv has no value, it does not restrict the import and export of mysqld in any directory.

Check the value of secure_file_priv. The default is NULL, which means that the limit 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 read-only, it 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 method

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

Secure_file_priv=''

View the modified values 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 it again and export it successfully.

Mysql > select * from user into outfile'/ tmp/user.csv' fields terminated by', 'optionally enclosed by' "'lines terminated by'\ r\ nQuery OK, 15 rows affected (0.00 sec)

For the above about mysql5.7 export data appears-- secure-file-priv option to deal with the method, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.