How to manually transfer large databases between boxes

If you have an excessivly large database, or you want to keep multipled databases backed up or synced, you can transfer them manually with a basic command.   In this example we will connect to a remote box, and downoad a remote database to a local database.

1) First create the empty database and all users
2) make sure you have an access host on the remote box to allow a remote connection.
3) you can either upload (send) or download it (get), doesn't make much difference (assuming you've setup your access host)

So, to download from a remote box to a local db, you'd use:

mysqldump -uremoteuser -premotepass -hremote.host.com dbname | mysql -ulocaluser -plocalpass dbname



mysqldump will connect to the remote box, with the remote user/host/pass and dump the output to stdout, thus the | (pipe) will redirect the stdout to the stdin of the 2nd part, which runs all sql commands from that output into the local database.


Was this article helpful?

mood_bad Dislike 0
mood Like 0
visibility Views: 7351