Devart Blog

Automated MySQL data comparison and synchronization: how it works

Posted by on July 30th, 2009

In dbForge Studio for MySQL you can find Data Compare, a tool which allows you to compare and analyze tables data in databases, for example, to check that data in the synchronized databases are identical. Besides, it  allows synchronizing the data differences to get identical databases. The following picture illustrates comparison and synchronization of two MySQL databases.

Data Comparison Workflow

Data Comparison Workflow

To compare data, the program automatically selects columns with a primary or unique key as comparison keys. Of course, you can set comparison keys manually, however, in such cases key repetition may occur which can cause conflicted records after comparison. Such records are not compared.

Conflict Records

Conflict Records

If a comparison key for a compared pair of records is found in Source database, but is absent in Target one, the program considers such records being Only in Source. If the key is found in Target, the records get Only in Target status.

Only in Source (Target) Records

Only in Source (Target) Records

If a comparison key is found in both databases, the program compares columns without primary or unique keys and, based on the result, considers the records either Identical or Different.

Different (Identical) Records

Different (Identical) Records

You can review data differences in every compared table and decide which records should be synchronized and which of them should be excluded from the synchronization.

Data Synchronization Wizard allows you to generate a synchronization script to review how the Target database will be changed after synchronization and execute the script. For records with Different status (which have comparison keys in Source and Target database, but their data is different), the program generates the sсript with INSERT statements.  So does it for records with Only in Source status (they have a comparison key only in Source database). For Only in Target records, the program generates the script with DELETE statements. Identical records or ones with repeated comparison keys are not included into the script.

Synchronization Script

D

When the script is executed, you can recompare the synchronized databases to check that they have the same data.

Data Compare speeds up data comparison and synchronization of two MySQL databases, saving your time and efforts.

Download a free 30-day evaluation version of the dbForge Studio for MySQL to automate your comparison and synchronization tasks.

For more information about the tool, please visit www.devart.com/dbforge/mysql/studio/.

Leave a Reply