// you’re reading...

MySQL

Getting the mysqldump you always hoped for.

Dumping data from MySQL might be seem like a covered topic, but just so it’s clear, you aren’t stuck outputting SQL. There’s at least 3 options for outputting data.

Option 1: The SQL way

Good’ole fashioned structured query language output of your whole database in a single file.

mysqldump -u root -p -f your_schema_name_here > your_schema_name.sql

Option 2: XML

The –xml switch will preserve the hierarchy of your table data and format it to XML.

mysqldump -u root -p -f --xml your_schema_name_here > your_schema_name.xml

Option 3: Delimited Data

This is where things get interesting from a artifact perspective. The –tab switch requires that you choose an output path and then creates a .sql file containing the table structure and a .txt file containing the delimited data for each table in the schema.
e.g. CSV Output

mysqldump -u root -p -f --skip-lock-tables 
--tab=/tmp/csv/ 
--fields-optionally-enclosed-by='"' 
--fields-terminated-by=',' 
--lines-terminated-by='\n' 
your_schema_name_here

*Note: I added line breaks to the above script for readability.

Discussion

No comments for “Getting the mysqldump you always hoped for.”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/