CODEWITHSUNDEEP
mysqllinuxpermissions
ACV
ACV

Reputation: 10560

mysqldump: Got error: 1: "Can't create/write to file '*.txt' (Errcode: 13 "Permission denied")" when executing 'SELECT INTO OUTFILE'

Linux. No SELinux (disabled). I try to run this command as my linuxuser (member of group mysql):

mysqldump --user=root --tab "/some/folder" dbname

Getting this error:

mysqldump: Got error: 1: "Can't create/write to file 'sometable.txt' (Errcode: 13 "Permission denied")" when executing 'SELECT INTO OUTFILE'

If I look in the folder, there is a sometable.sql file, but no txt file. As I understand, the sql file is created from the user's name who run the command (linuxuser in my case), but the txt file gets generated by mysql server (mysql user in this case). Note that mysql user is also a member of mysql group.

What could be the problem? Tried everything :)

Upvotes: 3

Views: 17342

Answers (4)

Freejack
Freejack

Reputation: 21

This was something I just found and wanted to pass along for the folks searching for the same answer.

This has been occurring on systems running systemd. In the /lib/systemd/system/mariadb.service file is the setting, 'PrivateTmp=true'. This is a security measure to prevent the data from being seen by folks who shouldn't be seeing it.

If you run:

SELECT * TO OUTFILE '/var/tmp/tablename.txt' FROM TABLENAME;

It will create the file but it creates it in a systemd protected directory.

/var/tmp/systemd-private-[hash]-mariadb-[hash]/tmp/tablename.txt

You can either save the data from here which is perfectly acceptable or stop mariadb, create the directory /etc/systemd/system/mariadb.service.d and in that directory create the file 'mariadb.conf' (as an example; any file should do) with the following:

[Service]
PrivateTmp=false

Run systemctl daemon-reload to reload the configuration and restart mariadb.

You should be able to create backups now.

Upvotes: 2

PaulRM
PaulRM

Reputation: 409

If you trying to execute a mysqldump in a client diferent that the server can be a problem I tried with mysqldump Ver 10.14 Distrib 5.5.56-MariaDB, for Linux (x86_64), and works only with the /tmp/ dir. Perhaps, help info say that will not work

  -T, --tab=name      Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

Upvotes: 2

Sergio
Sergio

Reputation: 1

What has worked for me (in Windows), trying to export a file using:

SELECT  * INTO OUTFILE 'C:/documents/dumps' from file
  1. MySQL Service properties --> Log on: take note account defined.
  2. Directory C:\documents\dumps --> properties --> Security: account defined above needs read/write privileges

Upvotes: -1

ACV
ACV

Reputation: 10560

The solution was to change "some/folder" to an external location. Looks like there were permission problems because originally the folder mentioned was under Perforce root and probably it had read-only permissions.

I then changed the new folder permissions to:

mylinuxuser:mysql

making sure that both the user with which the process was started, as well as mysql server have permissions to write there. This is because the sql file is written from user's name and txt file is written from mysql server's name.

I also added my user to the mysql group.

Upvotes: 1

Related Questions