1

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?

CodeMed
  • 5,079
  • 45
  • 100
  • 147
  • You need to install a LAMP server and then add phpMyAdmin – eyoung100 Dec 01 '14 at 19:29
  • Which user are you logged in as before you login to MySQL? Even if you've chmod'ed 1777 /home/user/tmp that may not mean that the user has permissions to access directories below that. – Chris Davidson Dec 01 '14 at 19:32
  • @eyoung100 Thank you but I do not need the `P` (`PHP` or `Python`). I already have `apache` and `mysql` installed in `CentOS`. Isn't there some way to just change the write permissions via the `command prompt` using without adding more software? – CodeMed Dec 01 '14 at 19:33
  • @CodeMed, what happens if you do `chown mysql:mysql /home/user/tmp` and then try? – Ramesh Dec 01 '14 at 19:35
  • @ChristopherDavidson I log into the machine as username. Then I open the terminal and type `su - ` before typing the above commands. I did try `chown username:username /home/user/tmp` and got the same error. – CodeMed Dec 01 '14 at 19:36
  • @Ramesh Thank you. I tried `chown mysql:mysql /home/user/tmp` but get the same error. – CodeMed Dec 01 '14 at 19:39
  • @CodeMed you miss the point, the php servers as a backend to [PhpMyAdmin - Install Tutorial](http://www.liquidweb.com/kb/how-to-install-and-configure-phpmyadmin-on-centos-7/). After installing this, you can dump your SQL to a CSV file without needing to worry about permissions on `/tmp` – eyoung100 Dec 01 '14 at 19:44
  • @eyoung100 This is a development machine. I don't want any other computers to have access to it. I see that `PhpMyAdmin` will manage the `write` privileges for `mysql`. But isn't there some way to just set the `write` privileges in `CentOS` without having to install everything else that comes with `PhpMyAdmin`? – CodeMed Dec 01 '14 at 19:51
  • A few pointers: 1. Is tmp a valid user? 2. No one can write to a home directory for a) a user that doesn't exist and b) Permissions for `/home` should not be changed. As such, write to a user that exists. – eyoung100 Dec 01 '14 at 19:56
  • @eyoung100 Thank you. `tmp` is not a valid user. But `username` and `root` are both valid users. I do not need to write to `/home`. Is there some other location you can suggest where I can have `mysql` write it's output? – CodeMed Dec 01 '14 at 19:59
  • How about trying your home directory?? But can you login to mysql as your user. – eyoung100 Dec 01 '14 at 20:05
  • 1
    Have you considered SELinux? Try `setenforce 0` as root then try again. – garethTheRed Dec 01 '14 at 20:17
  • @eyoung100 `chown root:root /home` processes without an error, but `chmod 1777 root:root /home` produces the error `chmod: cannot access ‘root:root’: No such file or directory` – CodeMed Dec 01 '14 at 20:20
  • to `chmod` you dont need a user. – eyoung100 Dec 01 '14 at 20:23
  • @garethTheRed `setenforce 0` lets the query run, but it triggers a security alert. – CodeMed Dec 01 '14 at 20:24
  • 1
    Then the error was due to SELinux blocking MySQL writing to your home directory. You should follow the instructions in the alert to enable MySQL to write to your home directory and then re-enable SELinux with `setenforce 1`. Don't leave SELinux disabled as that is a security issue. – garethTheRed Dec 01 '14 at 20:34
  • @garethTheRed I am trying to use your instructions from another question on another site, but they are not working on my remote CentOS 7 server. I posted a question about it at this site. Are you willing to look at it? Here is the link: http://unix.stackexchange.com/questions/171567/installing-fail2ban-on-centos-7?noredirect=1 – CodeMed Dec 05 '14 at 18:26

3 Answers3

2

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
tachomi
  • 7,372
  • 4
  • 25
  • 45
1

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
Eugene Lebedev
  • 157
  • 1
  • 5
1
  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.

fammi farendra
  • 11
  • 1