Home > Database > Mysql Tutorial > body text

mysq learning: Export the results of sql query to a specific file through commands

php是最好的语言
Release: 2018-07-30 13:58:24
Original
1697 people have browsed it

Introduction

When I modify online data recently, I need to continue to back up the modified data. However, the online customer's server cannot be connected directly, but through a bastion machine. , this means that we cannot directly connect to mysql through the visual client, so all operations need to go through sql statements. Let’s take a look at the exported sql:

mysql> select count(1) from table  into outfile '/tmp/test.xls';
Copy after login

Add into directly after the results of our query outfile 'path', but at the beginning, the path I added later was not /tmp but /data. After executing this, the following error was thrown:

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

This is because mysql For the set permissions, we can check the permission settings through the following sql:

show variables like '%secure%';
Copy after login

mysq learning: Export the results of sql query to a specific file through commands

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

We need to set it at the end of the /etc/mysql/mysql.conf.d/mysqld.cnf file. Add a secure_file_priv="/" at the end to set it. Data can be exported to any directory; Export can only occur in the /tmp/ directory

secure_file_priv=/tmp/

3. No restrictions on the import | export of mysqld

secure_file_priv

4. It can be exported to any directory

secure_file_priv="/"

After setting this, we can realize some of our customized exports!

Related articles:

An alternative method to export MySQL query results to a file

How to import sql files into mysql through the command line

Related videos:

SQL Getting Started Tutorial Manual

The above is the detailed content of mysq learning: Export the results of sql query to a specific file through commands. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template