When saving INTO OUTFILE from mysql on a systemd based system, the output file is grabbed by systemd -- why?

Question

I regularly run simple reports from a mysql (actually, mariaDB) database:

MariaDB [acts]> SELECT resolution, title FROM acts ORDER BY resolution INTO OUTFILE '/tmp/acts-titles.txt';

Recently I upgraded the DB server to a linux 3.19 based system using systemd version 218-2, and was surprised to find that the output file does not go directly into the /tmp directory as I suspected but rather into a private systemd folder in /tmp:

[root@www tmp]# pwd
/tmp/systemd-private-3589befe3ce34ca1a67ed5a83bc77b8b-mysqld.service-ylvzCn/tmp
[root@www tmp]# ls
acts-titles.txt
[root@www tmp]# ls -ld /tmp/systemd-private-3589befe3ce34ca1a67ed5a83bc77b8b-mysqld.service-ylvzCn/
drwx------ 3 root root 60 Mar 23 04:32 /tmp/systemd-private-3589befe3ce34ca1a67ed5a83bc77b8b-mysqld.service-ylvzCn/

While this isn't tragic, it is inconvenient, as only the root user can access this directory, thus requiring the mysql administrator to also have root access on the system, which is not necessarily what you want.

Can anyone explain why systemd is grabbing the file, and is there any way to circumvent this behavior?

Answer 1

You can change this behaviour if you edit secure tmp option in /lib/systemd/system/mariadb.service.

systemctl stop mariadb

next edit /lib/systemd/system/mariadb.service as you change PrivateTmp to false:

# Place temp files in a secure directory, not /tmp
PrivateTmp=false  # default is true

and finally:

systemctl daemon-reload
systemctl start mariadb

As noted in comments a better solution is not to make changes in mariadb.service file but chose different directory, give rights so as user running mariadb can access it and point your OUTFILE to that directory.

If PrivateTmp option is enabled this will ensure that the /tmp directory the service will see is private and isolated from the host's system /tmp.
A good point of view you can find here.