Hi !!

I am new to My SQL. I have got a requirement where data existing in one schema has to be migrated to another. Both schemas look alike except some minor changes in the table definitions here and there.

Does MySQL provide some kind of tools by default for these sort of things or we need to go for third party tools? Please let me know.

Thankx in Advance,
Mahanti.

If you give an example, it would be easier to tell if mysqldump and mysqlimport are capable.

Some of the most frequent differences between 2 schemas are like below...

1)

< id int(7) unsigned zerofill NOT NULL auto_increment,
< project_id int(7) unsigned zerofill NOT NULL default '0000000',
---
> id int(7) unsigned NOT NULL auto_increment,
> project_id int(7) unsigned NOT NULL default '0',

2)

< user_id int(7) unsigned zerofill NOT NULL default '0000000',
< bug_id int(7) unsigned NOT NULL default '0'
< );
---
> user_id int(7) unsigned NOT NULL default '0',
> bug_id int(7) unsigned NOT NULL default '0',
> PRIMARY KEY (user_id,bug_id)
> ) TYPE=MyISAM;

Schemas belong to two different versions of application mantis.

Thanks in Advance

If your two databases are on the same MySQL server, then you should be able to use a statement like

INSERT INTO destdb.desttable
SELECT * FROM sourcedb.sourcetable;

It looks like you shouldn't have to do anything special to make the transfer go smoothly.

If your databases are on separate servers, then it will require you to dump the data into text files, move them to the destination server and make a connection to import them. The commands look like:

SELECT * FROM sourcedb.sourcetable INTO OUTFILE 'C:\\datafile.dat';

move the data file to the destination server
connect to the server

LOAD DATA INFILE 'C:\\datafile.dat' INTO TABLE destdb.desttable;

Of course, if you issue "USE sourcedb;" or "USE destdb;" then you won't need to prefix the table names with the database names.

Hi !!

Thanks a lot for the information.

Yes, I understood that we can use default MySQL tools for data migration from one schema to another.

But, the problem i may face is, one or two of the tables do have blob/longblob columns. And i am not clear to what extent these MySQL tools support longblob column types. Can anyone throw some light on this please?

Thanks in Advance

The mysql tools support blob and longblob column types.

Thanks a lot. That's good to know.

To be more clear, does it mean,

if we use "mysqldump", blob, longblob types also can be written from database to text files?

Similarly, using "Load Data" we can read them from textfiles to database ?

Thanks for your time.

These tools can handle writting blob types to textfiles.