Where does MariaDB save output from SELECT INTO OUTFILE on CentOS 8?
Published Feb 1, 2020 • 3 min read • 0 commentsTags: linux centos mariadb

SELECT INTO OUTFILE Description
SELECT INTO OUTFILE is used to export query results to file and can be formatted using additional export options. By default, columns are separated by tabs ( \t ) while rows are separated by newlines ( \n ).
Syntax
From the MariaDB documentation, https://mariadb.com/kb/en/select-into-outfile/
SELECT ... INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
[export_options]
export_options:
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
Where are the files saved?
Using a relative path
When using a relative path, the file will not be saved in your pwd, rather in the MariaDB data directory. Assuming your database name is app, your file will be saved in /var/lib/mysql/app/.
MariaDB [app]> SELECT * INTO OUTFILE 'users.csv'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM app.users;
Query OK
[root@app]# ls /var/lib/mysql/app/
db.opt users.csv users.frm users.ibd
Using an absolute path
You may expect using an absolute path to write to a specific directory, however MariaDB running under systemd will create the directory path and file in its own directory defined in /usr/lib/tmpfiles.d/systemd-tmp.conf.
MariaDB [app]> SELECT * INTO OUTFILE '/tmp/users.csv'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM app.users;
Query OK
You can find the file in a directory similar to the one below:
[root@app]# ls /tmp/systemd-private-1343867e83364df4883c1398d281798e-mariadb.service-2WzN6u/tmp/
users.csv
You can change the default behavior by updating the mariadb.service unit file, or better, adding an included configuration so changes are not overwritten during package upgrades.
File: /etc/systemd/system/mariadb.service.d/private-tmp.conf
[Service]
PrivateTmp=false
Reload configuration and restart MariaDB
sudo systemctl daemon-reload
sudo systemctl restart mariadb
Now absolute file paths should be saved to the expected locations.
MariaDB [app]> SELECT * INTO OUTFILE '/tmp/users.csv'
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM app.users;
Query OK
[root@app]# ls /tmp/
users.csv