Copying MySQL databases from one server to other remote machine is task every webmaster has to do very often if not daily, and if your databases are huge, following command comes very handy to do this job without first downloading and then uploading the dump file.
Also, phpMyAdmin and other MySQL clients fail while importing big dump files due to timeout issues.
Following MySQL command basically pipes the data from mysqldump from one server directly into the other database instead of just dumping it into a file and then manually uploading it to the other server . ALso the uploading may stop if the file is too big.
The first host is FROM where you want to copy and destination is where you want to copy TO. Login to shell using SSH and run the following command by providing the DB name and DB user name and password of both servers.
mysqldump --opt --compress --user=USERHERE --password=PASSWORD.HERE --host=SOURCE.HOST.HERE.OR.localhost SOURCE_DB_NAME | mysql --user=USER.HERE --password=PASSWORD.HERE --host=DESTINATION.HOST.IP -D DESTINATION_DB_NAME -C DESTINATIONS_DB_NAME
You can also add number of other options in this above MySQL command from here.
You may get following error on running the command from shell.
ERROR 1130 (00000): Host 'x.x.x.x' is not allowed to connect to this MySQL server mysqldump: Got errno 32 on write
If you get above error, you need to allow the dumping MySQL server IP in the destination server. In cPanel, you can easily do that from “Remote Database Access Hosts” manager. Just add the dumping Dsevrer Ip there and that will do the job.
Hope that helps.