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