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

Question

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)

Answer 1

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?

Answer 2

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

Answer 3

(it executes about 4 inserts per second)

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

Answer 4

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