Migration Scheme - from one mysql DB to another mysql DB

PHP
1

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

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.

3

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

4

If the MySQL user has access to both DBs you can do it with a single query, along these lines:


INSERT INTO new_db.new_table 
(id, name, email, phone) 
SELECT 
id, name, email, phone 
FROM old_db.old_table
5

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

Thanks,

Yes the example was clear some doubts. Thanks. :slight_smile:

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

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).

8

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

Couldn’t be all good right? :wink:

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

10

My first example (using single query) will cope fine with different column names


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.

11

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