Feb 062011
 

I often transfer production MySql databases to my local machine for development and testing purposes, usually I do this over SSH for security reasons.
to automate the process I combined all the commands in one line.

ssh -C {ssh.user}@{remote_host} mysqldump -u {remote_dbuser} --password={remote_dbpassword} {remote_dbname} | mysql -u {local_dbuser} --password={local_dbpassword} -D {local_dbname}

How does it work ?
I run mysqldump command remotely using ssh than I pass the output to a local mysql command using the pipe, to speed up the transfer I passed -C to ssh to compress the data.

please note this method works good on fast connections and small databases , I didn’t test it with big database, as you need to have your identity.pub in a remote machine’s authorized_keys.

Get Adobe Flash player