How to Export a MySQL Table to CSV

How do I export a MySQL table to CSV quickly?

The fastest server-side method is SELECT ... INTO OUTFILE. It streams the result set straight to a file on the MySQL host, avoiding client round-trips.

What is the simplest SELECT ... INTO OUTFILE syntax?

Run a normal SELECT with INTO OUTFILE, then define field and line delimiters:

SELECT *
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Orders;

MySQL must have FILE privilege and write access to the target directory.

How do I change field separators and enclosures?

Use FIELDS TERMINATED BY, ENCLOSED BY, and ESCAPED BY to match downstream requirements. Example: TAB-separated with no quotes.

... FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '' ...

Can I export only specific columns or rows?

Yes. List needed columns and add WHERE:

SELECT id, customer_id, total_amount
INTO OUTFILE '/var/lib/mysql-files/big_orders.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM Orders
WHERE total_amount > 500;

How do I export when I lack server file-system access?

Run the query from the mysql client and redirect STDOUT locally:

mysql -u user -p -h prod \
-e "SELECT * FROM Orders" ecommerce \
--batch --silent > orders.csv

--batch produces tab-separated output; pipe through sed to replace tabs with commas if needed.

How can I export with column headers?

UNION a header row above the data:

(SELECT 'id','customer_id','order_date','total_amount')
UNION ALL
SELECT id, customer_id, order_date, total_amount
FROM Orders
INTO OUTFILE '/var/lib/mysql-files/orders_header.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Best practices for secure exports

Create a dedicated EXPORT_ROLE with only SELECT and FILE privileges. Write exports to /var/lib/mysql-files, then move files via a privileged script to the final destination.