writing mysql output to file in CentOS 7

Question

I am trying to run the following command from inside the mysql command line client in the CentOS terminal:

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')  
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('justatest')  
INTO OUTFILE '/home/user/tmp/truncate.sql';

The result is this error:

ERROR 1 (HY000): Can't create/write to file '/home/user/tmp/truncate.sql' 
(Errcode: 13 - Permission denied)

I then opened a new terminal, typed su -, gave the password, and then typed the following commands:

chown root:root /home/user/tmp
chmod 1777 /home/user/tmp

But I still get the same error when I run the original SQL code at top above.

EDIT:

I also get the same error when I try:

chown username:username /home/user/tmp
chmod 1777 /home/user/tmp

and the same error message when I try:

chown mysql:mysql /home/user/tmp
chmod 1777 /home/user/tmp

How can I resolve this error?

Answer 1

Just an other way to achieve what you're looking for...

Execute the mysql command without the path, just the wanted file name.

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('justatest')  
INTO OUTFILE 'truncate.sql';

This will save your file in the default mysql dir (/var/lib/mysql/database)

Then you can move the file and chage the owner

$ sudo mv /var/lib/mysql/database/truncate.sql /home/user/tmp
$ sudo chown user:user /home/user/tmp/truncate.sql

Answer 2

1. Setup correct rights:

Your path /home/user/tmp must have correct rights on all levels:

/home
/home/user
/home/user/tmp

all these folders must be available for mysql user, not only endpoint tmp directory

this command is not complete:

chown mysql:mysql /home/user/tmp

it adds owner for tmp folder only, it doesn't provide access to home or user

2. check SELinux activity: configure that or disable (if you cannot)

for disable:

edit /etc/selinux/config:

SELINUX=disabled

then restart or execute command

setenforce 0

Answer 3

1. Make sure your sql account have File Priviledges
2. Outfile into temp directory. example 'INTO OUTFILE /tmp/yourfile' Or directrory that mysql has access
3. For download the file within your site, just make symlink that '/tmp' into your public html folder

note : if you cant delete your file because of permission issue, then your filename must be unique.