MySQL database provides you with queries to import and export data using the LOAD DATA and SELECT INTO FILE statements.
For example, suppose you have a students table in your database as follows:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | English | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | 6 | male |
| 4 | Joe | English | 8 | male |
| 5 | Sarah | Math | 6 | female |
| 6 | Peter | English | 6 | male |
+----+---------+---------+-------+--------+
When you want to export the table as a text file, you can use the SELECT INTO FILE statement as shown below:
SELECT * FROM students INTO OUTFILE "/tmp/out.txt";
The SQL statement above should produce the file out.txt in the tmp/ directory as a result of the query execution.
However, MySQL frequently throws the following error when you run a data import or export statement:
mysql> SELECT * FROM students INTO OUTFILE "/tmp/out.txt";
ERROR 1290 (HY000): The MySQL server is running with
the --secure-file-priv option so it cannot execute this statement
The --secure-file-priv option is a system variable used by MySQL to limit the ability of the users to export or import data from the database server.
The option is added to your MySQL database as a global variable named secure_file_priv, so you can check the current value of the option using the SHOW VARIABLES statement.
Here’s an example of retrieving the secure_file_priv value:
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
The secure_file_priv option can have three possible values:
NULLvalue means the data export or import is disabled- Empty value means data export or import is enabled
- Directory path value means data export or import is enabled only in the specified path
The secure_file_priv value is a read-only value, so you can’t change it directly using SQL query.
The following statement tries to change the secure_file_priv value to tmp/ folder:
SET GLOBAL secure_file_priv = "/tmp/";
The response would be as follows:
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
To change the value of secure_file_priv variable, you need to create a MySQL configuration file that sets the value of the variable under [mysqld] options.
You need to put the following content in your my.cnf (Mac, Linux) or my.ini (Windows) file:
[mysqld]
secure_file_priv = ""
Once you edit the configuration file, save it and restart your MySQL server. You should be able to import or export data using MySQL LOAD DATA and SELECT INTO FILE statements.
Here’s an example of a successful data export query:
mysql> SELECT * FROM students INTO OUTFILE "/tmp/out.txt";
Query OK, 6 rows affected (0.00 sec)
Now the result of the SELECT statement above is saved as out.txt file.
You can import the same text file back into an SQL table by using the LOAD DATA statement.
First, create a copy of your table using CREATE TABLE ... LIKE statement as shown below:
CREATE TABLE students_copy LIKE students;
Then, load the data from the out.txt file with the following SQL query:
mysql> LOAD DATA INFILE '/tmp/out.txt' INTO TABLE students_copy;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
The query above will put the out.txt data into the students_copy table.
What to do when you have no configuration file
Sometimes, your MySQL installation didn’t come with a config file.
This happens to me when I install the official MySQL community edition for Mac OS, which doesn’t add a default my.cnf file to start the server.
In that case, you should create your own configuration file in a location that will be picked up by MySQL.
MySQL will look in the following locations for a .cnf file:
/etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf~/.my.cnf
Or if you’re using Windows:
C:\Windows\my.iniC:\Windows\my.cnfC:\my.iniC:\my.cnfC:\Program Files\MySQL\MySQL Server x.x\my.iniC:\Program Files\MySQL\MySQL Server x.x\my.cnf
As for me, I created a new ~/.my.cnf file in my Mac machine with the following command:
cd ~
touch .my.cnf
vim .my.cnf
The content of the .my.cnf file is as follows:
[mysqld]
secure_file_priv = ""
Save all changes and exit the Vim session by pressing semicolon : + wq and then hit Enter.
That will be enough to change the value of the secure_file_priv variable on your computer.
And that’s how you fix the --secure-file-priv option error and allow data import/ export using SQL query. Nicely done! 👍
