Exporting MySQL table data: "into outfile" fails, directory stays NULL after editing variables in cnf files

Question

I'm pretty new to SQL, MySQL and Docker. I'm running a Docker MySQL container (I believe).

My problem:

In MySQL, the command

select * from [tablename] into outfile [csv-filename];

fails with the message

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

What I've tried:

select @@GLOBAL.secure_file_priv;

outputs a value of NULL.

Then, I changed/added the line

secure_file_priv= "/home/"

into both of the following files within the container:

• /etc/mysql/my.cnf
• /etc/mysql/conf.d/docker.cnf

Still, "select @@GLOBAL.secure_file_priv;" in MySQL returns NULL and "(...) into outfile (...);" fails as described above.

Any clues are much appreciated!

Answer 1

It could be you missed the [mysqld] section for the configuration file however here is a complete example:

$ mkdir conf
$ mkdir home
$ mkdir datadir
$  echo -e '[mysqld]\nsecure_file_priv= "/home/"' | tee conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ cat conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ docker run -v $PWD/conf:/etc/mysql/conf.d \
             -v $PWD/home:/home  \
             -v $PWD/datadir:/var/lib/mysql \
             -e MYSQL_ROOT_PASSWORD=my-secret-pw \
             --name mysql \
             mysql:5.7

Another terminal:

$  docker exec -ti mysql  mysql -pmy-secret-pw -e 'select @@GLOBAL.secure_file_priv'

mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /home/                    |
+---------------------------+