Problem description
When I want to export data in mysql, I find an error. The error contents are as follows
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Principle interpretation
secure file priv is a system variable in MySQL that controls the operation of importing and exporting data, such as load data or select... Into output or load_FILE()
Use show global variables like 'secure_File% ' view the set value of this variable, which may have the following three values
- no value. That is, this variable has no effect
2﹥ point to a path. That is, import and export can only use this path
three ⃣ ️ NULL。 Import and export operations are prohibited
The default value of this system variable is different for different platforms
terms of settlement
Environment: MySQL 8.0.26 (installed with homebrew ), macbook pro 2020 Intel Edition
one ⃣ Create corresponding files, ~ /.My.CNF , and use your customary text editor. I use nano , which contains the following contents
[mysqld]
secure_file_priv = ''
two ⃣ Restart the MySQL service and log in
brew services stop mysql
brew services start mysql
mysql -uroot -p
3﹥ check whether the modification is successful in MySQL,
show global variables like 'secure_file%';
# If the modification is successful it should display the following, showing a null value
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
1 row in set (0.00 sec)
Done!
Link:
https://stackoverflow.com/questions/7973927/for-homebrew-mysql-installs-wheres-my-cnf
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv