How to Export a MySQL Table to CSV

Four reliable ways to export a MySQL table to CSV: SELECT INTO OUTFILE (server-side, requires the FILE privilege), mysqldump --tab (server-side, dumps schema + data side by side), the mysql client with a piped redirect (no special privilege needed, works from any host you can reach), and a shell-side one-liner that handles the comma-and-quote escaping when the dataset has commas inside fields. Pick based on where you have credentials and whether you have file-system access on the server.

The example table for this walkthrough is application_logs, a typical structured-log capture table from a web service: request id, route, status code, timestamp, and a JSON payload column. The export patterns work the same for any table — just substitute your column list — but a real log table is what most readers actually need to export, so it is what the examples assume.

CREATE TABLE application_logs (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  request_id    VARCHAR(36)  NOT NULL,
  route         VARCHAR(255) NOT NULL,
  status_code   SMALLINT     NOT NULL,
  occurred_at   DATETIME(6)  NOT NULL,
  duration_ms   INT          NOT NULL,
  payload       JSON         NULL
);

Jump to:

• How do I export a MySQL table to CSV?
• Method 1: SELECT INTO OUTFILE (server-side, FILE privilege)
• Method 2: mysqldump --tab (schema + data)
• Method 3: mysql client piped to a file (no special privilege)
• Method 4: shell-side escaping for commas and quotes
• Character encoding and UTF-8 BOM
• Including a header row
• FAQ

How do I export a MySQL table to CSV?

The reliable cross-environment path on MySQL 8.0 and 8.4 is mysql -B -e 'SELECT ...' | sed (or piped through Python's csv module for tables with embedded commas and quotes). It does not need the FILE privilege, runs against any database you can reach (including managed RDS, Aurora, and Cloud SQL where SELECT INTO OUTFILE is disabled), and writes the file on your local disk where you can grab it. The one-liner is mysql -u root -p mydb -B -e "SELECT * FROM application_logs" | sed 's/\t/,/g' > logs.csv. For exports that ship with the schema attached (so the table can be recreated on a different server) use mysqldump --tab=/var/lib/mysql-files/ --fields-terminated-by=',' --fields-optionally-enclosed-by='"' mydb application_logs, which writes both a .sql schema file and a .txt CSV-formatted data file side by side.

Method 1: SELECT INTO OUTFILE (server-side, FILE privilege)

The native MySQL export. Writes a file on the server's filesystem (not your client's), so the path has to be writable by the mysqld process and the user has to have the FILE privilege.

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

Three things to read out of the syntax:

• FIELDS TERMINATED BY ',' is what makes it CSV. Set it to '\t' for tab-separated, '|' for pipe-delimited, and so on.
• OPTIONALLY ENCLOSED BY '"' wraps string fields in double quotes only when they contain a comma, quote, or newline. This is what makes the output readable by Excel and Google Sheets without breaking on commas inside the data.
• The output path must be inside secure_file_priv. On modern MySQL installs the default secure_file_priv is /var/lib/mysql-files/. Trying to write anywhere else fails with "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement". Check the setting with SHOW VARIABLES LIKE 'secure_file_priv';.

On managed databases (AWS RDS, Google Cloud SQL, Aurora) FILE privilege is not granted to user accounts, so this method does not work there. Use Method 3 instead.

Method 2: mysqldump --tab (schema + data)

mysqldump --tab produces two files per table: a .sql with the CREATE TABLE statement and a .txt with the data in tab-separated format (which you can convert to CSV trivially).

mysqldump -u root -p \
--tab=/var/lib/mysql-files/ \
--fields-terminated-by=',' \
--fields-optionally-enclosed-by='"' \
--lines-terminated-by='\n' \
myapp application_logs

# Outputs:
#   /var/lib/mysql-files/application_logs.sql   (schema)
#   /var/lib/mysql-files/application_logs.txt   (data, CSV-formatted)

# Optional: rename the data file to .csv
sudo mv /var/lib/mysql-files/application_logs.txt /var/lib/mysql-files/application_logs.csv

This is the best option when you want both the schema and the data, because the two files together reproduce the table on another server with a mysql < schema.sql followed by a LOAD DATA INFILE of the CSV. It has the same FILE privilege requirement as Method 1.

Method 3: mysql client piped to a file (no special privilege)

When you do not have shell access to the database server or the FILE privilege, run a query through the mysql client and pipe the output to a file on the client side. No special privilege needed.

mysql -u root -p \
--batch \
--execute "SELECT * FROM myapp.application_logs" \
| sed 's/\t/","/g; s/^/"/; s/$/"/; s/\n//g' \
> /tmp/application_logs.csv

