How to change secure-file-priv option in MySQL on Ubuntu 20.04

Question

I am trying to use OUTFILE on Ubuntu 20.04 and getting this error: MySQL server version: 8.0.21

Code:

mysql> select * into OUTFILE '/home/yash/Desktop/data2.txt' from ticket;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I tried many solutions but it didn't work on Ubuntu 20.04

If anyone can give a solution for Ubuntu 20.04 and MySQL 8.0.21 then it will be appreciated.

Answer 1

As per your question you want to store the result of the query in a text file try 'tee' MySQL command Try this in your MySQL prompt

tee /home/yash/Desktop/data2.txt;

select * from ticket;

Are you using any shell script or connecting MySQL DB from the terminal?

If not working then disable "secure_file_priv" in mysqld.cnf then restart. set secure-file-priv = "" in mysqld.cnf file and the check SHOW VARIABLES LIKE "secure_file_priv"; and you get the below sample output

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |

Answer 2

You can use

SHOW VARIABLES LIKE "secure_file_priv"; 

to see the directory that MySQL thinks that it is secure to load or save files.

You have now two options:

1. Move your file to the directory specified by secure-file-priv.

2. Disable secure-file-priv. This must be removed from startup and cannot be modified dynamically. So you have to change it in my.conf

In my.con you should find secure-file-priv= and change it to

[mysql]
secure-file-priv='/home/yash/Desktop/'

So that your desktop will become a save directory.

And you can disable the secure file option, which is not recommended by

[mysql]
secure-file-priv=''