Shulou(Shulou.com)05/31 Report--

This article is about how mysq commands to export the results of sql queries to specific files. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Introduction

Recently, when you modify the online data, you need to back up the modified data now. However, the server of the online customer cannot be connected directly, but through a fortress machine, which means that we cannot connect to mysql directly through the visual client, so all operations need to be done through the sql statement. Take a look at the exported sql:

Mysql > select count (1) from table into outfile'/ tmp/test.xls'

Just add the into outfile 'path' after the result of our query, but the path I added at the beginning is not / tmp but / data and throws the following error:

The MySQL server is running with the-secure-file-priv option so it cannot execute this statement

This is because of the permissions set by mysql. We can check the permission settings through the following sql:

Show variables like'% secure%'

The exported data must be the specified path of this value before it can be exported. By default, NULL means that export is prohibited, so you need to set it.

We need to set it at the end of the / etc/mysql/mysql.conf.d/mysqld.cnf file, adding a secure_file_priv= "/" at the end to export the data to any directory.

Secure_file_priv

1. Restrictions on mysqld do not allow import | Export

Secure_file_prive=null

2. Restrict the import of mysqld | Export can only occur in the / tmp/ directory.

Secure_file_priv=/tmp/

3. No restrictions on import / export of mysqld

Secure_file_priv

4. You can export to any directory

Secure_file_priv= "/"

After this setting, we can achieve some of our custom exports!

Thank you for reading! On "how mysq commands to export the results of sql query to specific files" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see 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.