Full table select:


SELECT * INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table`;


Variations(for instance select from derived table):

SELECT tbl.* INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM (
SELECT t1.category_name, t2.full_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.category_id = t2.category_id
) AS tbl;


To add a heading row into the exported by MySQL CSV file you can use UNION statement:

SELECT 'Column Name 1', 'Column Name 2'
UNION
(
SELECT tbl.column1, tbl.column2 INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table1 tbl
);


Note: /tmp/file.csv must writable by mysql process