MySQL Export – ‘into outfile’ Fails, Directory Stays NULL

dockerMySQL

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!

Best Answer

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/                    |
+---------------------------+

Related Solutions

MySQL – How to Copy a Table Between Servers

I tend to shy away from the FEDERATED storage engine for three(3) reasons:

It only supports MyISAM
DDL performed on the source table requires manually changing the FEDERATED table design on external services.
Bulk operations against a FEDERATED table can become an instant mightmare !!!

What to do ???

One possibility is to use mysqldump and merge them

EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB

On the server that will combine the three tables, create four(4) import databases (one time operation):

CREATE DATABASE ImportDB1;
CREATE DATABASE ImportDB2;
CREATE DATABASE ImportDB3;
CREATE DATABASE ImportCombined;

Import Process Goes Something Like This

mysqldump the three(3) tables

mysqldump -hHOSTIP1 -Pportnumber1 -u... -p... dbname tbname > /root/ImportTable1.sql &
mysqldump -hHOSTIP2 -Pportnumber2 -u... -p... dbname tbname > /root/ImportTable2.sql &
mysqldump -hHOSTIP3 -Pportnumber3 -u... -p... dbname tbname > /root/ImportTable3.sql &
wait

Load the mysqldumps into the separate import databases in parallel:

mysql -hHOSTIP4 -u... -p... -A -DImportDB1 < /root/ImportTable1.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB2 < /root/ImportTable2.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB3 < /root/ImportTable3.sql &
wait
rm -f /root/ImportTable1.sql &
rm -f /root/ImportTable2.sql &
rm -f /root/ImportTable3.sql &
wait

Next, combine the data.

DROP TABLE IF EXISTS ImportCombined.tbname;
CREATE TABLE ImportCombined.tbname LIKE ImportDB1.tbname;
ALTER TABLE ImportCombined.tbname DISABLE KEYS;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB1.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB2.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB3.tbname;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;

Now, perform the task you already have in place against the ImportCombined.tbname table.

"You fill in these steps"

Then, when the table is ready to be exported, mysqldump it and copy 2 times:

mysqldump -hHOSTIP4 -Pportnumber4 -u... -p... ImportCombined tbname > /root/ExportTable1.sql
cp /root/ExportTable1.sql /root/ExportTable2.sql &
cp /root/ExportTable1.sql /root/ExportTable3.sql &
mysql -hHOSTIP1 -Pportnumber1 -u... -p... -A -Ddbname < /root/ExportTable1.sql &
mysql -hHOSTIP2 -Pportnumber2 -u... -p... -A -Ddbname < /root/ExportTable2.sql &
mysql -hHOSTIP3 -Pportnumber3 -u... -p... -A -Ddbname < /root/ExportTable3.sql &
wait
rm -f /root/ExportTable[123].sql &

As show from these steps, these things should be shell scripted and perhaps crontab'd.

Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.

Mysql – how to use a different tmp dir for INTO OUTFILE select with MySQL

Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'

The other option, if you have access to the command line, is to use the -e switch:

> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'