Using mysqldump to export CSV file

By default, mysqldump outputs SQL table dumps.  If you ever need to export a table (or even a database) in CSV format using only mysqldump, here's the quick and easy way without using any additional clients:

mysqldump -u root -p --fields-terminated-by="," --fields-enclosed-by="" --fields-escaped-by="" --no-create-db --no-create-info --tab="." information_schema CHARACTER_SETS

mysqldump will generate 2 files (generated file name is based on the table name):

• CHARACTER_SETS.txt
• CHARACTER_SETS.sql

The actual output is in the .txt file.  Output of the command below (output trimmed for brevity):

big5,big5_chinese_ci,Big5 Traditional Chinese,2dec8,dec8_swedish_ci,DEC West European,1cp850,cp850_general_ci,DOS West European,1hp8,hp8_english_ci,HP West European,1koi8r,koi8r_general_ci,KOI8-R Relcom Russian,1latin1,latin1_swedish_ci,cp1252 West European,1latin2,latin2_general_ci,ISO 8859-2 Central European,1[..]

If you get the following error when running the command, specify a location where the "mysql" user (or the owner of the MySQL process) is running can write to (e.g. /tmp).

mysqldump: Got error: 1: Can't create/write to file '/home/mike/CHARACTER_SETS.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

Now for some description on the options:

• --fields-terminated-by: String to use to terminate fields/columns.
• --fields-enclosed-by: String to use to enclose the field values.  Single quote by default.  I set to nothing as it suits my needs.
• --fields-escape-by: Set of string used to escape special characters e.g. tabs, nulls and backspace.  Look here for more info on escape sequences.
• --no-create-db: Do not print DB creation SQL.
• --no-create-info: Do not print table creation SQL.

For more comprehensive info on the mysqldump command, visit the reference manual page here.