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

Articles & Code Snippets

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

Archives

Articles & Snippets