Exporting MySQL Database to a CSV

There are various scenarios where you might need to export data from a MySQL database to a CSV file, such as data migration, analysis, or backup. This guide will walk you through how to export either a single table or an entire MySQL database to CSV files.

This guide assumes that you have access to the MySQL database and it is either running on your local machine or you have the necessary permissions on a remote machine.

Pre-Requisites

• MySQL server must be installed and running.
• Access credentials to the MySQL database.
• Terminal or shell access.

Exporting a Single Table to CSV

To export a single table from a MySQL database to a CSV file, you can use the following command:

mysql -u [user] -p[password] [database_name] -B -e "SELECT * FROM [table_name];" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > [output_file.csv]

Replace [user], [password], [database_name], [table_name], and [output_file.csv] with your MySQL username, password, the database name, the table name, and the desired output file name, respectively.

Exporting an Entire Database to CSV

If you want to export all tables from a database into separate CSV files, one for each table, use the following loop:

for tn in `mysql --batch --skip-column-names --raw -u [user] -p[password] -e "show tables from [database_name]"`
do
  mysql -u [user] -p[password] [database_name] -B -e "select * from \`$tn\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > $tn.csv
done

Replace [user], [password], and [database_name] with your MySQL username, password, and the database name, respectively.

A Note on the sed Command and MacOS

The sed command is implemented differently on MacOS, and it does not interpret /t as a tab character. Here are two workarounds:

Quick Fix

You can manually copy/paste a tab character in place of /t in the sed command.

Permanent Fix

Install GNU sed via Homebrew and replace sed with gsed in the commands:

brew install gnu-sed

Best Practices

• Data Integrity: Always double-check the exported CSV to ensure that all required data has been exported correctly.
• Secure Credentials: Never hard-code or expose your MySQL credentials.
• Backup: Always keep a backup of the database before performing any export operation.

By following these guidelines and using the provided commands, you’ll be able to efficiently export your MySQL database tables into CSV files.