#How to Export a CSV file from MySQL Command Line – CloudSavvy IT

Comma Separated Values files (CSV) are a way of transferring data between applications. Databases like MySQL and spreadsheet software like Excel support importing and exporting through CSV, so you can use CSV files to exchange data between the two.

CSV files are plaintext, so they’re naturally lightweight and easy to export from MySQL.

From The Database Server

If you have access to the server that MySQL is running on, you can export a selection with the INTO OUTFILE command.

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

This will output a CSV file to /tmp/mysqlfiles/table.csv, or wherever you configured it to. You’ll need to make sure the user running MySQL (usually mysql or root) has ownership and write access to the directory.

You’ll also need to make sure the secure_file_priv setting allows MySQL to access that directory. This, by default, blocks read and write access from SQL queries. This is a good thing; if your code is vulnerable to SQL injection, any potential attacker would only have access to MySQL, and not the rest of the filesystem.

You can whitelist specific directories by editing your MySQL config file (usually located at /etc/my.cnf) to include:

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

Which will allow MySQL to read and write to /tmp/mysqlfiles/ (which you’ll have to create with mkdir). Once MySQL can export files, you should be able to run the query and output CSV files.

With the ENCLOSED BY setting, commas will be properly escaped, e.g.,:

"3","Escape, this","also, this"

Which you can take and import directly into any spreadsheet program or other software.

Keep in mind that the exported CSV file doesn’t include column headings, but the columns will be in the same order as the SELECT statement. Also, null values will be exported as N, which is expected behavior, but if you’d like to change this you can modify the selection by wrapping ifnull(field, "") around your fields in your SELECT statement.

From The MySQL Command Line

If you only have command line access to the MySQL instance, and not access to the server itself (such as when it’s not managed by you, in the case of Amazon RDS), the problem is a little trickier. While you can use FIELDS TERMINATED BY ',' on the server to generate a comma-separated list, the MySQL CLI will separate with tabs by default.

Simply enter a query from the command line, and pipe it to a file:

mysql -u root -e "select * from database;" > output.tsv

Because MySQL output is separated with tabs, this is called a TSV file, for “tab-separated values,” and may work in place of your CSV file in some programs like spreadsheet imports. But it isn’t a CSV file, and converting it to one is complicated.

You could simply replace each tab with a comma, which would work but would cause it to fail if there are commas in the input data. If you’re entirely 100% certain that there are no commas in your TSV file (check with grep), you can replace the tabs with sed:

sed "s/t/,/g" output.tsv > output.csv

But if you have commas in your data, you’ll have to use a much longer regex:

sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

This will properly escape fields with quotation marks, which will solve the comma problem.

Note: the tab character t is not standard. On macOS and BSD, it’s not available, which leads to a mess of every lowercase “t” causing sed to insert erroneous commas. To solve this, you’ll need to use a literal tab character in place of t:

sed "s/  /,/g" output.tsv > output.csv

If your input data contains tabs, you’re out of luck, and will have to generate a CSV file yourself with a scripting language.

Do It Manually With a Real Programming Language

MySQL (and most databases) are designed to be interacted with, so you likely have some sort of programming language connected to MySQL already. Most languages can also write to disk, so you can create your own CSV output scripts by reading fields from the database directly, escaping them properly, and writing a comma-delimited file.

An example in Python.