Solve "MySQL server is running with the --secure-file-priv" Error | Co...
source link: https://computingforgeeks.com/how-to-solve-mysql-server-is-running-with-the-secure-file-priv-error/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
When starting the mysqld server, you can specify program options in the options file or on the command line. These options are meant to unlock other MySQL features, change variables or impose restrictions.
This is how options are read in MySQL server:
- mysqld reads options from the
[mysqld]and[server]groups - mysqld_safe reads options from the
[mysqld],[server],[mysqld_safe], and[safe_mysqld]groups - mysql.server reads options from the
[mysqld]and[mysql.server]groups.
You can see a brief summary of options supported by MySQL using:
$ mysqld --helpTo see the full list, use the command:
$ mysqld --verbose --helpOne of those system variables that can be set at server startup is mysqld_secure-file-priv
What is mysqld_secure-file-priv variable?
The variablesecure_file_priv is used to limit the effect of data import and export operations. Example of the affected operations is those performed by the LOAD DATA andSELECT ... INTO OUTFILE statements and the functionLOAD_FILE(). These operations are permitted only to users who have the FILE privilege.
To see the current setting at runtime, use the SHOW VARIABLES statement.
Login to MySQL shell as root user
$ mysql -u root -pThen run
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set
Time: 0.023sYou can see the directory set is /var/lib/mysql-files/
Changing secure-file-priv variable directory
This value can be changed on MySQL options file under [mysqld] section.
sudo vim /etc/my.cnfSet the variable under [mysqld] section
[mysqld]
secure-file-priv=/mysqlfilesThen create the directory configured
sudo mkdir /mysqlfiles
sudo chown -R mysql:mysql /mysqlfiles/Restart MySQL service for the changes to take effect
sudo systemctl restart mysqldLogin again to confirm new setting
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| secure_file_priv | /mysqlfiles/ |
+------------------+--------------+
1 row in set (0.00 sec)Let’s test to confirm we can export to the specified path.
mysql> SELECT * FROM information_schema.processlist into outfile '/tmp/mysql_processes.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementLet’s try again writing to correct path.
mysql> SELECT * FROM information_schema.processlist into outfile '/mysqlfiles/mysql_processes.txt';
Query OK, 1 row affected (0.00 sec)Diable secure-file-priv variable
To disable it, set the variable to a NULL value.
[mysqld]
secure-file-priv = ""The restart mysqld service
sudo systemctl restart mysqldConfirm after restarting service
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
1 row in set (0.00 sec)Try to save QUERY content to a different path
mysql> SELECT * FROM information_schema.processlist into outfile '/tmp/mysql_processes.txt';
Query OK, 1 row affected (0.00 sec)This was successful. You have learned to configure secure-file-priv variable to fit your use case. Until next time, thanks for using our guide to solve “MySQL server is running with the –secure-file-priv” error when trying to load or save data.
Best Video Courses to Learn MySQL / MariaDB:
More:
How To convert all MySQL tables from MyISAM into InnoDB Storage engine
Recommend
-
18
Solve Error “char cannot be dereferenced” in Java Are you facing the error “char cannot be dereferenced”? So in this article, we’re going to see the main reason behi...
-
10
java-version.com: What's new in Java 16? 15? Keep up to date! How to solve the Angular redirection error (404) using Java
-
13
Solve Error “int cannot be dereferenced” in Java Dereferencing means accessing an object from the heap using a reference variable Or we can say It is a process of accessing the referred value by a reference....
-
11
So in this article, We’ll see how to solve the error “Could not reserve enough space for object heap”. While solving this error you will be also able to solve the following errors which occurs due to the same reasons: Error Occurr...
-
8
"MySQL server has gone away" errorThe MySQL server has gone away error, means that MySQL server (mysqld) timed out and closed the connection. By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens. Howev...
-
9
How can I solv...
-
4
Multiple MySQL server running on same Ubuntu serverThere are many situations where there is a require where you need to run multiple instances of MySQL on same machine. Some situations are: test a new MySQL release wh...
-
11
The MySQL error log is usually used to store and late...
-
2
Featured...
-
8
How to solve "error: externally-managed-environment" when installing via pip3 ...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK