Saving MySql list to text file to counter '-secure-file-priv' error

Posted under » MySQL on 17 Aug 2021

This is the command to save your list to txt file.

SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'

However when you encounter the '--secure-file-priv' error, you need to find out where is the secure location to save the text file at.

Your MySQL server has been started with --secure-file-priv option which basically limits from which directories you can load files using LOAD DATA INFILE. We need to find out where is the secure location or directory to save the text file at by the following command.

SHOW VARIABLES LIKE "secure_file_priv"; 

In my case I have to change my MySQL command to

SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/var/lib/mysql-files/orders.txt'