How to export data from MySQL or MariaDB to CSV format

Exporting query results to CSV makes database data easy to share with spreadsheet tools, reporting pipelines, and bulk import workflows. A repeatable export avoids copy/paste mistakes and keeps data transfers consistent across environments.

In MySQL and MariaDB, SELECT … INTO OUTFILE writes the result set directly to a file. Export options control how fields are separated, how values are quoted, how quotes are escaped, and which line ending is used so other tools parse the file as expected.

The output file is created on the database server filesystem by the mysqld service account, not on the client machine running the mysql shell. The database account must have the FILE privilege, the target path must be writable by the server, and settings like secure_file_priv can restrict (or disable) export locations. The target filename must not already exist, and exported files may contain sensitive data that should be stored and cleaned up carefully.

1. Choose a server-side output path for the CSV file.

   INTO OUTFILE writes the file on the database server filesystem, not the machine running the mysql client.

2. Log in to the MySQL or MariaDB server.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 175
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql>

3. Select the database that contains the table.

   mysql> USE appdb;
   Database changed

   Run exports from the intended database to avoid writing the wrong dataset to disk.

4. Review the table columns to confirm the export schema.

   mysql> DESCRIBE orders;
   +-------------+---------------+------+-----+-------------------+-------------------+
   | Field       | Type          | Null | Key | Default           | Extra             |
   +-------------+---------------+------+-----+-------------------+-------------------+
   | id          | int           | NO   | PRI | NULL              | auto_increment    |
   | customer_id | int           | NO   | MUL | NULL              |                   |
   | status      | varchar(20)   | NO   |     | NULL              |                   |
   | total       | decimal(10,2) | NO   |     | NULL              |                   |
   | created_at  | datetime      | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
   +-------------+---------------+------+-----+-------------------+-------------------+
   5 rows in set (0.00 sec)

   Explicit column lists keep exports stable if the table schema changes later.

5. Run the SELECT query to confirm the rows that should be exported.

   mysql> SELECT id, customer_id, total, created_at
       ->   FROM orders
       ->   ORDER BY id
       ->   LIMIT 5;
   +----+-------------+--------+---------------------+
   | id | customer_id | total  | created_at          |
   +----+-------------+--------+---------------------+
   |  1 |           1 | 249.99 | 2025-12-24 13:52:40 |
   |  2 |           2 |  89.50 | 2025-12-24 13:52:40 |
   |  3 |           3 | 149.00 | 2025-12-24 13:52:40 |
   |  4 |           1 | 249.99 | 2025-12-24 13:53:03 |
   |  5 |           2 |  89.50 | 2025-12-24 13:53:03 |
   +----+-------------+--------+---------------------+
   5 rows in set (0.00 sec)

   Use WHERE clauses to keep the export size and contents aligned with the intended downstream use.

6. Check the secure_file_priv setting to confirm the allowed export directory.

   mysql> SHOW VARIABLES LIKE "secure_file_priv";
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   1 row in set (0.02 sec)

   A directory value restricts exports to that directory, an empty value permits any path, and a NULL value disables file exports.

7. Export the query results to a CSV file using INTO OUTFILE.

   mysql> SELECT id, customer_id, total, created_at
       ->   INTO OUTFILE '/var/lib/mysql-files/orders-export.csv'
       ->   FIELDS TERMINATED BY ','
       ->   ENCLOSED BY '"'
       ->   ESCAPED BY '"'
       ->   LINES TERMINATED BY '\n'
       ->   FROM orders
       ->   ORDER BY id;
   Query OK, 9 rows affected (0.00 sec)

   FIELDS TERMINATED BY sets the delimiter, ENCLOSED BY quotes values, and ESCAPED BY set to "" produces doubled quotes for embedded quote characters; use '\r\n' for LINES TERMINATED BY when a Windows-style line ending is required.

   The target file must not already exist (ERROR 1086 (HY000): File '...' already exists), and writing outside the permitted directory can fail with:

   ERROR 1290 (HY000): The MySQL server is running with the %%--secure-file-priv%% option so it cannot execute this statement

8. Preview the first rows of the exported CSV file.

   $ sudo head -n 6 /var/lib/mysql-files/orders-export.csv
   "1","1","249.99","2025-12-24 13:52:40"
   "2","2","89.50","2025-12-24 13:52:40"
   "3","3","149.00","2025-12-24 13:52:40"
   "4","1","249.99","2025-12-24 13:53:03"
   "5","2","89.50","2025-12-24 13:53:03"
   "6","3","149.00","2025-12-24 13:53:03"

9. Verify the exported row count.

   $ sudo wc -l /var/lib/mysql-files/orders-export.csv
   9 /var/lib/mysql-files/orders-export.csv