Where does MariaDB save output from SELECT INTO OUTFILE on CentOS 8?

Published Feb 1, 20203 min read 0 comments

Tags: 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
More Articles
Install MySQL Community Server on CentOS 8
Install MySQL Community Server on CentOS 8
Jan 14, 2020
How to enable HAProxy logs on CentOS 8
How to enable HAProxy logs on CentOS 8
Jan 6, 2020
Copyright © 2017-2023   Mark Brody | Blog