[Solved] MYSQL ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot e…

Today, when I was learning MySQL, I wanted to import the data from the text file into the database, but I found that I kept getting errors, and I changed the path to import the text and still got the same error, the error showed ERROR 1290 (HY000): The MySQL server is running with the –secure- file-priv option so it cannot execute this statement.

select ….. .into outfile is a logical backup method that can be restored very fast, faster than inserting. It only backs up the data in the table and does not include the structure of the table.

The reason for the error is that after MySQL 5.7.6, the import file can only be in the folder specified by secure_file_priv (also because of insufficient permissions)

Method 1.

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

This will place the imported files in the /var/lib/mysql-files/ folder, and then import them from there.

When you export the files, you will also export the files to this folder.

[email protected]:mysql3306.sock [(none)]>show global variables like ‘%secure%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /data/mysql/mysql3306/tmp/ |

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

[email protected]:mysql3306.sock [(none)]>select * from qt into outfile ‘/data/mysql/mysql3306/tmp/qt.sql’;
ERROR 1046 (3D000): No database selected
[email protected]:mysql3306.sock [(none)]>select * from qq.qt into outfile ‘/data/mysql/mysql3306/tmp/qt.sql’;
Query OK, 8 rows affected (0.01 sec)

[[email protected] ~]# cd /data/mysql/mysql3306/tmp/
[[email protected] tmp]# ll
total 4
-rw-rw-rw- 1 mysql mysql 56 Aug 13 06:06 qt.sql
[[email protected] tmp]# pwd
/data/mysql/mysql3306/tmp

Recovery.

[email protected]:mysql3306.sock [(none)]>use qq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[email protected]:mysql3306.sock [qq]>select * from qt;
Empty set (0.01 sec)

[email protected]:mysql3306.sock [qq]>LOAD DATA INFILE ‘/data/mysql/mysql3306/tmp/qt.sql’ into table qq qt;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘qt’ at line 1
[email protected]:mysql3306.sock [qq]>LOAD DATA INFILE ‘/data/mysql/mysql3306/tmp/qt.sql’ into table qq.qt;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

[email protected]:mysql3306.sock [qq]>

If it shows ERROR 1261 (01000): Row 1 doesn’t contain data for all columns
This error is because the data rows do not match and cannot be empty by default, use the following command to solve set sql_modul = 0;

Similar Posts: