Mysqldump: create column names for inserts when backing up

MysqlMysqldumpDatabase Schema

Mysql Problem Overview


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?

Mysql Solutions


Solution 1 - Mysql

Use --complete-insert in the mysqldump command params

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionQuintin ParView Question on Stackoverflow
Solution 1 - MysqlCherianView Answer on Stackoverflow