ERROR 1290 (HY000): The MySQL server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement

Today, when studying MySQL, want to import text file data into the database, but found that the error, the path of the imported text is still the same mistake, error 1290 (HY000): The MySQL server is running with the-- Secure-file-priv option So it cannot the execute this statement.

Then find a solution on the Internet, find the method is not very useful under Linux, and finally found a solution to the Linux MySQL file import error method

The reason for the error is that after the MySQL 5.7.6 version, the import file can only be in the folder specified by Secure_file_priv (also due to insufficient permissions)

Method One:

We can use show variables like '%secure% '; command to display the file directory

 

This will put the import file under the/var/lib/mysql-files/folder, and then import it from here.

When exporting a file, the file is also exported to this folder.

Load data infile '/var/lib/mysql-files/part.csv ' into table part fields terminated by ', ' optionally enclosed by ' "' Esca PED by ' "' lines terminated by ' \ r \ n ';

If error 1261 (01000) is displayed: Row 1 doesn ' t contain data for all columns
This error, because the data row does not match, the default can not be empty, with the following command to resolve set Sql_modul = 0;

The following two methods are mainly for the Windows under mysql,linux MySQL My.ini file is not good to find

Method Two: 

Modify the My.ini under the MySQL installation path, and at the end add the line "secure-file-priv= path name" to restart MySQL

You will be able to do this by placing the file on the load data infile in the path defined above.

Method Three: 

1. Stopped the MySQL service.

2. In this location similar to C:\ProgramData\MySQL\MySQL Server 5.6\my.ini, locate the INI file. Duplicate a copy as a backup.

3. edit this file. Delete the similar line inside of secure-file-priv= "C:/programdata/mysql/mysql Server 5.6/uploads".

4. Start the MySQL service.

When doing the MySQL bulk export data, the problems encountered are as follows:

ERROR 1290 (HY000): The MySQL server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement

MySQL default to the exported directory has permission restrictions, that is, when using the command line to export, you need to specify the directory to operate;

Workaround:

1. Query the MySQL secure_file_priv value configuration, using the command line: show global variables like '%secure% ';

(My configuration here is Secure_file_priv =/var/lib/mysql-files/)

2. Start the export with into outfile: (Because of the SECURE_FILE_PRIV configuration, it must be exported to the/var/lib/mysql-files/directory)

SELECT * FROM table_name where a = ' test '
into outfile '/var/lib/mysql-files/test.txt ' fields TERMINATED by ', ' optionally enclosed by ' "' LINES TERMINATED by ' \ n ' ;

3. A few keywords used:

Fields TERMINATED by ', ' optionally enclosed by ' "' LINES TERMINATED by ' \ n ';

(because the exported data will appear some garbled or special characters, so use the above keyword to escape)

ERROR 1290 (HY000): The MySQL server is running with the--SECURE-FILE-PRIV option so it cannot execute this statement