SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Migration Scheme - from one mysql DB to another mysql DB

    Hello all,

    If possible, I would like to ask and have your help about the methods and procedures that should exist to accomplish the following task:
    I need to grab some data from one mySQL database with some specific table and field names, to another mySQL database with specific table and field names. The destination database tables are empty.

    What is a common procedure to do on those cases? Is there any? Several? What are the most common?
    If I need to be more specific in order to benefit from your help, please, let me know.


    Best regards,
    Márcio

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if both databases share same sever, you can use simple INSERT ... SELECT type of query.
    if not, just write a php script that connects to both servers and do your task

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    They are in the same host environment, but I suppose that, in order to query one database and then, another, I would need two dsn yes?
    Or can I accomplish that by using only mySQL without php ?

    The destination table have same required fields that their values are randomly defined, can we accomplish that with mysql only as well ?

    I really sorry for this questions, I realize that I'm so lost here, that I can't even found the right questions to proceed to the task. :s

    Thanks again,
    Márcio

    Note to previous post: when I said that the destination tables were empty I mean that they contain no data inside. But they have column names and field names of course. Those, I cannot change.

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the MySQL user has access to both DBs you can do it with a single query, along these lines:
    Code mysql:
    INSERT INTO new_db.new_table 
    (id, name, email, phone) 
    SELECT 
    id, name, email, phone 
    FROM old_db.old_table
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  5. #5
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would need two dsn yes?
    yes, nothing bad with it.
    just have 2 connection statements in your script. every mysql library allows that.

    But I hope Mike's example would be enough. The only thing to care of: user of db1 must have access to db2 too.

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Yes the example was clear some doubts. Thanks.

    One last question, just to orientate my thoughts a little:

    If, for some reason, the user can't be the same for both databases, still, there is no need to create a script that exports to xml or cvs, and then, iterate over the xml tree or something, right? This more complex work-flow operations, are reserved for those cases where the data-source of one side is of different nature of the data-source on the other side. Correct?


    Thank you both,
    Márcio

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You shouldn't need to do anything that complex.
    If the field names (the ones you want) on old and new tables are the same and you have PhpMyAdmin you could run a SELECT query on the source database, then export the result set as SQL, use a text editor to change the name of the table and then run that SQL on the new DB to populate the new table.

    Failing that, create a PHP script to connect to both databases and SELECT from one and INSERT into the other using PHP as an intermediate step (as a way of bridging two user accounts).
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it doesn't really matter.
    you have to create at least textual query from the fetched data. So, one more text conversion won't make it too complicate. but iterate over the xml tree sure unnecessary as you ca iterate over query results.

    Edit: Using PhpMyAdmin is very good idea.

  9. #9
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Couldn't be all good right?

    The table names and column names are, in fact, different.

    I can now see the options, and now it will be a question to dig in and do some tries.

    MySQL Migration Toolkit could also be a possibility?
    For some weird reason that I cannot explain, it hasn't been granted access to me on phpmyadmin webapplication.

    edit: assuming that I will, by hand, take care of all fields and table name differences. (they are not to many).

    I will also need to populate some fields with random data (depending on the field, the values could be numbers only or alphanumeric).
    Can we accomplish this on the mysql query as well, or a php functions needs to be used? (this is a more specific question that I can later on see, but still... if it is a quick answer, please let me know).

    Thanks,
    Márcio
    Last edited by oikram; Mar 13, 2010 at 10:27. Reason: Need to add some clarifications to the previous post. Sorry.

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My first example (using single query) will cope fine with different column names
    Code SQL:
    INSERT INTO NEW (id, full_name, telephone)
    SELECT id, concat(firstname, ' ', lastname), phone) FROM OLD

    For the random data you'll probably need PHP for that.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2009
    Posts
    1,006
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I rest my case for now, time to do the tries and see what I get.

    Thanks a lot for your time and share.


    Best Regards,
    Márcio


Bookmarks

Posting Permissions

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