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 :

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 :

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 🙂

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

1. Open sqlyog

2. Click New button to make new connection

3. Fill database connection information

4. Example of successful connection

5. Click Powertols -> Structure Synchronization Tool

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

7. Click Compare Now

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  😉

Happy diff-ing 🙂