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:
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"
Restoring data into MySQL from a tab delimited file
Tomorrow we’ll look at how to restore data from a tab delimited file into MySQL.
