Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unhappy Unanswered: Data Migration between diffeent schemas

    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.

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Data Migration between diffeent schemas

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

    Originally posted by mahanti
    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.

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    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

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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.

    Originally posted by mahanti
    Some of the most frequent differences between 2 schemas are like below...

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    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

  6. #6
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    The mysql tools support blob and longblob column types.

    Originally posted by mahanti
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    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.

  8. #8
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    These tools can handle writting blob types to textfiles.

    Originally posted by mahanti
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •