CODEWITHSUNDEEP
mysqldata-transfer
ed209
ed209

Reputation: 11303

Quickest way to transfer data from MySQL DB 1 to MySQl DB 2

I have a new database, similar to the old one but with more columns and tables. So the data on the old table is still useable and needs transferring.

The old database is on a different server to the new one. I want to transfer the data from one database to the other.

I have navicat, but it seems to take forever using the host to host data transfer. Also downloading a sql file then executing that takes too long also (it executes about 4 inserts per second).

The downloaded SQL file is about 40mb (with complete insert statements). The final one would probably be 60mb to 80mb.

What's the best way to transfer this data? (a process I will need to repeat a few times for testing)

Upvotes: 1

Views: 1225

Answers (4)

topper 9
topper 9

Reputation: 58

Cody thank you for the direction. For some reason it did not work for me, but the below did on my redhat linux server:

(recipient machine) mysql -u [username] -p -h localhost database < database.sql

(source machine) I just used php myadmin

Is there a command that can be run to pull the DB from another server something along the lines of: mysqldump -u [username] -p -h [host address] [dbname] > [filename].sql Thanks

Upvotes: 0

Tim
Tim

Reputation: 20777

(it executes about 4 inserts per second)

That sounds more like there's something wrong with your database.. Are you sure that's alright?

Upvotes: 0

Cody Caughlan
Cody Caughlan

Reputation: 32748

Doing a mysqldump on the source machine and then slurping it in on the other side, even on a 40-100MB file is well within reason. Do it from the command line.

(source machine)
mysqldump -u user -p password database > database.sql

..transfer file to recipient machine...

(recipient machine)
mysql -u user -p password database < database.sql

Upvotes: 4

Sampson
Sampson

Reputation: 268462

Can you not transfer only a portion of the data for testing first? Then, later, transfer the entire thing when you're satisfied with test-results?

Upvotes: 0

Related Questions