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?
- Backup remote MySQL database over SSH — You can backup a remote DB and get a dump of this DB locally.
- 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:
- 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.
- 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.
- 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.
