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

About these ads

12 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.

    Reply

  2. Beautiful. Thanks.

    Reply

  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.

    Reply

  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.

    Reply

  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 [...]

    Reply

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

    Thanks!!!!

    Reply

  7. Thanks dude,

    I’ve added some trick to omit auto-increment column values.

    http://bugfixer.endel.me/2011/06/25/tip—omit-auto-increment-values-using-mysqldump/

    Reply

  8. Very nice!!!

    Reply

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

    Reply

  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

    Reply

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

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

    Reply

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

    thank you, for your statement. :)

    Reply

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: