How to manually transfer large databases between boxes

Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"

Top Level » MySQL / MariaDB

How to manually transfer large databases between boxesLast Modified: Dec 4, 2007, 2:02 pm
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 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.

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST