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
20 Feb
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
Posted by Ryan on April 1, 2008 at 10:39 am
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.
Posted by Tim on March 25, 2010 at 5:08 pm
Beautiful. Thanks.
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.
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.
Posted by MySQL : Dump structure or data only on commandline | Nrdy.ch on May 9, 2011 at 4:59 am
[…] 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 […]
Posted by Mansor on May 26, 2011 at 12:14 pm
Thanks!!!!
Posted by Endel Dreyer on June 26, 2011 at 9:56 am
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/
Posted by lagden on August 30, 2011 at 5:51 pm
Very nice!!!
Posted by survietamine on September 9, 2011 at 4:51 am
thanks a lot, I was bored to remove tables related lines from the dumps.
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
Posted by Zahid.Pakistan on February 3, 2012 at 6:05 am
Finally the last statement i shared worked as user root!!!!:
Posted by khaeg on June 28, 2012 at 12:46 am
thank you, for your statement. :)
Posted by nay on December 18, 2012 at 1:33 pm
awesome thx