can't set secure_file_priv on mysql 5.7 Ubuntu 16.04

Question

How do I set a value for secure_file_priv?

I found this which tells what settings may be used https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

The mysql server starts without any command line options. There is nothing to override its' .cnf file.

user@server:~$ ps aux | grep [m]ysql
mysql     4495  0.0  7.0 544368 144924 ?       Ssl  09:16   0:02 /usr/sbin/mysqld

Running :~$ mysqld --verbose --help tells me

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Only the 2nd file exists. It is the beginning of a symlink chain to /etc/mysql/my.cnf.migrated as follows...

user@server:~$ ls -l /etc/mysql/my.cnf
lrwxrwxrwx 1 root root 24 Aug 12 15:15 /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
user@server:~$ ls -l /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 26 Aug 12 15:15 /etc/alternatives/my.cnf -> /etc/mysql/my.cnf.migrated
user@server:~$ ls -l /etc/mysql/my.cnf.migrated
-rw-r--r-- 1 root root 4455 Dec 13 03:18 /etc/mysql/my.cnf.migrated

I've tried setting values for secure_file_priv in that last file, restarting the mysql server and even rebooting the Ubuntu server. No matter the value that is set the command

mysql> SELECT @@GLOBAL.secure_file_priv;

always returns /var/lib/mysql-files/.

I've also searched for other .cnf files and tried setting the value for secure_file_priv in each of them

user@server:~$ find /etc -iname "m*.cn*" -type f
/etc/mysql/conf.d/mysql.cnf
/etc/mysql/my.cnf.migrated
/etc/mysql/my.cnf.fallback
/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/mysql.cnf

No matter. After making a change, restarting the server, and checking the value with

mysql> SELECT @@GLOBAL.secure_file_priv;

the result /var/lib/mysql-files/ is always the same. It doesn't change.

What do I need to do to set a value for secure_file_priv?

Answer 1

Add the variable under the [mysqld] section of /etc/mysql/my.cnf:

secure_file_priv=/absolute/path

Then restart mysql service afterwards.

Check with SELECT @@secure_file_priv;

It worked for me:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)

To avoid getting ERROR 1290 eg:

 mysql [localhost] {msandbox} (test) > select * from t1 into outfile 'file.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Make sure you use absolute paths like this:

mysql [localhost] {msandbox} (test) > select * from t1 into outfile '/tmp/file.sql';
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > \! ls /tmp/file.sql
/tmp/file.sql

mysql [localhost] {msandbox} (test) > truncate t1;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > load data infile '/tmp/file.sql' into table t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0