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.


# Specify number of backups to keep in days

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

# Clean up old backups to save space
# Day before number of backups to keep
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

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/ > /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!