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

by

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

Leave a Comment

tech