Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Feb 14, 2003

Synchronizing Your MySQL Databases Using a Free MySQL Admin Tool - SQLyog

By DatabaseJournal.com Staff

by Insanely Great

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.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM