How to Automatic Backup MySQL Database using SSH

Geeky Duck

8 years ago

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?

# mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD DATABASE_NAME > backup.sql

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 doBackup.sh

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

Edit the shell script

# sudo nano doBackup.sh

Paste the following code into the shell script

#!/bin/bash

ssh_User="ssh_username"
ssh_Host="ssh_host"
sql_User="sql_username" 
sql_Pass="msql_password" 

# Backup destination directory
DEST="/Users/GeekyDuck/db_backups"

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

# Database name to backup
DB ="mydatabase"

# Generate the backup path
FILE="$DEST/$DB.$NOW.sql"
 
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 dbBackup.sh

0 0 * * 0 /usr/local/bin/dbBackup.sh

Learn more: Automatic Backup MySQL Database to S3 bucket – Simple Disaster Recovery Plan