Hello,
I want to move or backup 30 millions rows from a MySQL database to another one (also MySQL).
Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

Hello,
I want to move or backup 30 millions rows from a database to another one.
Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

Hello,
I want to move or backup 30 millions rows from a database to another one.
Please give me some advices on how can I do this process in the fastest way AND especially I need to know the required time to do this process. Thanks.

if you are planning to move data from a table in one db to another, try bcp..

since it involves two different databases, I would think the best way is to export the data to a file from the source and LOAD into the Target.

At the source :
EXPORT TO table1.ixf of ixf select * from table1

On the target:
LOAD from table1.ixf of ixf savecount 1000000 insert into table1

It is not easy to predict the time that will be needed. It will depend on a number of factors like the table and index defn, row size, your disk system, db and dbm config etc.

HTH

Sathyaram

are you moving the whole database or just part of it?

Nobody can tell you the required time. You will have to try it and see, and it will probably vary some from one execution to the next as network activity, CPU usage, and drive space change.

Do we get to rearchitect the system as well?

Is this one table? Is the database on another server (I would hope so).

what's the color of the hair of the women sitting closest to you?

What is the table type? With MyISAM you can copy the files directly, but you don't want to do that with MySQL running.

How long it takes? Well, it depends on your amount of memory, number and speed of CPUs, version of MySQL, your operating systems, number and speed of disks, buses, connections to SAN or fileservers, ...

I would use mysqldump to dump table data from server A directly to server B. Just redirect the output to the MySQL client connected to your target server and go grab some lunch. With reasonable equipment, you should probably be able to expect 10,000 rows/second, so it might take an hour or so.

What next? Are you going to ask whether the carpet matches the drapes?

(In the office, of course...)

Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

-PatP

Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

-PatP

Ok, I'll byte. Holy_Joy must be doing an assignment that requires them to find the best database for this problem. So far they've asked for DB2, Microsoft SQL, and MySQL. The teacher must have done the legwork for Oracle as an example for the class!

-PatP

Aha, interesting. But what kind of assignment is that? Seems pretty useless to me

lol PAT PHELAN!
I hope you byted... But I don't think u have an idea of the problem here, maybe in few month u'll probably guess
Anyway thanks for replying!!!!!!!!! it was funny