MYSQ Learning: Exporting the results of a SQL query to a specific file by command

Introduction

Recently in the modification of data on the line, you need to now continue to back up the modified data, but the online customer's server is not directly connected, but through a bastion machine, which means that we can not directly connect to MySQL through the visual client, so all operations are required through the SQL statement, Let's take a look at the exported sql:

Mysql> Select COUNT (1) from table to  outfile '/tmp/test.xls ';

Add the into outfile ' path directly behind the results of our query, but at the beginning, the path I added later is not/tmp, but/data throws the following error after execution:

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, and we can look at the permission settings from the following sql:

Show variables like '%secure% ';

The exported data must be the specified path of this value can be exported, the default is likely to be null on behalf of the forbidden export, so need to set up;

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

Secure_file_priv

1, limit mysqld not allowed to 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, do not import the Mysqld | Export to make restrictions

Secure_file_priv

4, can be exported to any directory

secure_file_priv= "/"

This allows us to implement some of our custom export!

Related articles:

Alternative ways to export MySQL query results to a file

From the command line, how to import SQL file into MySQL

Related videos:

SQL Getting Started Tutorial manual