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

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

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

This is because mysql For the set permissions, 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. 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!