--batch mode produces tab-separated output without the box-drawn table format. The sed pipeline wraps every field in double quotes and replaces tabs with ",", producing CSV that handles embedded commas correctly.

This is the method to use on managed databases. It works against AWS RDS, Google Cloud SQL, Aurora, Azure Database for MySQL, and DigitalOcean Managed Databases without any privilege grants.

The downside: the entire result set streams through the mysql client, so very large tables take longer than Method 1 (which writes directly from the server to a local file). For tables under a few hundred million rows this overhead is fine; for billion-row tables, prefer running it on a read replica.

Method 4: shell-side escaping for commas and quotes

The sed pipeline in Method 3 handles tabs and quotes around fields, but it does not escape existing double-quotes inside the data. For tables with JSON or free-form text columns, a more robust approach uses the mysql client's CSV output options directly, but they only exist in some forks.

mysql -u root -p myapp -B -N -e "SELECT * FROM application_logs" \
| python3 -c '
import csv, sys
w = csv.writer(sys.stdout)
for line in sys.stdin:
  w.writerow(line.rstrip("\n").split("\t"))
' > /tmp/application_logs.csv

Python's csv module handles all the edge cases: embedded double-quotes get doubled (" becomes ""), fields with commas get wrapped in quotes, and embedded newlines are preserved properly. This is the safest pipeline when the table has any column that might contain user input.

-B is shorthand for --batch, -N is --skip-column-names (so the output is data-only).

Comparison: which export method to pick

Character encoding and UTF-8 BOM

Always export as UTF-8. Excel on Windows still defaults to opening CSV files as the local 8-bit encoding (Windows-1252 in the US, Shift-JIS in Japan), which mangles any non-ASCII character. The fix is to write a UTF-8 byte-order-mark (BOM) at the very start of the file:

# Prepend the UTF-8 BOM so Excel opens the file correctly
printf '\xef\xbb\xbf' > /tmp/application_logs-excel.csv
cat /tmp/application_logs.csv >> /tmp/application_logs-excel.csv

Numbers in Excel are unaffected by the BOM; only character-encoding interpretation changes. Modern versions of Numbers (macOS) and Google Sheets handle UTF-8 correctly without the BOM, so this trick is only needed for Windows Excel.

Including a header row

SELECT INTO OUTFILE does not emit a header row. To get one, UNION the column names as a first row:

SELECT 'id','request_id','route','status_code','occurred_at','duration_ms','payload'
UNION ALL
SELECT id, request_id, route, status_code, occurred_at, duration_ms, payload
FROM application_logs
INTO OUTFILE '/var/lib/mysql-files/logs.csv'
FIELDS TERMINATED BY ','
     OPTIONALLY ENCLOSED BY '"'
LINES  TERMINATED BY '\n';

The header row is a literal SELECT of string constants that produces one row with your column names, then UNION ALL adds the actual data underneath. The output file starts with the header line and proceeds normally.

For Method 3 (mysql client), --batch already includes a header line by default. Add -N (or --skip-column-names) to suppress it when you do not want one.

What to do next

The natural counterpart to exporting is importing on another machine or back into a different database. How to Import a CSV File Into MySQL walks through LOAD DATA INFILE, the LOCAL flag for client-side files, the parallel mysqlsh util.importTable() loader, character-encoding handling, and the field-terminator gotchas that show up when the export was done with non-default separators.

For a full database backup that captures everything (schema, data, stored routines, triggers, and events) in one file rather than a single-table CSV, How to Export or Back Up All MySQL Databases covers the mysqldump --all-databases --single-transaction --routines --events pattern that is the right shape for nightly backups.

Once the export is in another database, you usually want to confirm the row sets line up. How to Find Rows in One MySQL Table Not in Another covers the anti-join check; How to JOIN Two MySQL Tables is the foundation for cross-table verification. If the export reveals duplicate rows that should not exist, How to Find Duplicate Rows in MySQL and How to Delete Duplicate Rows in MySQL are the cleanup walkthroughs.

For schema work around the export (adding a column to capture an exported value, changing a column type to match a downstream system), How to Add a Column to a MySQL Table, How to Change a MySQL Column Type, and the MySQL Field Types and Sizes reference are the matching writeups. If exported DATETIME values need formatting for a downstream consumer, How to Format a MySQL DATETIME in a SELECT is the lookup. For shell-driven scheduled exports, Bash for loop and Bash while loop are the wrappers.

For the wider reference covering mysqldump, restore, and the rest of the operational SQL syntax, the MySQL Cheat Sheet keeps the common commands in one searchable page.

External reference: official documentation on SELECT INTO OUTFILE syntax and mysqldump --tab options.

FAQ