Schema Synchronization

SQLyog's Schema Synchronization Tool, 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 and routines of two databases, and generating scripts to bring them in Sync.

Schema Synchronization Tool 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. Schema Synchronization Tool supports column, index and routines comparison enabling you to quickly insert & drop columns, indexes and routines. If there are any differences in the schema of a column/index/routines, Schema Synchronization Tool generates the correct scripts to apply those changes.

Select  Powertools -> Schema Synchronization Tool or use the keyboard shortcut  Ctrl+Q to start the tool.

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, indexes or routines between the two databases. In addition, the order of fields in indexes is considered when comparing index objects.
GREEN: The object is present in the database and needs to be created in the other database.
GREY: The 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 database objects

Hide and ignore object(s):

Use upto 3 of the 4 checkboxes to hide object matching any of the 4 criteria. What you select has effect on what is displayed in the tree views after clicking the 'Compare' button.

Note that objects that are hidden are also ignored when generating the sync script. This can give you a better overview and you may use the options to protect your data against unwanted changes. Examples:

1) Check 'Identical' object(s) that exists on both hosts. No action will ever be taken with those whether displayed or not. You can hide those to better be able to identify the changes that will take place.

2) Check 'To be dropped in target' will prevent anything being dropped by the sync script as the objects will be invisible to the code generating the sync script.

3) Check both 'To be dropped in target' and 'To be created in target' will ensure that only identically named objects that exist on both hosts will be synced (that is only ALTER statements will be generated - not CREATEs or DROPs.

Filter: Use filter to synchronize structures of only specific database objects. One can use MySQL wildcard characters like "%" and "_" for pattern matching. If you have got "%" or "_" as literal in Database Object name then escape it with backslash, example "\%" and "\_".

Also note option to compare 'All objects' or compare 'Only tables'. After clicking 'Compare' you can deselect individual objects. Next and click Compare once more (and those individually selected objects will not hide - but they will be ignored). Also, whatever you have selected you can toggle (reverse selection) and Select All/Deselect All.

Set FOREIGN_KEY_CHECKS=0 for Target: If this option is checked, SQLyog issues SET FOREIGN_KEY_CHECK=0 command before the sync on the target server. It allows you sync data without validating FOREIGN KEY integrity.

Ignore DEFINER: If this option is checked, it will Ignore the DEFINER clause for database objects. Note that the target server will then create DEFINER as current SQLyog user when creating the object.

The size of the object area and the script area can be changed by moving the splitter between those with the mouse.

Synchronizing The Schema

The generated script can either be executed directly or saved in a file for later execution.

Note:This feature is available in SQLyog Enterprise and Ultimate.