MySQL databases are a common way to manage and store a wide variety of structured data. However, you would often need to migrate data from your MySQL database to another. There are many ways to migrate data. One of the popular methods to migrate data is to export MySQL data in CSV files. But, migration is not limited to just moving between two databases. There are many reasons to export MySQL data with CSV files.

Following are some of the reasons for MySQL to CSV export.

• You can build reports from the exported CSV data.
   
• You can easily access your database data in offline mode.
   
• Several SaaS tools accept CSV files to import or export data.

This article will demonstrate 5 ways to export data from MySQL to CSV. However, let’s first understand what CSV and MySQL are in brief. 

What is a CSV file?

A CSV (Comma Separated Value) file is a plain text file used to store data. The values in CSV files are separated using commas. Since separating data with commas is a simple process, the CSV files are lightweight. As a result, it is a popular choice for exporting and importing data among data professionals. 

Although the data in CSV files are separated in comma-delimited format, you can quickly convert them into a columnar format. This allows you to quickly read and analyze data with different programming languages.

What is MySQL?

MySQL is an open-source Relational Database Management System. It is widely used to store structured data to perform quick analysis. With MySQL, you can store data across multiple tables and apply the relationship among data points for better accessibility. 

MySQL works with Structured Query Language (SQL), making it accessible to a wider range of users. However, you can also talk to the MySQL database with different programming languages like Python, Java, and more.  

Methods of Exporting Data to CSV from MySQL Database

In this section, you will learn how to export your databases or tables from MySQL to CSV files: 

1. Using the Command Line
2. Using Mysqldump
3. Using phpMyAdmin
4. Using the CSV Engine
5. Using MySQL Workbench
6. SaaS Alternatives

1. Using the Command Line

You can use the command line method to establish a connection with your MySQL database and export data into a CSV file.

1. To open a command prompt, press the Windows button + R and type cmd in the run window. 
    
2. A command line interface will appear on your screen. Verify if MySQL is running by executing the net start command in the command prompt. You will see a list of services that are currently running. 
    
3. Connect with your local MySQL server using the below command:
   
   mysql -u [username] -p
   
   Enter the username at [username] that you created for MySQL installation.
    
4. You can log in to your MySQL local server as the root user using mysql -u root -p command.
    

4. On execution of the above command, you will be prompted to enter the password. 
   
   Enter password: *******
    
5. Now, you’re connected to the MySQL server. The command prompt will now look like this:
   
   mysql>
    

6. Move to the MySQL folder using the cd command and list the files in this folder using dir command.
   
   cd c:\Program Files\MySQL

dir
    
7. To check the list of databases, enter the below command.
   
   show databases;
    
8. To select a specific database, use the name of the database that you want to use.
   
   use [databasename];
   
   Replace [databasename] with the name of the database you want to select.
    
9. Now, let’s export the data in a CSV file. Let’s consider the below MySQL database table for exporting in a CSV file.

Image Credit: learnsql

11. Use the below command to export data from MySQL to CSV. 
    
    SELECT * FROM data.employees 
INTO OUTFILE 'employees.csv';
    
    Here, the SELECT statement is used to get the data from the data.employees table and the data is exported in the employees.csv file. INTO OUTFILE statement will load the data into the employees.csv file.
    
    If you are using Windows, your file will be stored at the C:\ProgramData\MySQL\MySQL Server 8.0\Data location.
    
    The above method will export data into CSV without any specific customization. You can follow the below methods to add column names, export specific columns of a table, or handle null values.
    
    Using the command line method, you can also export customized data from MySQL to CSV with the following approaches: 
    
    
    1. Export data with a column heading
    2. Handling NULL values
    3. Export specific columns of a table

Export Data with a Column Heading

It would be easier to understand data if the exported CSV files contained a column heading as their first line. Use the UNION operator to add a column heading to the CSV file. 

As shown in the command below, you need to add a column heading for every column.

Code: 

plaintext
(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber, orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY ' " ' TERMINATED BY ';' ESCAPED BY ' " '
LINES TERMINATED BY '\r\n');

Let’s understand the query in more detail:

• In the first SELECT statement mention the column heading, add the column heading that you want in the output file. 
   
• In the OUTFILE, mention the path where you want to store your csv file with .csv extension.

Handling NULL Values

Often your tables might contain NULL values which would be shown as ‘N’ in the exported file. You can change this ‘N’ with your preferred text for better understanding using the IFNULL function. 

Code:

plaintext
SELECT empName, empjoinDate, IFNULL(empNumber, 'N/A') 
FROM employee INTO OUTFILE 'C:/tmp/employeedata2.csv' 
FIELDS ENCLOSED BY ' " ' 
TERMINATED BY ';' 
ESCAPED BY ' " ' LINES 
TERMINATED BY '\r\n';

In the above query statement, the ‘Null’ values in the empNumber column are replaced with ‘N/A’ string.

Export Specific Columns of the Table

For more convenient use, you would want to select specific columns from a table. Using the SELECT statement and WHERE clause, you can achieve filtered results. For reference, check the below command lines.

plaintext
SELECT employeeName,  
FROM Employee
WHERE employeeName= 'value'; 
INTO OUTFILE 'employees.csv';

