How to Export a Table from MySQL to CSV

April 3, 2020


Only a limited number of applications read raw MySQL tables. A CSV file is much more likely to work with different database applications. CSV is a standard format for databases in which a comma distinguishes values from different rows and columns. The added benefit of CSV files is that they are human-readable.

This detailed guide will show you how to export a MySQL database to a CSV file.

Guide on how to export a table from MySQL to a CSV file.


  • Access to a command line/terminal window
  • User account with root or sudo privileges
  • A MySQL user account with root privileges
  • Preconfigured phpMyAdmin account (optional)

Export MySQL to CSV with phpMyAdmin

The phpMyAdmin tool provides a graphical interface for managing your MySQL databases. You can use it to export any of its tracked databases to a CSV file.

  1. Start by logging in to phpMyAdmin.
  2. Next, click on the Databases button on the top banner.

Database tab in Myphpadmin tool.

  1. On the list of Databases, click the link to the database you want to export. In this example, we selected the user database.

Select a database that contains the tables for the csv files.

  1. The next screen displays a list of tables in that database. Check the boxes for the tables you want to export.
  2. Click the Export button on the banner.

The location of the export button on myphpadmin interface.

  1. Leave Export method set as-is. Use the Format drop-down menu to select CSV, then click Go.

Select CSV from dropdown and click Go.

  1. A dialog box prompts to specify the location where to want to save the CSV file.

A prompt allowing you to save file in CSV format

Export from MySQL to CSV Using Command Line

You can do a no-frills export by selecting all the data in a table, and specify the location to save it to.

Start by opening the MySQL shell, then switch to the database you want to export.

Enter the following statement:

INTO OUTFILE 'tmpmyExportFile.csv'

Replace myTable with the actual name of the table from your database. You can replace tmpmyExportFile.csv with any other filename or location. Make sure to keep the .csv filename at the end.

Note: This example uses a Linux file location. If you’re working in Windows, you can use c:/folder/file.csv for your file location.

Additional Options for Exporting from MySQL

To specify individual data sets to export from a table:

SELECT column1, column2, column3, column4
FROM myTable
WHERE column2 = 'value';

Replace column1 (and the rest) with the actual names of columns you want to export. Make sure to use the FROM command to specify the table you’re exporting from. The WHERE statement is optional, and it allows exporting only rows that contain a specific value. Replace value with the actual value you want to export. For example:

SELECT order_date, order_number, order_status
FROM current_orders
WHERE order_status='pending';

Note: The WHERE clause can be useful for filtering results.

Export and Timestamp CSV File

Use the following command to export to a CSV file, and add a timestamp for the time the file was created:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = 'tmp';
SET @PREFIX = 'myTable';
SET @EXT    = '.csv';
ESCAPED BY '"'"," 
PREPARE statement FROM @CMD;
EXECUTE statement;

As usual, replace myTable with the actual name of the table you’re exporting.

Note: You may notice the SELECT * FROM command inside the parentheses. We’ve wrapped the command in a function that adds a timestamp.

Export with Column Headers

Use a UNION statement to add column headers to the exported file:

(SELECT 'column1','column2','column3','column4')
(SELECT column1, column2, column3, column4
FROM myTable
INTO OUTFILE 'tmpmyExportFile.csv'

Dealing with NULL Values

If there are any null (empty) values in the database, this process exports them as the letter N. You can replace NULL values with another string of text that makes more sense:

SELECT column1, column2, IFNULL(column3, 'N/A')
FROM myTable INTO OUTFILE 'tmpmyExportFile.csv'

In this case, the IFNULL command looks for empty values in column3. When it finds them, it replaces them with the text string N/A.

Export MySQL to CSV Using mysqldump

You can use the mysqldump application to export your MySQL database to a CSV file. Enter the following into a command prompt:

mysqldump ––tab=/var/lib/mysql–files/ ––fields–enclosed–by='"' ––fields–terminated–by=',' ––lines–terminated–by='n' myTable

This command creates a copy of the database myTable in the /var/lib/mysql-files.

Note: Your user account must have privileges to access the directory the CSV file is saved in. You can save to a different path, but it must be a path MySQL has full access to.

Export MySQL to CSV Using CSV Engine

In some cases, you can use the CSV engine in MySQL to change the table. This method won’t work if a MySQL table has an index, or if the table uses auto_increment. Use the following command:


This statement changes the format of the database to CSV. You can then copy the CSV file to another system.


You now have four ways to export your MySQL database to a CSV file. If you have access to phpMyAdmin, it’s by far the most accessible tool to use.

If you’re working from a command-line interface, the SELECT INTO command is the most reliable method. In certain configurations, the CSV Engine or mysqldump method can also save a lot of time.

Now that you know how to export MySQL database to a CSV file, the next step would be to learn how to import CSV into MySQL database.

Vladimir Kaplarevic
Vladimir is a resident Tech Writer at phoenixNAP. He has more than 7 years of experience in implementing e-commerce and online payment solutions with various global IT services providers. His articles aim to instill a passion for innovative technologies in others by providing practical advice and using an engaging writing style.
Next you should read
How to Import a CSV file into a MySQL database?
June 28, 2020

The article explains how to import a CSV file into a MySQL database using the command line or phpMyAdmin...
Read more
How to Install MySQL on CentOS 8
February 17, 2020

MySQL, the most widely used relational database management system can be installed on CentOS 8 from the...
Read more
How to Create MySQL Database in Workbench
January 29, 2020

Workbench is a cross-platform, open-source, visual tool for database management. It provides a user-friendly...
Read more
How to Improve MySQL Performance With Tuning
January 15, 2020

The performance of MySQL databases is an essential factor in the optimal operation of your server. Make sure...
Read more
  • © 2021 Copyright phoenixNAP | Global IT Services. All Rights Reserved.