[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.

root@localhost: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.

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

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

Recovery.

root@localhost: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
root@localhost:mysql3306.sock [qq]>select * from qt;
Empty set (0.01 sec)

root@localhost: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
root@localhost: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

root@localhost: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: