Pipe a gzipped mysql dump over ssh

I had a problem with a server without any space left on the drive, but still had to dump a database. So what i did was pipe the output of `mysqldump` to gzip and then send it over ssh to another server.

`mysqldump` outputs clear text so lets send that to gzip. We need to tell gzip to output the result to stdout so we can pipe it to ssh. We can do this by adding the -c option. Last we need to send it over ssh. You can pipe it over ssh by simply doing `ssh user@host ‘cat > ~/dump.sql.gz’

At the end you’ll end up with a string like this,

5 thoughts on “Pipe a gzipped mysql dump over ssh

  1. iforce2d

    Nice. You can also use ssh to copy data from master to slave to prepare for replication, by running something like this on the slave:

    ssh user@masterhost “mysqldump yourdbname” > mysql

  2. Kyle Jones

    I needed something similar, I wouldn’t consider this good practice, but it sure is handy: mysqldump –opt –add-drop-table -Q -u localuser –password=localpassword localdatabase |gzip -c |ssh remoteuser@remoteserver ‘gunzip -c | mysql -u remoteuser –password=remotepassword remotedatabase’

  3. Damian

    In multicore/multiprocessor environments you should consider to use “pigz” instead of “gzip”. It’s a massive speed improvement in most cases.

  4. Need help

    Please , help me
    I just googling & found this site
    after I do :
    mysqldump -u MYSQL_USERNAME -p YOUR_DATABASE | gzip -c > db.tar.gz

    now I cant unpack result db.tar.gz

Leave a Reply

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