Dump a mysql database to a plaintext (CSV) backup from the command line

Question

I am developing an offline Digital Addressing System. I need to keep spatial data in my spatialite. However, i have my spatial data in SQL server. How do i convert them into a format that can be used by the spatialite?

Solution 1

If you can cope with table-at-a-time, and your data is not binary, use the -B option to the mysql command. With this option it'll generate TSV (tab separated) files which can import into Excel, etc, quite easily:

% echo 'SELECT * FROM table' | mysql -B -uxxx -pyyy database

Alternatively, if you've got direct access to the server's file system, use SELECT INTO OUTFILE which can generate real CSV files:

SELECT * INTO OUTFILE 'table.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
FROM table

Solution 2

In MySQL itself, you can specify CSV output like:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

Solution 3

You can dump a whole database in one go with mysqldump's --tab option. You supply a directory path and it creates one .sql file with the CREATE TABLE DROP IF EXISTS syntax and a .txt file with the contents, tab separated. To create comma separated files you could use the following:

mysqldump --password  --fields-optionally-enclosed-by='"' --fields-terminated-by=',' --tab /tmp/path_to_dump/ database_name

That path needs to be writable by both the mysql user and the user running the command, so for simplicity I recommend chmod 777 /tmp/path_to_dump/ first.

Solution 4

The select into outfile option wouldn't work for me but the below roundabout way of piping tab-delimited file through SED did:

mysql -uusername -ppassword -e "SELECT * from tablename" dbname | sed 's/\t/","/g;s/^/"/;s/$/"/' > /path/to/file/filename.csv

Solution 5

Here is the simplest command for it

mysql -h<hostname> -u<username> -p<password> -e 'select * from databaseName.tableNaame' | sed  's/\t/,/g' > output.csv

If there is a comma in the column value then we can generate .tsv instead of .csv with the following command

mysql -h<hostname> -u<username> -p<password> -e 'select * from databaseName.tableNaame' > output.csv

View more solutions

Comments

[dreeves]

thanks! your second "table" should be "database", right? no option for CSV instead of TSV that you know of?

[Alnitak]

duh - yes, it should have read 'database'. No, there's no option for CSV, this is the best I know of without using MySQL's built-in 'select into outfile', which can do CSV, but writes the files on the server, not the client.

[dreeves]

thanks, very helpful! you convinced me. i do have other reasons to want a quick way to get a csv dump via a command line command though. i'm holding out for that for the "accepted answer". (tho i could probably piece it together at this point from the current answers, i guess w/ a mysql script).

[Zoredache]

I can see lots of value in having both a backup using the native method and also extracts for other purposes.

[kliron]

mk-parallel-restore can restore a CSV backup created with mk-parallel dump, so you can get the best of both worlds. In fact, mk-parallel-restore does a better job by ensuring any triggers you have defined are restored last.

[atroon]

This may not be the ideal backup but is completely awesome for SHARING. Thank you!

[JCm]

I cant seem to find the dumped file on the directory i specified, why is that?

[JCm]

how to force overwrite on the output file?

[Alnitak]

@JCm it dumps on the server

[Mala]

Note that when a relative path (or simply a filename) is given, the file will appear in your MYSQL directory, and not your current shell directory (i.e. where \. file.sql reads from). As such, depending on your install you will probably find it at /var/lib/mysql/[db_name]/table.csv

[sjas]

maatkit seems to be part of the percona toolkit now, but I cannot find corresponding tools.

[BrennanR]

This command resulted in the letter t being replaced with ", " in the output file. Not exactly what I was after.

[joevallender]

I kept coming back to this answer, it's definitely the best way to export all the tables to a delimited file.

[Marco Marsala]

mysqldump: You must use option --tab with --fields-...

[Michael Roswell]

This mostly got me where I needed to go but was surprised when the letter "t" was getting replaced with commas: stackoverflow.com/a/2610121/8400969

[Michael Roswell]

And this should say --skip-column-names in my version of mysql, as well

[Admin]

For CSV: it works where i just need comma. mysql -hlocalhost -uroot -e 'select * from databaseName.tableNaame' | sed 's/\t/,/g' > /tmp/output.csv `

[qräbnö]

As root: GRANT FILE ON *.* TO 'user1'@'localhost'; - stackoverflow.com/a/15014385/1707015. In my case I had to take /var/lib/mysql-files/ (instead of /tmp/), set the user to mysql:mysql and set the rights to 777 - stackoverflow.com/a/32737616/1707015.

[Luis]

This does not work. It does not generate a csv file