In the above query, employeeName is the column name and Employee is the table name. Using the WHERE clause, you can select specific values from the column. With this method, you can export specific columns and values from the table. 

2. Using mysqldump

mysqldump is one of the functions used to perform logical backups of MySQL databases. For backup and transfer tasks, mysqldump dumps one or more databases into the SQL server. Using this function, you can also generate output in XML, CSV, or other delimited formats.

To perform a MySQL dump to CSV, use the following command in the terminal.

Code:

plaintext
mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

The above command will create a copy of the table, as mentioned in [tableName], at a specified location/path, which is defined using -T flag. 

A CSV file will be created with a .csv extension, and the file name will be the same as that of the table name. 

To dump/export the entire database, do not use [tablename] after [database], use the --databases or --all-databases option.

3. Using phpMyAdmin

With phpMyAdmin, you can export your databases in CSV, XML, YAML, and JSON formats. It provided a Graphical User Interface (GUI) to export data from the database to any other format.

Below is the step-by-step guide to exporting data using phpMyAdmin.

Step 1: Log in to your phpMyAdmin. Next, choose the database and select the table you want to export. Now, click the ‘Export’ button from the top menu bar.

Image Credit: inmotionhosting.com

Step 2: Next, you need to select the ‘Export Method’. In order to export all the data from the table, select the ‘Quick’ option. You can choose the 'Custom' option if you want to export specific data from the table.

Image Credit:  inmotionhosting.com

Step 3: After selecting an Export method option, you need to select the format of your exported file. Now, choose the CSV from the dropdown list under the format menu.

Click on Go. Next, click on the Save button.

4. Using the CSV Engine

The CSV store engine is one of the ways to export to CSV from MySQL. It uses CSV format to house data in text files. 

Once you create a table, the server will create a .csv extension file name. The file name will be the same as that of the table name. It stores data using comma-separated values format, as shown below in the output.

Code:

plaintext
CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) 
ENGINE = CSV; 
Query OK, 0 rows affected 

INSERT INTO test VALUES(1,'record one'),(2,'record two'); 
Query OK, 2 rows affected 
Records: 2  Duplicates: 0  Warnings: 0 

After executing the above commands, you will get a test.csv file in the database directory. The data in the file should look like this.

Output:

plaintext
"1","record one" 
"2","record two"

5. Using MySQL Workbench

MySQL Workbench provides import/export wizard that allows you to import or export your database or specific tables. The wizard uses a Graphical User Interface (GUI) and supports CSV and JSON format. 

Click here to download MySQL Workbench.

Follow the below steps to export MySQL output to CSV files. 

Step 1: Open MySQL Workbench and select the database. Right-click on the table name and choose ‘Table Data Export Wizard.’ You can also perform this step from the tab menu, click ‘Server’, and select ‘Data Export.’

Image Credit: dev.mysql.com

Step 2: After selecting the ‘Table Data Export Wizard,’ you will see the below window. By default, all the tables will be selected from that schema. You can uncheck if you don’t want to export any table. Click Next.

Image Credit: dev.mysql.com

Step 3: Next, you will get a ‘Table Data Export’ window. Here, you can browse the path to store your file. Choose the CSV format. Click Next.

Image Credit: dev.mysql.com

Step 4: In the ‘Export Data’ window, check if ‘Prepare export’ and ‘Export data to file’ are selected. Then click Next. Your data will start exporting, and you can track the process through Logs. 

Image Credit: dev.mysql.com

6. SaaS Alternatives to CSV Export

The five methods above are great options if your ultimate goal is to export a small number CSVs locally from a few MySQL tables. However, this is rarely your final goal. 

What if you want your MySQL data in a spreadsheet?

Or you need to integrate your MySQL data with a SaaS application?

If your plan is to export MySQL data as a CSV and then upload it to another platform, that might not be your best option. That’s because:

1. Time. If you have multiple tables to export, this process gets incredibly tedious and time-consuming. Even if you can devise a script to help, it’s likely not the most efficient use of your time.
    
2. Latency and accuracy. Your data in MySQL probably changes quickly. By the time you export your CSV and upload it to another system, the source data might have already changed! This means you’ll end up with an inaccurate picture of reality in your other data systems… and you’ll have to re-export the CSVs regularly, taking up more of your time. 

Luckily, this process isn’t the only way to get data out of MySQL and into other systems. 

Estuary Flow lets you easily extract data from MySQL using change data capture and write that data to a variety of destinations, including Google Sheets, BigQuery, Snowflake, and more. Once deployed, the pipeline operates continually in real-time — no repetitive process needed. 

Conclusion

Exporting MySQL databases or tables into CSV files is quite an easy task and a great way to store backups. There are plenty of tools available to export databases into CSV files. Now, using the above methods, you can easily export MySQL data to a CSV file. You can use the command line or mysqldump methods if you are comfortable with querying. However, phpMyAdmin and Workbench are the best tools if you are not accustomed to MySQL queries.

But exporting MySQL data to CSV isn’t ideal for all use cases. To quickly replicate your MySQL data to a spreadsheet, or elsewhere, in real-time, give Estuary Flow a try — your first pipeline is free.

Keywords: mysql, csv