How to Automatic Backup MySQL Database using SSH

You have learned “How to use SSH to access and manage MySQL database” in the previous article. Take a step further and you are about to automate the database backup process.

I hope you still remember how to dump a mysql database?


This command will dump your mysql database to backup.sql. Now, how to make it automatic backup the database every week? All you need is put the command into a shell script, and use a cron job to automate it.

Create a blank shell script

# cd /usr/local/bin
# sudo touch

The command first change your directory to /usr/local/bin and then created a blank shell script “”

Edit the shell script

# sudo nano

Paste the following code into the shell script



# Backup destination directory

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# Database name to backup
DB ="mydatabase"

# Generate the backup path
ssh $ssh_User@$ssh_Host mysqldump -u $sql_User -h $sql_Host -p$sql_Pass $DB > $FILE

Setup a Cron Job

# crontab -e

This command will open the cron tab editor. Add the line below to create a weekly cronjob that execute the

0 0 * * 0 /usr/local/bin/
Learn moreAutomatic Backup MySQL Database to S3 bucket – Simple Disaster Recovery Plan

2 thoughts on “How to Automatic Backup MySQL Database using SSH”

  1. How about a daily backup? Also, what if the server reboot? Will the cron job backup the mysql database automatically even after server reboot?

Leave a Reply

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

twenty + 3 =