Mysql Dump directly into SFTP or into another server

Posted on

Question :

I just have a quick question : I have a MariaDb Database ( with 1Tb of data) running on a dedicated server, I only have 2gb left on my storage and I would like to backup a few databases on my SFTP server or on another dedicated server directly since I don’t have any storage left to store them temporary.


Answer :

On a separate machine with perhaps 2TB of disk space free:

 mysqldump -u ... -p... -h mysql_server ... > dumpfile.sql

It will take time. Be sure to get the correct parameters — routines, etc may not be dumped by default.

I suggest 2TB on the target machine because sometimes the dump is bigger than the database. (Sometimes it is smaller.) A “0” in a BIGINT takes 8 bytes in the database, but only 2 bytes in the dump. Some other things are 3x bigger in the dump.

In the future, be sure to keep more free disk space than the largest table. This is to allow for ALTER, which can sometimes help keep away from “out of disk space”.

If the “S” part of SFTP is what you are after, check on “SSL” settings in MySQL.

(I don’t know if SFTP can be used to “pipe” data; I suspect it can only handle “files”, which you don’t have.)

Leave a Reply

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