What is SSH

SSH (Secure Shell) is a method of secure data transmission over a network.

Administrators often encounter it when connecting to a Linux server by entering the command ssh userName@hostAddress, which allows them to execute commands on the remote server.

However, it is primarily a protocol, and on top of it, you can not only transmit data but also create SSH tunnels, allowing applications to transfer data over SSH.

How can SSH connections be used for MySQL backups?

1. Backup remote MySQL database over SSH — You can backup a remote DB and get a dump of this DB locally.
2. Backup local MySQL to a remote server over SSH — You can backup a local MySQL DB and send this backup over SSH to a remote server.

Why backup through SSH if direct connection is possible?

mysqldump has a --host parameter that allows you to backup a remote server. So why use SSH?

But this is only permissible for private networks, as exposing the MySQL port on the internet is unsafe, leading to the following risks:

1. Brute-force attacks — Establishing a connection to the DB and refusing it happens very quickly, allowing for rapid spamming of authorization attempts to guess the password.
2. Exploit — There might be vulnerabilities in the DB authorization that allow access to the DB. Regular DB updates help avoid such risks, but not from zero-day exploits.
3. DDOS attacks — Your DB is responsible for authorization, and a large number of authorization attempts can overload your DB.

Good practice is not to allow access to the MySQL port (3306), so direct connection using mysqldump is only possible in closed networks.

Does it work on Windows?

Starting with Windows 10 and Windows Server 2019 — OpenSSH received official support and can be easily installed. Here is a simple installation guide in a couple of PowerShell commands.

However, PowerShell does not support on-the-fly compression, so it does not allow for full utilization of backup through SSH.

Examples

Note that in the examples below, creating temporary files is not required — the backup is created right where it’s needed, and sometimes it’s even compressed on the fly.

Backup Remote MySQL Database Over SSH

Backup

ssh root@ipaddress "mysqldump -u dbuser -ppassword sakila " > sakila.sql

Restore

ssh root@ipaddress "mysql -u dbuser -ppassword sakila " < sakila.sql

Backup remote MySQL over SSH with remote compression

ssh root@ipaddress "mysqldump -u dbuser -ppassword sakila | gzip -9" > sakila.sql.gz

Restore remote MySQL over SSH with remote uncompression

cat sakila.sql.gz | ssh root@157.230.19.242 "gunzip | mysql -u dbuser -ppassword db1"

In these examples, compression is performed on the same server where the MySQL DBMS is located. This reduces network load but can put a strain on the server with the DBMS. An alternative is to compress the backup after receiving it.

Backup remote MySQL over SSH with local compression

ssh root@157.230.19.242 "mysqldump -u root -p****** sakila" | gzip > sakila.sql.gz

Restore remote MySQL over SSH with local uncompression

gzip -d < sakila.sql.gz | ssh root@157.230.19.242 "mysql -u root -p****** sakila"

On Windows

Backup remote MySQL over SSH on Windows

ssh alex@20.237.171.192 "\`"C:\Program Files\MySQL\MySQL Server 8.2\bin\mysqldump\`" -u root -p****** sakila" > sakila.sql

Restore remote MySQL over SSH on Windows

Get-Content -Path "1.sql" | ssh alex@20.237.171.192 "\`"C:\Program Files\MySQL\MySQL Server 8.2\bin\mysql\`" -u root -p****** db1"

mysqldump through an SSH tunnel

There’s no need to run mysqldump on the remote server and fetch the result back to your own server. Instead, you can create an SSH tunnel that allows you to interact with the database on the remote server as if it were a local server.

Simply configure port forwarding. As an example, let’s redirect from port 3306 to 3307. This can be done using the following command:

ssh -f -N -L 3307:localhost:3306 root@111.122.133.14

Now you can make a backup as if it were a local server

mysqldump -u new_user -ppassword -h 127.0.0.1 -P 3307 --all-databases

How to backup DB via SSH using SqlBak

To create backups of MySQL DBs via SSH using SqlBak, select SSH Tunnel when creating a connection.

In the Windows client, select the appropriate option when creating a connection.

Backup MySQL to a remote server over SSH

You can actually create a backup locally via SSH and transfer it to another server. Of course, you can use the scp command separately.

mysqldump -u root -p****** sakila > sakila.sql 
scp sakila.sql root@157.230.19.242:~/sakila.sql

BUT, you can make this backup without temporary files by transferring it on the fly.

mysqldump -u root -p****** sakila | ssh root@157.230.19.242 "cat > sakila_backup.sql"

As well as restore on the fly

ssh root@157.230.19.242 "cat sakila.sql" | mysql -u root -p****** sakila

The same but with compression, for backup

mysqldump -u root -p****** sakila | gzip | ssh root@157.230.19.242 "cat > sakila.sql.gz"

For restore

ssh root@157.230.19.242 "cat sakila.sql.gz" | gunzip | mysql -u root -p****** sakila

How to Send a Backup via SSH Using SqlBak

When choosing a destination, select FTP, in the Protocol parameter select SFTP, and enter the host, login, and password.