in Database

Diff database structure with sqlyog

Recommended VPS for your FreeBSD or Linux Server + Custom Iso Allowed (Free $20 credit for limited time, grab it now by click Here)

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 🙂