How to back up MySQL or MariaDB databases remotely via SSH

Last updated on July 4, 2020 by Dan Nanni

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 a quick-and-easy command to do that:

$ 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 an one-shot command to achieve that:

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

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.