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.
Hi,
Is there is any option to change the source path of the backup file? For example
mysqldump /home/estho/mydb > estho.sql
@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