Diff database structure with sqlyog

When requirement for information stored in database grow, old table structure need to adjust for flexibility.

Common task to do in  normal cycle :

1. Build new application in other place

2. Synchronize database ( tables)

3. Upload application.

Second step ( synchronize tables) often  painful when a lot of changes made at home and need to sync with server environment.

A handy tool to sync database table I often use is sqlyog.

For example I have two database :

db1 (at server)

db2 (at home)

table structure has changed.

In db1, table testing has this fields :

 Diff database structure with sqlyog

CREATE TABLE `testing` (
`id` tinyint(3) NOT NULL auto_increment,
 `name` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
in db2, table testing has this fields :

 Diff database structure with sqlyog

CREATE TABLE `testing` (
`id` tinyint(3) NOT NULL auto_increment,
  `name` varchar(15) NOT NULL default '',
  `location` varchar(25) NOT NULL default '',
  `message` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

as you see, testing in db1 and testing in db2  is different. Need to see whats change in easy way icon smile Diff database structure with sqlyog

Here’s how to make diff of table structure using sqlyog :

1. Open sqlyog

 Diff database structure with sqlyog

2. Click New button to make new connection

 Diff database structure with sqlyog

3. Fill database connection information

 Diff database structure with sqlyog

4. Example of successful connection

 Diff database structure with sqlyog

5. Click Powertols -> Structure Synchronization Tool

 Diff database structure with sqlyog

6. Choose database that need to sync from available database in dropdown

 Diff database structure with sqlyog

7. Click Compare Now

 Diff database structure with sqlyog

8. Click Sync [File] … >> for sync db1 to db2

Here the result  :

/* Alter table in Second database */
alter table `db2`.`testing`,
    drop column `location`,
    drop column `message`;

9. Click << Sync[File] … for sync db2 to db1

Here the result  :

/* Alter table in First database */
alter table `db1`.`testing`,
    add column `location` varchar(25) NOT NULL   after `name`,
    add column `message` text NOT NULL   after `location`;

Use this file or copy paste to phpmyadmin window for quick sync.

How about diff the data?

I don’t know yet, when I found the way. I’ll make new  post  icon wink Diff database structure with sqlyog

Happy diff-ing icon smile Diff database structure with sqlyog