Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I'd like to avoid mysqldump since that outputs in a form that is only convenient for mysql to read. CSV seems more universal (one file per table is fine). But if there are advantages to mysqldump, I'm all ears. Also, I'd like something I can run from the command line (linux). If that's a mysql script, pointers to how to make such a thing would be helpful.

like image 557
dreeves Avatar asked Jan 21 '09 23:01

dreeves


People also ask

How do I export a MySQL database to a CSV file?

Select the table of the database that you want to export and click on the Export tab from the right side. Select the CSV format from the Format drop-down list and click on the Go button. Select the Save File option and press the OK button. The file will be downloaded in the Downloads folder.

How do I dump a MySQL database in terminal?

The mysqldump client utility A standard mysqldump command is represented by the following command syntax. -u [mysql_username]: represents a privileged user of the MySQL database. This user should be able to execute database dump operations. -p[mysql_password]: represents the user password of the MySQL database.


1 Answers

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 
like image 146
Alnitak Avatar answered Sep 19 '22 16:09

Alnitak