Export a MySQL/MariaDB SELECT into a CSV file

This is not a new question, but I think it is interesting to know how to export a SELECT statement into a CSV formatted file. In this example, I am going to export a FusionBPX CDR format.

SELECT domain_name, direction, caller_id_name, caller_id_number, destination_number, start_stamp, end_stamp, billsec, hangup_cause
        INTO OUTFILE 'amfs.csv'
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        ESCAPED BY '\\'
        LINES TERMINATED BY '\n'
        FROM v_xml_cdr
        WHERE domain_uuid='f4abf9c1-842f-4408-b923-dd0c94ae86da'
                AND start_stamp >= '2015-03-01'
        ORDER BY start_stamp;

 This query exports only selected columns into a file called amfs.csv with the following characteristics:

• fields are delimited by a comma,
• non-numeric fields are enclosed by double quotes "
• if there is a need to escape a character, it will be used the slash \

Now, after executing this SELECT, your next question is to know where the file is. In my case, it was in the /var/lib/mysql/fusionpbx/amfs.csv path, which is the directory where the database is stored.

This file does not contain the fields names in the first row. You can edit the file with any file editor and add them. Take them from the SELECT statement, as they are. This is an optional step.

After that, you can use any software such as Microsoft Excel or LibreOffice to open it.

Good Luck!