Moving large mySQL database
 

Hi All, this is not exactly an e-class question, but I think this section would provide the best answer.

First off, I have a 3gb database and no shell / ssh access to the source machine. I have root access on the target. I have tried using the provided export methods (cpanel backups!) but they do not produce a viable database dump.

Short of stopping mysql, creating a tar of the database and copying it over to the target, I am at a loss, and as mentioned I do not have this level of access.

Is there a way to pull the database to the source machine directly from a remote mysql connection? Or some other method I haven't mentioned? Something like a manual one-shot replicate?

Thank You!

Could you run a phpMyAdmin on your box and connect it to the db you want to copy? Then you could export the db thru phpMyAdmin...

Can you connect to the db server with a local mysql client maybe? Probably the server is not configured to allow that.

https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
mysqldump will create a valid backup and you can give it a remote host to access.
You will need the mysql root (aka dba) acct to backup all the DBs OR the owner acct for each db (ie schema) you want to backup.

Thanks all, after posting, I did remember that mysqldump did allow for the host argument. Using this method I was able to get a good dump. cPanel dumps were no good, although they were almost as big, mysqldump was the winner.