MySQLDump To a Remote Server

Posted on January 2nd, 2009 in Encryption,General,Linux System Administration,MySQL,Security by Brandon

I was running out of disk space on a server today. The server had a large database table that was no longer used, so I wanted to archive it and then drop the table. But the server didn’t have enough disk space to dump it out to disk before copying it off to a remote server for archiving.

The first thought was to run mysqldump dump on the destination machine, and to access the database over the network. That however, doesn’t compress or encrypt the data. Plus I would have had to create a mysql user with permission to access the database remotely.

The solution I came up with worked out well: mysqldump directly to the remote host with this command:

mysqldump <DATABASE_NAME> [mysqldump options] | gzip -c | ssh user@remotehost "cat > /path/to/some-file.sql.gz"

That pipes the mysqldump command through gzip, then to through and SSH connection. SSH on the remote side runs the ‘cat’ command to read the stdin, then redirects that to the actual file where I want it saved.

2 Responses to 'MySQLDump To a Remote Server'

Subscribe to comments with RSS or TrackBack to 'MySQLDump To a Remote Server'.

  1. Math said,

    on October 21st, 2009 at 10:32 am

    Is there is any option to change the source path of the backup file? For example

    mysqldump /home/estho/mydb > estho.sql

  2. Brandon said,

    on October 21st, 2009 at 1:22 pm

    @math, you would just change the /path/to/some-file.sql.gz to point wherever you would like the file saved on the remote server.

    Your example doesn’t appear to be related to dumping it to a remote machine. If you are trying to save it to that directory, you would just run

    mysqldump mydatabase > /home/estho/mydb/estho.sql

Post a comment