How to Export a MariaDB Table to CSV

How do I export a MariaDB table to CSV?

Use SELECT ... INTO OUTFILE or mariadb-dump --tab to write table rows to a comma-separated text file.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why export a MariaDB table to CSV?

CSV is the quickest bridge between MariaDB and spreadsheets, BI tools, or external services. It preserves rows, keeps costs low, and loads instantly.

What is the simplest command?

Run SELECT ... INTO OUTFILE inside MariaDB.The server streams the result set directly to a file on the database host—no client bandwidth required.

How does SELECT … INTO OUTFILE work?

It executes a normal SELECT but redirects rows to a file path defined by secure_file_priv or any writable directory if secure_file_priv is empty.

Which options control CSV formatting?

Use FIELDS TERMINATED BY, ENCLOSED BY, and LINES TERMINATED BY to define separators, quotes, and line endings that downstream tools expect.

Step-by-step example

1. Confirm secure_file_priv with SHOW VARIABLES. 2.Choose a writable path, e.g., /var/lib/mysql-files. 3. Run the example query below. 4. Copy the .csv file to your workstation.

How to export only new rows?

Add a WHERE clause (e.g., WHERE created_at > CURRENT_DATE - INTERVAL 1 DAY).You still use INTO OUTFILE; only qualifying rows get written.

Can I automate recurring exports?

Yes—schedule the statement in a cron job that calls mariadb -e "..." or use the MariaDB Event Scheduler for server-side automation.

What about large tables?

Export in chunks with LIMIT/OFFSET or primary-key ranges to avoid table locks and oversized files.Compress files afterward with gzip to save disk.

Best practices for production

• Grant FILE privilege only to service accounts that need exports.
• Keep exports under /var/lib/mysql-files to respect secure_file_priv.
• Always set ENCLOSED BY '"' to protect embedded commas.

Which alternative tools exist?

mariadb-dump --tab writes a .sql and .txt pair per table; the .txt file is ready-made CSV when you combine --fields-terminated-by and --fields-enclosed-by parameters.

Troubleshooting: empty file created?

Check that the SELECT actually returns rows and that the server user owns the target directory.Review error log entries for file-path denials.

Why How to Export a MariaDB Table to CSV is important

How to Export a MariaDB Table to CSV Example Usage


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

How to Export a MariaDB Table to CSV Syntax


SELECT <column_list>
FROM <database>.<table>
INTO OUTFILE '/absolute/path/filename.csv'
FIELDS TERMINATED BY ','          -- delimiter
ENCLOSED BY '"'                   -- quote char
LINES TERMINATED BY '\n'          -- line ending
[OPTIONALLY] ESCAPED BY '\\'      -- escape char
[WHERE <condition>];

Common Mistakes

Using a relative path: MariaDB requires an absolute path inside INTO OUTFILE. Fix by supplying /full/path/filename.csv.
Forgetting FILE privilege: The user must have FILE on *.*; otherwise MariaDB denies the export. Grant with GRANT FILE ON *.* TO 'user'@'host';

Frequently Asked Questions (FAQs)

Does INTO OUTFILE overwrite existing files?

No. It fails with “File exists” to prevent accidental data loss. Delete or rename the old file first.

How do I export multiple tables at once?

Run a separate SELECT ... INTO OUTFILE for each table or use mariadb-dump --tab to dump every table in a schema.

Can I run the command from my laptop?

Yes. Use the mariadb client: mariadb -h db_host -u user -p -e "SELECT ... INTO OUTFILE ...". The file is still created on the server, so copy it via scp.