88

How do I instruct mysqldump to backup with column names in insert statements?
In my case I didn’t a normal back up with insert sql’s resulting in 

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` 
INSERT INTO `users` VALUES (1

structure.

Now I went ahead and removed a column from the schema in users. After this when I run the backup sql’s I get a column number mismatch error.

To fix this how do I go about instructing mysqldump to write column names too? Here is how I do it now

mysqldump --host=${dbserver} --user=${dbusername} --password=${dbpassword} \
          --no-create-db --no-create-info --extended-insert --single-transaction \
          --compress tablename

On a broader level, what’s the best practice to manage these schema changes?

Amil Waduwawara
  • 1,632
  • 1
  • 16
  • 14
Quintin Par
  • 15,862
  • 27
  • 93
  • 146
  • 1
    How will explicitly specified fields help you? After you have deleted a column even query with specified fields will be incorrect. – zerkms Mar 29 '11 at 03:24
  • @zerkms It will help when you only have added fields to a table. If there have been fields deleted, they should be removed entirely – Kellen Stuart Apr 06 '18 at 00:47

1 Answers1

223

Use --complete-insert in the mysqldump command params

Cherian
  • 19,107
  • 12
  • 55
  • 69
  • 11
    Is there a sane reason why this should not be the default behaviour for mysqldump? – paradiso Jun 17 '14 at 22:55
  • 11
    @paradiso I believe that is because `mysqldump` is heavily used for backups, and it is a nice thing that they are as smaller as possible. – Cacovsky Jul 03 '14 at 18:30
  • 11
    @paradiso also note that mysqldump is by default dumping the structure and the data, thus making the column names information useless. Having column names specified is useful when you load values into a table whose structure may differ from the one you extracted the data from. – Ninj Apr 29 '15 at 11:18
  • @paradiso The smaller the file the better. Not including column names drops the size of the file substantially depending on the complexity of your database – Kellen Stuart Apr 06 '18 at 00:48
  • But in some case like mine it's kind of life saving, I've local database that's getting changes day to day but not instantly goes to live server, so when I got to update the live server db then it's definitely a life saving for me, otherwise i'd have to track all column names that i've added to my local shema's and then add those columns with add columns manually for each table. but with this simple trick i can save many times and headache. – Nirob Oct 24 '20 at 14:33
  • An indication where having the column names is helpful: I have a django project, that when built from scratch places the columns in a different order to the dump. I suppose this has happened because the column names/structure has changed over time. Became a particular problem moving from MySQL to PostgreSQL. But --complete-insert solved it. – typonaut Apr 04 '22 at 16:03