Learn how to – Solve “MySQL server is running with the –secure-file-priv” Error

Learn how to – Solve “MySQL server is running with the –secure-file-priv” Error.

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 --help

To see the full list, use the command:

$ mysqld --verbose --help

One 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 -p

Then run

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set
Time: 0.023s

You 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.cnf

Set the variable under [mysqld] section

[mysqld]
secure-file-priv=/mysqlfiles

Then 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 mysqld

Login 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 statement

Let’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 mysqld

Confirm 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:

• The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
• SQL – MySQL for Data Analytics and Business Intelligence
• MySQL, SQL and Stored Procedures from Beginner to Advanced
• SQL for Beginners: Learn SQL using MySQL and Database Design
• The Complete MySQL Developer Course
• MySQL Database Administration: Beginner SQL Database Design
• Learn Database Design with MySQL

More:

How To convert all MySQL tables from MyISAM into InnoDB Storage engine

Install Percona MySQL Server 8.0 on Ubuntu / Debian

How To Install MySQL 8.0 on CentOS 8 / RHEL 8