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.
 Click for enlargement |
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.
 Click for enlargement |
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.
 Click for enlargement |
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 Database
This 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.
Conclusion
Keeping your production database up to date with your test
database is now a piece of cake with SQLyog’s powerful Database Synchronization
Tool.
