Knowledge Base

How to back up a MySQL database over SSH when remote connections are blocked

Updated on September 4, 2026

Overview

By the end of this article, a scheduled script on your web server will produce a dated backup file of your MySQL database every day and delete backups older than seven days. The article lists what you need before you start, then covers four stages: connecting to the server over Secure Shell (SSH), testing that mysqldump can reach the database, writing and testing the backup shell script, and automating it with a cron job. It closes with how to confirm the backup ran, how to protect the credentials the script contains, and answers to common questions.

Prerequisites

SSH access to your web server, with the username, hostname, and password or key.
MySQL database credentials: hostname, username, password, and database name.
A terminal program — Terminal or iTerm on macOS, or Command Prompt, PowerShell, or a Secure Shell client on Windows.
Permission to create files and schedule cron jobs on the server.

Stage 1 — Connect to your web server over SSH

Open your terminal program and connect to the server, substituting your own values:

ssh <ssh_username>@<webserver_hostname>

Enter your password when prompted. A shell prompt for the web server confirms you are connected.

Figure 1: Terminal window showing a completed SSH connection to the web server

Stage 2 — Test that mysqldump can reach the database

Before automating anything, confirm the server can reach the database and produce a dump. Run:

mysqldump -h <database_hostname> -u<database_username> -p <database_name> > test.sql

Omitting the password after -p makes MySQL prompt for it, which keeps the password out of your shell history. A large database may take several minutes. When the shell prompt returns with no error output, inspect the file with any text viewer:

less test.sql

If the file contains the dump contents, the connection works. Remove the test file:

rm test.sql

Figure 2-4: Terminal window showing mysqldump output in the test file

Stage 3 — Create the backup shell script

Step 1 — Create and protect the backup directory

Create a directory for the dump files, and add an .htaccess file so the backups cannot be reached over HTTP:

mkdir mysqldumps

echo "Deny from all" > mysqldumps/.htaccess

Step 2 — Store the credentials outside the script

Rather than placing the database password in the script, put it in a MySQL option file readable only by your user:

.my.cnf

Add the following, then save and close the file:

[client]
host=<database_hostname>
user=<database_username>
password=<database_password>

Restrict the file so no other account can read it:

.my.cnf

Step 3 — Write the script

Open a new script file:

nano mysqldump.sh

Add a line that deletes dump files older than seven days, then a mysqldump command for each database. The $(date +%Y-%m-%d) fragment puts the backup date in the filename:

#!/bin/sh

# Delete dump files older than seven days
find /path/to/mysqldumps/ -name '*-mysqldump-*' -mtime +7 -delete

# Create a dated dump of the database
mysqldump <database_name> > /path/to/mysqldumps/wordpress-mysqldump-$(date +%Y-%m-%d).sql

The find expression must match the filenames your mysqldump commands produce. In the example above, the files are named wordpress-mysqldump-<date>.sql, so the expression *-mysqldump-* matches them. To back up more than one database, add a mysqldump line for each and give every one a different filename so they do not overwrite each other.

Save and close the file. In nano, press Ctrl+O then Ctrl+X. In vim, press Esc, then type :wq.

Step 4 — Make the script executable and test it

chmod u+x mysqldump.sh
./mysqldump.sh

Open the mysqldumps directory and confirm a dated .sql file was created, then open the file and confirm it contains the dump data.

Figure 5: Terminal window showing the dated backup file inside the mysqldumps directory

Stage 4 — Automate the backup with cron

A cron job runs the script on a schedule without any action from you. Open your cron table:

crontab -e

A cron entry has five schedule fields followed by the command to run:

Field	Position	Allowed values
Minute	1	0–59
Hour	2	0–23
Day of month	3	1–31
Month	4	1–12
Day of week	5	0–6, where 0 is Sunday, or day names

An asterisk in a field means "every value". To run the backup every day at 2 a.m.:

mysqldump.sh

Save the file. Cron uses the time zone configured on the server, not your local time zone. Check it with:

timezone

Figure 6: Terminal window showing the crontab file with the scheduled backup entry

How to verify success

After the first scheduled run, open the mysqldumps directory and confirm a .sql file exists with the current date in its name. Open the file and confirm it contains the database dump. After more than seven days, confirm the oldest files have been removed automatically.

Frequently asked questions

What if my database credentials change?

Update the credentials in your MySQL option file at .my.cnf. The backup script itself does not need to change.

Can I back up more than one database?

Yes. Add another mysqldump command to the script for each database, and give each one a different output filename so the dumps do not overwrite each other. Confirm the find expression still matches all the filenames you use.

Why is my mysqldump file incomplete?

The dump most likely stopped on an error. Run the mysqldump command directly in your shell, outside the script, and check for error output. An incomplete dump can also mean the database is large enough that the process exceeded the maximum run time your host allows.

How do I know the scheduled backup ran?

Check the mysqldumps directory for a file with the current date in its name. If no file appears, run the script manually to confirm it works, then check that the cron entry uses the full path to the script.