Database Synchronization & Revisioning with MySQL
When developing a software application, one of the most common problems is distinguishing between the development, test, and production environments, which consequently duplicates the database instances of a software product and any change on one breaks the consistency with others.
The file changes can be tracked with Version Controlling Software. If you are interested in how to track changes, you might find one of my previous articles helpful. Do not worry about the fact that it is specific to PHP because the logic behind it is the same for any software development technology.
Let’s now focus on two suggested techniques for tracking database changes.
Technique 1 – Version your database change scripts
This technique has also been suggested by Martin Fowler, software engineer and especially popular with Agile Software Development Methodologies, which you can learn more about on his blog page. These steps will help you logically apply this technique:
- When making any change to a database object, you must first save the change script (i.e. ALTER TABLE … script) to a file (.sql, .txt file etc.) and version that file in your Version Control repository with an identity for each script. Identity can be in time format or a series of chronological numbers.
- Keep the last executed change script in the database. For instance, create a Config (Key varchar, Value varchar) table and save “LastExecutedChangeScript” with the identity you specified for it.
- Include some checkpoints in your project. For instance, in Admin CP to ensure your database’s LastExecutedChangeScript is up to date according to the change script file. If not, execute the change scripts from LastExecutedChangeScript to the latest one, and update the corresponding database Config record.
- Change file should be updated automatically with the revision control system. Let the magic begin!
This approach is very straightforward and should be selected anytime if it is applicable. However, if you are using third party software like WordPress, you cannot track database changes because any plug-in, or even WordPress itself, can make changes under the hood.
Technique 2 – MySQL Workbench Database Synchronization
There is a great feature in MySQL Workbench that allows you to synchronize two databases. It can automatically compare and create any change script. There are other third party products that can do this as well (perhaps even more easily!). Because MySQL Workbench is a free application developed by the MySQL Community, it may be the best alternative. However, always research a new tool before using it to make sure that it meets your own needs best.
There are a couple of things you need to keep in mind. First, Workbench needs to create an EER Diagram to be able to compare the databases. You can create the EER Diagram of your local schema and compare it with the remote database. It will automatically display the differences. You can also change which way to synchronize – source to destination or destination to source.
- Open Workbench
- Click Database > Reverse Engineer
- Connect the MySQL Server and choose the database
- When Reverse Engineering dialog disappears, go to “Arrange > Auto Layout” to have it organized
- Click Database > Synchronize With Any Source
- You may choose to save change script into a DB instead of executing it directly.
- Choose the other MySQL Server and database
- Now you can see the differences between both databases and you can decide whether to synchronize them source to destination or destination to source.
Keep in mind that the schema names should be same for source and destination. If they are not the same, generate the EER Diagram to Catalog Tree on the left, “Right Click > Edit Schema” and rename it. Next, on the destination database selection window, Workbench will still ask whether or not you want to ignore the renaming. Ignore it; otherwise it will rename the destination database, too.
It is possible to manage third party software products like WordPress in different environments and synchronize the database changes with this second technique.
To learn more about our custom programming options, please contact us today!
Editorial by Jani Seneviratne.