How to Export MySQL Data to CSV

July 18, 2024

Introduction

Not many applications read raw MySQL tables. On the other hand, a CSV file is versatile and functions with different database software and programs.

CSV is a standard database format in which a comma separates values between rows and columns. The added benefit of CSV files is that they are human-readable.

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

How to Export MySQL Data to CSV

Prerequisites

Note: To install phpMyAdmin, use one of our guides for the following operating systems:

Export MySQL to CSV with phpMyAdmin

phpMyAdmin provides a GUI for managing MySQL databases. It contains various administration functionalities, including exporting databases to CSV. Follow the steps below:

1. Log in to phpMyAdmin using your credentials.

phpMyAdmin login page

2. Click the Databases tab on the top banner.

phpMyAdmin Databases tab

3. Click the database name.

phpMyAdmin databases list

4. The following screen shows the tables in that database. Check the boxes next to the tables you want to export.

phpMyAdmin database tables list checkbox

Alternatively, check all the tables to export the whole database.

5. Expand the With selected drop-down menu and choose Export.

phpMyAdmin database table export

6. Open the Format drop-down menu, select CSV, and then click Go.

phpMyAdmin CSV export

The file is saved to the downloads location of the browser (typically the Downloads directory).

Export MySQL to CSV via Command Line

Another way to export to a CSV file is through a MySQL statement. Follow the steps below:

1. Open the MySQL shell in the terminal:

mysql -u [user] -p
MySQL shell prompt terminal output

Enter the user's password when prompted. The prompt changes to the MySQL shell.

2. Switch to the desired database:

USE [database_name];
USE mydatabase; MySQL output

3. See the following example to export data to a CSV file:

SELECT * FROM [table_name]
INTO OUTFILE '[path].csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
SELECT statement export CSV output

Replace [table_name] with the table's actual name. Provide the desired location ([path]) and file name, ensuring it ends with .csv.

4. If the previous statement shows an error, the server is set up to limit the directory where files can be loaded.

error 1290 MySQL output

Use the following statement to see which directory is set as the default:

SHOW VARIABLES LIKE "secure_file_priv";
secure_file_priv MySQL location output

Use the output path and rerun the export statement from the previous step.

Note: Alternatively, adjust the value in the MySQL config file to change the location. Set the option to an empty string to allow any location.

Export and Timestamp CSV File

Follow the steps below to add a timestamp to the file name during export:

1. Switch to the desired database:

USE [database_name];

2. Set the timestamp and directory path in variables:

SET @TS = DATE_FORMAT(NOW(), '%Y_%m_%d_%H_%i_%s');
SET @FILENAME = CONCAT('[path]', @TS, '.csv');
set timestamp and filename variables MySQL

The variables contain the following:

  • SET @TS. Sets the timestamp in the %Y_%m_%d_%H_%i_%s format using the DATE_FORMAT function. Adjust the timestamp if a different format is required.
  • SET @FILENAME. Concatenates the directory path ([path]), the timestamp (@TS), and the .csv file extension. The path can contain a filename prefix. Ensure that the MySQL server has permission to load files to the location (use SHOW VARIABLES LIKE "secure_file_priv"; to check).

3. Construct the SQL query:

SET @SQL = CONCAT(
    'SELECT * FROM [table_name] INTO OUTFILE \'', @FILENAME, '\' ',
    'FIELDS ENCLOSED BY \'"\' ',
    'TERMINATED BY \',\' ',
    'ESCAPED BY \'"\' ',
    'LINES TERMINATED BY \'\\r\\n\' '
);
set SQL variable CONCAT MySQL

The statement creates a variable that concatenates the previously generated file name with the SQL query to select a table.

4. Prepare the SQL statement:

PREPARE stmt FROM @SQL;
prepare statement MySQL

5. Execute the statement:

EXECUTE stmt;
execute statement MySQL

The CSV file appears in the previously provided location.

