Paul Chris Jones's coding blog
PHP
JS

A bash script to backup MySQL databases automatically

27th February 2020

I wrote a bash script that backs up all my MySQL databases:

The script

echo logging in 
ssh -tt your_host_username@your_hostname <<'ENDSSH' 
stty -echo
USER="your_MySQL_username"
PASSWORD="your_MySQL_password"
BACKUP_DIRECTORY=~/backups/MySQL
BACKUP_FOLDER=$(date +%F_%H-%M-%S)

rm -rf $BACKUP_DIRECTORY/*
mkdir $BACKUP_DIRECTORY/$BACKUP_FOLDER

databases=`MySQL -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
stty echo

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "MySQL" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        MySQLdump -u $USER -p$PASSWORD --databases $db -r $BACKUP_DIRECTORY/$BACKUP_FOLDER/$db.sql
    fi
done
echo "logging out"
exit
ENDSSH
stty echo
echo "downloading files, please wait"
sleep 5
rsync --recursive your_host_username@your_hostname:backups/MySQL/ ~/Bluehost_backups/Databases/
echo "done"

Explanation

Here's how the script works. First, the script logs in to the server. Notice that the server doesn't ask for a password. That's because I'm using SSH keys.

Then the script removes the last backup from the server:

rm -rf $BACKUP_DIRECTORY/*

This is so we don't annoy the hosting company by keeping loads and loads of backups on the server.

Next, the script makes a new folder where it will place the latest backup files:

mkdir $BACKUP_DIRECTORY/$BACKUP_FOLDER

Then the script downloads all the MySQL databases.

MySQLdump -u $USER -p$PASSWORD --databases $db -r $BACKUP_DIRECTORY/$BACKUP_FOLDER/$db.sql

Note that for this part to work, you need to create a MySQL user and give that user download permissions for all the databases. With Bluehost, you do this by going to the cPanel and selecting MySQL Databases.

Then the script logs out of the server. Finally, it uses rsync to download the backup from the server to your computer.

Making the script run automatically

You can tell your computer to run the script automatically every day by doing

crontab -e

then adding

0 23 * * * ~/backup_bluehost_databases.sh

to the bottom of the crontab file.

Leave a comment