How to back up MySQL or MariaDB databases remotely via ssh

Question: I want to back up a MySQL (MariaDB) database running on a remote server. Is there a way to back up and dump a remote MySQL (MariaDB) database over SSH?

mysqldump is a command-line utility for backing up MySQL (MariaDB) databases. In order to back up a remote database, you can send mysqldump command remotely over SSH, and pipe its output back to the localhost. For efficiency, the output of mysqldump can be compressed before the pipe.

Here is the command:

$ ssh -C remoteuser@remote_host mysqldump -u MYSQL_USER -p'MYSQL_PASSWORD' YOUR_DATABASE | gzip -c | cat > ~/backup.sql.gz

Alternatively, you can ssh to the remote host first. Then run mysqldump from there, and pipe the result directly to your host over SSH.

Here is the command:

remoteuser@remote_host:$ mysqldump -u MYSQL_USER -p'MYSQL_PASSWORD' YOUR_DATABASE | gzip -c | ssh you@your_host 'cat > ~/backup.sql.gz'

Download this article as ad-free PDF (made possible by your kind donation): 
Download PDF

Subscribe to Ask Xmodulo

Do you want to receive Linux related questions & answers published at Ask Xmodulo? Enter your email address below, and we will deliver our Linux Q&A straight to your email box, for free. Delivery powered by Google Feedburner.

Support Xmodulo

Did you find this tutorial helpful? Then please be generous and support Xmodulo!

Leave a comment

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