MySQL database to CSV timestamp file

6. Deallocate the statement:

DEALLOCATE PREPARE stmt;
DEALLOCAE PREPARE statement MySQL

The prepared statement is now empty.

Export with Column Headers

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

SELECT '[column_1]', '[column_2]', '[column_3]'
UNION ALL
SELECT [column_1], [column_2], [column_3]
FROM [table_name]
INTO OUTFILE '[path].csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
MySQL statement include headers output

The query uses the UNION ALL statement to combine two SELECT statements. The first statement creates the column headers, and the second selects the table data.

CSV file with table headers output

The exported CSV file contains the column names in the first row.

Dealing with NULL Values

If there are null (empty) values in the database, this process exports them as \N. The following example shows how to replace the null values with another string:

SELECT [column_1], [column_2], IFNULL([column_3], 'N/A')
FROM [table_name] INTO OUTFILE '[path].csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n';
SELECT IFNULLCSV export output

In this case, the IFNULL function checks for empty values in the provided column.

MySQL database to CSV N/A field output

If found, it replaces them with the text string N/A.

Export MySQL to CSV Using mysqldump

The mysqldump CLI tool helps export a MySQL database to a CSV file. Enter the following command into a command prompt using your information:

sudo mysqldump -u [username] -p [database_name] --tab=[path] --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\n' [table_name]
mysqldump database to CSV output

The command prompts for the MySQL user password before execution. It creates two files: a .sql file with the database schema and a .txt file in CSV format.

mysqldump two files output

To change the file extension, use the mv command:

sudo mv [path].txt [path].csv

The mv command can also rename the file in the same step or move it to another location.

Export MySQL to CSV Using CSV Engine

Another way to export to CSV is to change a table's storage engine. However, this method won't work if a MySQL table has an index, contains null values, or uses auto-increment (in those cases, use one of the other methods).

To change a table's storage engine, run:

ALTER TABLE [table_name] ENGINE=CSV;
ALTER TABLE ENGINE=CSV; output

This statement changes the storage engine to CSV and converts the MySQL database file to CSV. To find the file's location, run:

SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'datadir'; MySQL output

The MySQL server stores all database files in the directory shown in the output. To see the file, use the ls command and add the database name to the path.

MySQL database table CSV file output

Export MySQL to CSV Using MySQL Workbench

MySQL Workbench contains a convenient export wizard program for exporting MySQL database tables into different formats, including CSV.

Note: If you are using Ubuntu and haven't tried MySQL Workbench yet, check out our guide on how to install MySQL Workbench on Ubuntu. Besides installation, the guide explains basic configuration and usage.

To export using MySQL workbench, do the following:

1. Right-click the table name and choose Table Data Export Wizard.

MySQL Workbench table data export wizard

The action opens the export wizard interface.

2. Select the columns to export and click Next to proceed.

Table data export columns

3. Enter the file path in the appropriate field and provide the file name at the end. Optionally, adjust the export options and select whether to export the file to the local machine. Click Next to proceed.

Table export output file location

4. The following screen shows the tasks that will be performed (preparing the export and exporting data to a file). Click Next again to execute the operation.

MySQL Workbench table data export tasks

5. The wizard completes the export tasks. Click the Show Logs button to view the export logs, or click Next to continue.

Table data export tasks completed

6. The final screen shows the file location and the time taken to export.

Table data export results finish

7. Click Finish to complete the process.

Conclusion

This guide showed several different ways to export MySQL data into CSV files. Use a method that best fits your use case, table data, and environment.

Next, learn how to import CSV into a MySQL database.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP with a passion for programming. With a background in Electrical Engineering and Computing, coupled with her teaching experience, she excels at simplifying complex technical concepts in her writing.
Next you should read
How to Import a CSV file into a MySQL database?
June 6, 2024

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
April 25, 2024

The performance of MySQL databases is an essential factor in the optimal operation of your server. Make sure...
Read more