Using mysqldump to save data to CSV files

Subject:

mysqldump -u [username] -p -T/path/to/directory [database]

The -u flag is used to specify the username used to connect to the MySQL database server, and you would substitute the [username] part of the above example with your username.

The -p flag indicates that you will enter a password to connect to the database; you will be prompted for it once the command starts executing.

The -T flag followed by the directory name is where MySQL will write its files to. You can have a space between the -T and the start of the directory name or no space: it's up to you as either will work. It is important to note that the directory you specify must be writeable by the user the MySQL server runs as. If it is not, you'll get an error like this:

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

If your Linux machine has SELinux enabled, then the directory must also be allowed by the SELinux configuration for MySQL to write to.

Files created by mysqldump

Using the above example, with a database that has two tables called "something" and "something_else", four files will be created as follows:

  • something.sql - contains the SQL to create the table. By default, it includes DROP TABLE IF EXISTS `something`; as part of the query
  • something.txt - the data from the "something" table in tab delimited format
  • something_else.sql - contains the SQL to create the table. By default, it includes DROP TABLE IF EXISTS `something_else`; as part of the query
  • something_else.txt - the data from the "something_else" table in tab delimited format

If you don't want the *.sql files to be created, then you can add the -t flag to the mysqldump command like so:

mysqldump -u [username] -p -t -T/path/to/directory [database]

Note that even if the directory you specify cannot be written to by the MySQL server, the *.sql files will still be created; it's only the text files which cannot be created.

Changing the output format

By default, mysqldump with the -T flag will dump the data into tab delimited files. However, it is possible to change the delimiter, and also to specify that quotes surround the field values.

To change the delimiter, use the --fields-terminated-by= flag like in the following example. In this example we will dump the data into comma separated values or CSV:

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

If you wanted to also put quotes around each field, then use the --fields-enclosed-by= flag. In the example below, each field is surrounded by quotes. Note that we need to escape the quote symbol on the command line with a slash.

mysqldump -u [username] -p -t -T/path/to/directory [database] --fields-enclosed-by=\" --fields-terminated-by=,

The resulting file would look like the following example:

"1","foo1","bar","2007-12-15 04:20:43"

"2","foo2","baz","2007-12-15 04:20:43"

"3","foo3","bat","2007-12-15 04:20:43"

2015-02-05 12:16:50gstlouis

references
http://www.electrictoolbox.com/using-mysqldump-to-save-data-to-csv-files/

 

IMPORTANT

to avoid permission errros when dumping, dump the file in the /tmp folder

 

gstlouis
vote
2015-02-05 12:17:43