How can I dump structure of a MySQL DB and import it to alter tables with existing data?

To first give a little bit of context, my use-case is that my partner and I are in the early stages of a web application. The database has been designed, but we still end up making changes to the structure to suit our needs. We therefore need a way of passing back and forth structural changes to the database. I have been attempting to use git to store mysqldumps so that we can pass back and forth all data (as well as revert mistakes), but we run into major conflicts due to our posts having conflicting primary keys! If there is any way around this, the rest of my question will be mostly moot. Assuming there is no solution, I need some way to dump structure that can be run without removing all of the existing data from the DB tables.

I have learned how to dump only the structure of a MySQL database using the mysqdump -d. However, this includes DROP and CREATE commands. Therefore, running this SQL script causes my database to empty (as each table is dropped before creation). I'm looking for a way to dump my database structure such that I can import it and NOT lose my data. In short, I want to be able to dump the database and output ALTER commands, or something to a similar effect.

Now that I've got this far in the question, it seems unlikely that it would be 开发者_C百科possible to dump a DB with ALTER commands. Regardless, is there a way to dump MySQL data such that it can change the structure of tables without removing the existing data?

Thanks a lot!

Paragon

You already have your answer -- write the scripts that alter the database structure, version them in git, and come up with a process for running them. Whoever is changing the structure should formalize these changes in a sql script. Typically it's just going to be create table or alter table DDL. Like any other type of code these changes should be tested. Then it's simple enough for either one of you to get the latest required update.sql script from git, and run it via command line mysql or in a tool.

Sometimes just altering the table is not enough -- you also need to do queries that convert the data. I also will lock relevant tables so that these queries can be run against production if needed. Just name the scripts name.sql, and establish a convention for how you'll name them and where you'll store them in your tree. It could be as simple as update_1.sql If you already have been versioning DDL you can stick them in that directory.

One other thing ... most alter table changes do not effect the data in the database. On rare occasions there might be a problem, but you can always code up a workaround in the script like using a temporary column or table, with some sql statements that move the data back and forth during the translation.

There are tools out there that will do this type of thing for you, but I don't see why you would need them when you are controlling the structural changes. The PHP ORM Doctrine2 has some of these capabilities and I've seen other products that offer the same type of capability ... comparing 2 db's and determining the structural changes and DDL required to sync the structures. That won't be helpful to you when you are both trying to work independently.

What I suggested is simple and effective, and has been used on numerous projects I've been involved with.