Mysqldump over SSH

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 in a remote machine’s authorized_keys.

3 thoughts on “Mysqldump over SSH”

  1. Very useful! Thank you for that one!
    For bigger databases it may be smart to save the remote database for later use :

    ssh -C {ssh.user}@{remote_host} mysqldump -u {remote_dbuser} --password={remote_dbpassword} {remote_dbname} | gzip > {dbname}.sql.gz

  2. Thanks. Very nice tip! I will use this to save gziped bakups and then rotate them!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>