MySQL

MySQL is primarily an RDBMS and therefore ships with no GUI tools to administer MySQL databases or manage data contained within. Users may use the included command-line tools,or download MySQL frontends from various parties that have developed desktop software and web applications to manage MySQL databases, build database structure, and work with data records.

Official

The official MySQL Workbench is a free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structure. MySQL Workbench replaces the previous package of software, MySQL GUI Tools. Similar to other third-party packages, but still considered the authoritative MySQL frontend, MySQL Workbench lets users manage the following:

• Database design & modeling
• SQL development — replacing MySQL Query Browser
• Database administration — replacing MySQL Administrator

MySQL Workbench is available in two editions, the regular free and open source Community Edition which may be downloaded from the MySQL website, and the proprietaryStandard Edition which extends and improves the feature set of the Community Edition.

Third-party

Several other third-party proprietary and free graphical administration applications (or "front ends") are available that integrate with MySQL and enable users to work with database structure and data visually. Some well-known front ends, in alphabetical order, are:

• Adminer — a free MySQL front end written in one PHP script, capable of managing multiple databases, with many CSS skins available.
• DBEdit — a free front end for MySQL and other databases.
• dbForge GUI Tools — a set of tools for database management that includes separate applications for schema comparison and synchronization, data comparison and synchronization, and building queries.
• HeidiSQL — a full featured free front end that runs on Windows, and can connect to local or remote MySQL servers to manage databases, tables, column structure, and individual data records. Also supports specialised GUI features for date/time fields and enumerated multiple-value fields.^[17]
• Navicat — a series of proprietary graphical database management applications, developed for Windows, Macintosh and Linux.
• Other available proprietary MySQL front ends include Aqua Data Studio, dbForge Studio for MySQL, Epictetus, Oracle SQL Developer, SchemaBank, SQLyog, SQLPro SQL Client, Toad, Toad Data Modeler
• Open Office — The database part of Open Office can manage MySQL databases. (You must install all of the Open Office suite. It is free and open source.)
• phpMyAdmin — a free Web-based front end widely installed by Web hosts worldwide, since it is developed in PHP and is included in the convenient LAMP stack andWAMP software bundle installers.

Command-line

MySQL ships with a suite of command-line tools for tasks such as querying the database, backing up data, inspecting status, performing common tasks such as creating a database, and many more. A variety of third-party command-line tools is also available, including:

• Maatkit, a set of power-user tools written in Perl
• MySQL Sandbox, a set of scripts for quickly starting server instances for testing and development

source: http://en.wikipedia.org/wiki/MySQL

It is possible to synchronize a model in MySQL Workbench with a live database. By default, the synchronization process will change the live database to be the same as the model, but this is configurable during the synchronization process.

MySQL Workbench enables control over the direction of synchronization, and the objects synchronized, in a completely flexible way. You can choose to synchronize only certain tables, allow synchronization to the live database only, allow synchronization from the live database to the model only, or a combination of directions. In effect you have complete control as to whether the synchronization is unidirectional or bidirectional, and which objects exactly are subject to synchronization. This is all controlled in the Select Changes to Apply page of the synchronization wizard:

In the above example the live database consists of table1, table2 and table3. In MySQL Workbench an additional table, table4, has been created, along with a relationship between it and table3. Further, table5exists in the live database, but not in the model. The actions that are configured to occur would result in table3being altered (to include the relationship with table4), table4 being created and table5 being dropped, in the live database. It is possible to reconfigure this though.

The next example shows how the direction of synchronization can be changed:

In this case the synchronization direction has been changed so that rather than the default action of table5 being dropped from the live database, it will be incorporated into the MySQL Workbench model.

For convenience the wizard provides three additional buttons to allows synchronization directions to be applied to a group of selected changes. The Update Model button causes the selected changes to only be applied to the model itself:

In the above example table7 would be added to the model.

The Ignore button causes the selected changes to be ignored, no synchronization will take place for those changes:

In the above example no changes would take place.

The Update Source button causes the selected changes to only update the live database:

In this example table6 would be added to the live database and table7 would be dropped from the live database.

It is also possible to control individual changes by clicking the arrows. Clicking an arrow causes it to change between the three available synchronization directions:

In the above example table6 will be created in the live database, and table7 will be created in the model.

source: http://dev.mysql.com/doc/workbench/en/wb-database-synchronization.html

The Synchronization Manager (SyncMan) provides a platform for database-agnostic data replication and synchronization.  The platform is built on Java technologies such as the Java Messaging Service (JMS) and the Java Database Connectivity framework (JDBC).  The core SyncMan server runs on any of the supported MaxDB platforms, and the synchronized databases can be any combination of database and OS, so long as there exists a JDBC driver for the combination.

Another feature of the Synchronization Manager is that you may choose to replicate any portion of your database to remote systems.  Combined with SyncMan's support for offline devices and advanced collision resolution algorithm, this provides a convenient way to disseminate information to potentially embedded devices "in the field."

The following table compares the SyncMan with a few of the other common replication/synchronization systems available today in the market.

Supported OS Platforms

This field indicates the Operating Systems under which the synchronization engine is supported.

Disk or In-Memory?

This field indicates whether the database stores its memory solely in memory, or whether is can make use of disk-based storage as well.

Uni-directional or Bi-directional?

This field indicates whether the connections to the data sources are uni-directional, meaning that the engine is only capable of synchronizing records to slaves, or bi-directional, meaning that each data source is capable of updating the records and re-synchronizing with the other data sources.

Supported data sources

This field indicates the data sources that the particular synchronization engine is capable of peering with.

Offline Operation?

This field indicates whether the synchronization engine will continue to operate if connectivity to its peers is lost.

Atomic synchronization?

This field indicates whether the synchronization of the data sources happens atomically. 

	Supported OS Platforms?	Disk or In-Memory?	Uni-directional or Bi-directional	Supported data sources	Offline Operation?	Atomic synchronization?
MaxDB Synchronization Manager	Supported MaxDB platforms	Data source dependant	Data source dependant	Any data source supported by JDBC, including MySQL, MaxDB, PostgreSQL, Sybase, MS SQL Server, DB2, and Oracle	Yes	No
Oracle Distributed Database architecture	Supported Oracle platforms	Disk + In-Memory	Bi-directional	Oracle	No	Yes
MySQL Replication	Supported MySQL platforms	Disk + In-Memory	Bi-directional	MySQL	No	No
MySQL Cluster	Supported MySQL platforms	In-Memory only	Bi-directional	MySQL	No	Yes
DB2 ICE	Linux only	Disk + In-Memory	Bi-directional	DB2	No	Yes

 

In order to begin using the Synchronization Manager, you will need to have a Java environment installed that includes a 1.4 J2RE (Runtime Environment) and version 1.4 of the J2EE (Enterprise Edition), as well as the SWT libraries provided by version 3.0 of the Eclipse platform.  The following installation HOW-TO will walk you through installing and configuring this software.

A typical installation requires about 3G of free disk space (including temporary space for installers) and roughly an hour and a half of time (not including download time).  The following HOW-TO has been tested on Debian and SuSE GNU/Linux and is expected to work equally as well on Redhat Enterprise, Fedora, Slackware, Mandrake, and any other sufficiently recent distribution of GNU/Linux. 

source: http://dev.mysql.com/tech-resources/articles/syncman/index.html

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.