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 SQL Getting Started Tutorial ManualThe 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!