Version control for MYSQL database schema

Was wondering whether anyone has come across a decent MYSQL IDE with version control baked in? I am looking for something like what Red Gate developed for MSSQL called SQL Source Control

In my own research I've come across a few very manual systems and while they get the job done, it is quite an onerous process to get the schema changes recorded (requires SQL dump or manually copying and pasting IDE generated SQL scripts). I would rather not spend my time chasing my developers about not checking in their schema changes, but instead have an IDE that has native VCS support wired in,  automatically tracking and commits schema changes as the developers are making them.

My VCS of choice is GIT, but I'm open to using TFVC. Any help would be very much appreciated!
tdilbertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Why don't you script the schema export using mysqldump of a single DB with the option --no-data then compare the output of this backup against a prior if there is a change, ......
Your script could then depending on how granular you want (per table schema change) versioning to be, use versioning tools you want to commit the new schema into the system. Granular schema males thing more complicated as a change in one table, could have dependencies in others ......

One has to be one accustomed to using opensource  tools/resources, one has to put a bit of effort assembling the various tools to achieve what they envision.
0
PWinterCommented:
The db schema is data in information_schema db. Once you see it as such, the life is easier.

You can run the query by cron with saving the result to a text file, and check in the file to git.
You can make a routine that does same and call the routine from trigger on any change to, say, information_schema.tables.
You can store result of such query to, say, Excel, and add a simple script that re-executes the query and detects differences with existing metadata (that's what I do).

Meanwhile, I would suspect that tracking schema changes is not a largest challenge you face. Making database modifications in consistent and disciplined way is.

Good luck.
PW
0
tdilbertAuthor Commented:
Hey guys,

With a little bit of luck I received an email from a colleague that pointed me towards dbForge Fusion for MySQL. This sounds like the type of integration we're looking for since it plugins into Visual Studio.

Just as a side note, I've seen the DB dump option recommended quite a few times but it really doesn't offer as much transparency as you'd like / think. Without investing quite a bit of investigation (opening and diff'ing the SQL dump file each time), it isn't easy to look for where (within the history of changes) a MySQL table (or column) was modified. For example, when developers are using GIT, they'll have multiple commits and the change I may be looking for is in commit 2 of 30, but hidden within the single MySQL schema dump text file, that's been modified X times. I have to diff the file between commits to find out specific where within the history the column was changed.

The solution I  was looking for would ideally that break out the schema into one file per object (table, FK relationship, sproc etc.); that seems to be what dbForge does. What's also nice about dbForge is that MYSQL support on dotConnect for MySQL is also included (ORM).

Thanks for the responses though!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Steve BinkCommented:
No points, but...  

>>> For example, when developers are using GIT, they'll have multiple commits
>>> and the change I may be looking for is in commit 2 of 30, but hidden within
>>> the single MySQL schema dump text file, that's been modified X times. I
>>> have to diff the file between commits to find out specific where within the
>>> history the column was changed.

Ya know, that's what git is *for*
0
arnoldCommented:
The difficulty with your 2 of 30, is  that other changes in 3 of 30 and 4 of 30 etc. could be dependent on that.
Usually, one change per event, but in database structure that is not possible to make one change at a time because of the inherent dependencies such that if you go to 2 of 30 everything has to be reverted including the frontend interface that accesses this database.
the IDE referenced as is redgate are to be used in test/development environment and the changes deemed appropriate there are committed into production.

To deal with what you wish to achieve involves more than maintaining a versioning system of the schema,
i.e. a complete log of changes/why they are being made and t he dependencies i.e. change in table 1 column 3 has an implication in the software, has other tables/columns that depend on it i.e. FK, reference, triggers, etc.
0
tdilbertAuthor Commented:
IDE integration was quite important for my team. This allows change tracking to happen within the same IDE that developer is already using, thus helping promote good habits re: ensuring schema changes are tracked within a VCS. Also, because solution is within Visual Studio, solution includes GIT, and TFVC support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.