26 Mar 2019 | Hosting | 0

Cron job to backup all MySQL databases in your VPS host

With this script, you can backup your all your MySQL databases. You need to have SSH access to your hosting. You can also extend functionality to backup to another remote location.

#!/bin/bash

# Specify number of backups to keep in days
NUMBER_OF_BACKUPS=3

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
IGNORE_LIST="SELECT schema_name FROM information_schema.SCHEMATA WHERE schema_name NOT LIKE '% %' AND  schema_name NOT LIKE '%-%' AND schema_name != 'information_schema' AND schema_name != 'mysql' AND schema_name != 'performance_schema';"

echo "Reading databases..."

databases=`$MYSQL -e "${IGNORE_LIST}" | tr -d "| " | grep -v schema_name`

today=`date +%Y-%m-%d`
mkdir $today

# Dump databases
for db in $databases; do
   echo "Dumping $db..."
   $MYSQLDUMP $db > /backup/db/$today/$db.sql
done

# Clean up old backups to save space
# Day before number of backups to keep
dataset_date=`date`
day_to_delete=`date -d "$dataset_date - $((NUMBER_OF_BACKUPS+1)) days" +%Y-%m-%d`

rm -rf $day_to_delete

# todo Generate a log
# todo Notify user of the backup event

https://gist.github.com/jgmuchiri/8445b27d86d6fa7aa556f381f5c9f6c2

Login as root and create a cron job to backup the databases (mine runs every day at 3 am)

crontab -e

0 3 * * * /backup/db/mysql-dump.sh > /dev/null 2>&1

The script will create a directory by date, dump the databases there, then delete older backup a day before your defined number of backups to keep.

Optionally, on your local machine or another remote host, you can use scp tool to copy those folders as you wish over ssh. That's beyond the scope of this post.

Backup path must not be accessible from web!

If you would like to explore how to use scp, let me know!