One To Many Schema Sync

[bmreeves]

Ill try to explain this as best as I can. First off, we just downloaded SQLyog. We are demoing it for a very specific purpose but would probably use it for additional features in the future if it meets our needs. Here is some back story.

 

We started a SAAS organization some time ago based of PHP, MySQL, and linux technologies because that is what we knew. The architecture is pretty simple. The technology sits in Azure, there is a single php codebase that is a frontend to multiple mysql backends depending on the clients regulatory requirements.

 

We have multiple staging environments that look like this: “alpha => beta => dev => production” from a code perspective. From a php perspective, this has worked great because we only have to update a single codebase for all our clients, however due to growth, we now have grown to over 100 mysql databases we are managing. The management isnt necessarily the problem, What is the problem, is that when we go through the development cycle, we have to alter the schemas on multiple databases without impacting the underlying data.

 

We did the demo with SQLog and was looking for some input prior to purchasing. Currently, we have been running a shell script to perform some of the updates, however we were looking to use a tool like SQLyog to perform the diff of our development and our known production database. Then we would use a script to perform the alter across all the databases without impacting the data. Additionally, we often have multiple updates so we would like to understand how that could be accomplished easily.

 

So for example:

 

Compare db_dev with db_prod = db_diff_date (we have been able to get this part done with SQLyog)

use db_diff_date to alter schema for client_db_1, client_db_2, etc. until all databases are completed. (We cant seem to get a shell script to iterate through databases properly to do the updates here with the output from SQLyog)

 

We are familiar with shell scripting and do most automated tasks that way, however as we are finding out, when dealing with MySQL, quotes, commas, doublequotes, etc can make it more complicated so we were hoping that SQLyog would provide the input to a script that would minimize the potential for error.

 

Thanks

[Sibin A S]

Hello bmreeves,

 

You can try using the option “Schema Synchronization Tool” to sync the schema of different tables. Also the “Schema Synchronization Tool” generates the script after comparing that can be saved as a sql file and executed at a later time or Schema Synchronization Tool can execute the generated script to sync the schema by clicking on the ‘Execute All’ button.

 

As far as the Schema Synchronization Tool is concerned, it is not possible to automate the synchronisation process.

[Author]

Posts