Using mysqldump to save data to CSV files

Yesterday I looked at basic use of mysqldump for backing up MySQL databases. Today I will look at how to use mysqldump to dump the data from a MySQL database into CSV and tab delimited text files, instead of using SQL insert queries which is the default dump method.

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:

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.