Synchronising Your MySQL Databases Using a Free MySQL Admin Tool - SQLyog
(Page 1 of 2 )
Do you want to know how to synchoronise your remote MySQL datebases together? Try using the free MySQL Admin Tool, SQLyog. I G reports.
This article discusses a solution to bring two remote MySQL databases in Sync with each other using a Free MySQL Admin Tool called SQLyog. You can download the latest version of SQLyog at Webyog.
Overview
As PHP/MySQL developers, we very often see ourselves working on a test server from where we need to apply the changes to the production server. This becomes a tough job if we have lot of tables; the chances of errors while propagating structural changes increases proportionally with the number of tables. At times, when I am working on deadlines, this job becomes frustrating and I find myself wishing I had a tool to do this job for me. SQLyog 3.x is just the answer.
SQLyog is more than a MySQL database synchronization software--it has all of the features that PHP/MySQL users require to work with MySQL. Since I am concentrating on synchronization, I will discuss one of its features--the Database Synchronization Tool.
SQLyog's Database Synchronization Tool, (DST), is a visual comparison/synchronization tool designed for developers who work between different MySQL servers or need to keep databases between two MySQL servers in sync. This means reporting the differences between tables, indexes, columns of two databases, and generating scripts to bring them in Sync.
In Depth
DST enables you to compare and work with different databases. Its color-coded side-by-side comparison makes it easy to pinpoint at a glance, similarities or differences between two databases. DST supports column and index comparison enabling you to quickly insert & drop columns and indexes. If there are any differences in the structure of a column/index, DST generates the correct scripts to apply those changes.
Other features include copying the generated SQL scripts (that bring two databases in sync) into clipboard or saving them in a file.
Getting Started
First of all you need to install SQLyog. You can download it from http://www.webyog.com/sqlyog/download.html. Connect to the different MySQL hosts whose databases you want to bring in sync. This is quite easy as you only have to give your server details and you will be connected.
After connecting, select Tool->Database Synchronization Tool or use the Keyboard Shortcut ( Ctrl+Q to start the tool ). The first screen you will get is displayed to the right.
If you click on either of the database dropdown lists, SQLyog shows you a combined list of all of the databases available from multiple connections that you have made through SQLyog. Select the two databases that you want to compare from the combo boxes.
As seen in the top of the dialog box, the object's state, (whether it be altered, new, etc.), is signified by different colors. The colors and their meanings are as follows:
BLUE -There is a difference between at least one of the columns or indexes between the two tables. In addition, the order of fields in indexes is considered when comparing index objects.
GREEN -The table object is present in the database and needs to be created in the other database.
GREY -The table object is not present in the database and needs to be dropped in the other database (comes as green in the alternate box).
BLACK -No difference was found between the two table objects.
Expanding/Collapsing any object in a tree also Expands/Collapses its partner object in the other table.
This gives you a convenient method to view the difference between two selected databases. The checkboxes in the tree allow you to Select/De-select objects that you want to be altered. By default, only those objects that require some change in structure or new objects will be checked. Other objects are unchecked by default.
The various options like Select All, Unselect All, Toggle Selection, help you to quickly select/unselect objects.
Synchronizing The DatabaseThis is probably the easiest part of the entire exercise. DST creates SQL commands that will bring one database in complete sync with another. This script can either be copied to the clipboard or saved in a file for later execution.
Clicking the LEFT hand Sync buttons generates scripts that will bring the SECOND database in sync with the FIRST database and clicking the RIGHT hand Sync buttons generates scripts that will bring FIRST database in Sync with the SECOND database.
If you copy the generated scripts to clipboard, you can paste it in the Query Window, and press Shift+F5 to execute all SQL statements. If you choose to save the script in a file, use the Tools->Execute As Batch Script option to execute the queries from the file.
Next: Conclusion >>
More Web Services Articles
More By Insanely Great
/ 25 
