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?
Use --complete-insert
in the mysqldump command params