Script to Backup Mysql Databases

Posted by Cătălin on Mon, 31 Oct 2016

Shell Script to Backup Mysql Databases, you can keep as many versions as you like

Create a cron with this script to backup your databases:

#!/bin/sh

# List of databases to be backed up separated by space
dblist="DATABASE1 DATABASE2 DATABASE3"

# Directory for backups
backupdir=/PATH/FOR/MYSQL/BACKUPS/gz

# Number of versions to keep
numversions=7

# Full path for MySQL hotcopy command (find location with: locate mysqldump)
hotcopycmd="/usr/local/mysql/bin/mysqldump --user=YOUR-USER --password=YOUR-PASSWORD --lock-tables --lock-tables --databases"

# Create directory if needed
mkdir -p "$backupdir"
if [ ! -d "$backupdir" ]; then
   echo "Invalid directory: $backupdir"
   exit 1
fi

# Hotcopy begins here
echo "Dumping MySQL Databases..."
RC=0
for database in $dblist; do
   echo
   echo "Dumping $database ..."
   mv "$backupdir/$database.gz" "$backupdir/$database.0.gz" 2> /dev/null
   $hotcopycmd $database | gzip > "$backupdir/$database.gz"

   RC=$?
   if [ $RC -gt 0 ]; then
     continue;
   fi

   # Rollover the backup directories
   rm -fr "$backupdir/$database.$numversions.gz" 2> /dev/null
   i=$numversions
   while [ $i -gt 0 ]; do
     mv "$backupdir/$database.`expr $i - 1`.gz" "$backupdir/$database.$i.gz" 2> /dev/null
     i=`expr $i - 1`
   done
done

if [ $RC -gt 0 ]; then
   echo "MySQL Dump failed!"
   exit $RC
else
   # Hotcopy is complete. List the backup versions!
   ls -l "$backupdir"
   echo "MySQL Dump is complete!"
fi
exit 0

web design