I work with a small web team that is currently in the process of getting GIT integrated into our development process. We develop locally, have a central bare repository and then pull changes down to separate test and production servers. This is working great for our files but we are hitting roadblocks when it comes to syncing MySQL databases.
We have a lot of sites built with WordPress and the issues are more prominent here:
- WordPress inserts the domain name into the DB. Right now, we get around this by doing a find and replace whenever we move the sites from local, to testing and then to production. It would be nice if we didn’t have to do this, though.
- The production server site DBs are constantly changing (comments, etc.) and the testing server and our local servers are not in sync. This makes it difficult to send changes (after adding a plugin, page, etc.) to the production DB from the test server.
It would be great if we could find something that could integrate with GIT (maybe through githooks) that would allow us to sync the databases across different development and production servers. Moreover, it would be a bonus if there were a way to track changes within the database itself — allowing us to merge changes (development edits and production changes) when pushing to production.
And finally, it would be even better if this could all work across multiple domains (local, testing and production); in other words, it would have to find and replace the URLs in the sql on each push/pull.
Thanks a bunch for any insight.
You might want to check out http://www.liquibase.org/. It’s a database refactoring tool made for creating and modifying database schema, creating rollbacks and code for SQL generation. I was introduced to it a long while back and can’t remember it that well, but it seems like it’s made for what you need and from what I remember it kicks ass.