Mysqldump: create column names for inserts when backing up

Quintin Par picture Quintin Par · Mar 29, 2011 · Viewed 31.9k times · Source

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?

Answer

Cherian picture Cherian · Mar 29, 2011

Use --complete-insert in the mysqldump command params