Solution to secure-file-priv problem when exporting MySQL data

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

I believe that most students who perform MySQL data export operations for the first time will encounter the above errors. As for why this error occurs, the reason is very simple: we don't know where the default export directory of MySQL is, and we don't even know that MySQL has restrictions on the export directory of data. This is actually normal, because it is impossible for us to know everything. As long as we can find the correct solution and remember it after encountering a problem, it is enough! Learning is a process of continuous accumulation.

Next, let's take a look at how to solve this problem: After we install MySQL, a configuration file named my.ini will be automatically generated. This file records all the default configurations of MySQL, such as port number, default character set, default storage engine, and so on. This configuration file is located in the hidden folder ProgramData. For details on how to find this hidden folder, please refer to the detailed method for viewing the storage location of MySQL data files.

In addition, we can also view the current value of secure-file-priv through the command:

show variables like '%secure%';

Execute the above command to test:

As shown in the figure above, the secure-file-priv value I set is D:/CoderLife/testMySQL, and the default value of MySQL is:

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

We can view and modify it by opening the my.ini configuration file:

As shown in the figure above, I commented out the default value of MySQL and changed it to my own specified path. In fact, after we modify the contents of this configuration file, such as changing the default value of secure-file-priv, we must shut down MySQL and restart it to make it take effect. Here, we need to pay attention to the following: shutting down MySQL does not mean simply exiting MySQL with the exit command or closing the MySQL window, but shutting down the MySQL service and then restarting the service . As for how to start and stop the MySQL service, please refer to the detailed description of the MySQL database crash after entering the password and the solution.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.