mysqldump data only

mysqldump has this terrible affinity for blowing its nose all over your data. If you just want a list of INSERT statements, here’s the magic spell:

mysqldump --skip-triggers --compact --no-create-info


13 responses to this post.

  1. Just so you know, those switches made my dump output nothing at all.

    It was working before these.

    It might just be what data I have or something, but I just thought I’d give a heads up here.


  2. Beautiful. Thanks.


  3. Posted by Roger on August 11, 2010 at 5:11 am

    Works fine.
    If you have to alter a table between backup and restore, add “–complete-insert” to be able to restore data even there are more fields after alter table.


  4. Posted by Metin on January 25, 2011 at 3:31 am

    –no-create-info is what i was searching. It skips dumping the create table and view parts for me. Just good plain data:

    mysqldump –no-create-info –extended-insert=FALSE
    –compact -uUSER -pPASS DBSchema > insert_data.sql

    Option –extended-insert=FALSE disable multi value inserts, which results in single value inserts. Each row has it’s own insert statement.


  5. […] had to dump the structure or the data only from an entire database. After some searching around i found an very easy approach for doing each, dumping only the data or only the structure of a […]


  6. Posted by Mansor on May 26, 2011 at 12:14 pm



  7. Thanks dude,

    I’ve added some trick to omit auto-increment column values.—omit-auto-increment-values-using-mysqldump/


  8. Very nice!!!


  9. thanks a lot, I was bored to remove tables related lines from the dumps.


  10. Posted by Zahid.Pakistan on February 3, 2012 at 6:03 am

    I needed only data and insert statements on single line with no comments and no DB creation statement as:
    mysqldump –skip-extended-insert –skip-comments –no-create-info –no-create-db -u root -pPWD DATABASE > /mnt/FOLDER/backup_`date +%m%d%y`.sql


  11. Posted by Zahid.Pakistan on February 3, 2012 at 6:05 am

    Finally the last statement i shared worked as user root!!!!:


  12. Posted by khaeg on June 28, 2012 at 12:46 am

    thank you, for your statement. :)


  13. awesome thx


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: