#!/bin/bash
 
DATE=$(date +'%Y-%m-%d');
MYSQLUSER="root";
MYSQLPASS="";
BACKUPDIR="/var/backups/mysql";
MAKEGZIP=1;
 
# Check backup dir and create if not exists
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
fi
 
# Check mysql binary
MYSQLBIN=$(which mysql);
MYSQLBINTEST=$(echo $MYSQLBIN | wc -w);
if [ $MYSQLBINTEST -eq 0 ]; then
 echo "$0: There is no 'mysql' application, install 'mysql-client' and try again. Exiting..."
 exit 1
fi
 
# Use password or not
MYSQLPASSLEN=$(echo $MYSQLPASS | wc -c);
if [ "$MYSQLPASSLEN" -gt 1 ]; then
 pMYSQLPASS="-p$MYSQLPASS";
else
 pMYSQLPASS="";
fi
 
# Remove old backups
find $BACKUPDIR -ctime +14 -exec rm -f {} \;
 
echo "$0: $DATE: Making mysql backups..."
 
# Create backups
for DATABASE in $(/usr/bin/mysql -u $MYSQLUSER $pMYSQLPASS -e 'show databases' -B -r -s | /bin/egrep -v "(information_schema|performance_schema)"); do
  echo "$0: Dumping $DATABASE..."
  if [ $MAKEGZIP -eq 1 ]; then
    BACKUPFILENAME="$DATABASE-$DATE.sql.gz"
  else
    BACKUPFILENAME="$DATABASE-$DATE.sql"
  fi
  BACKUPFILEFULLNAME="$BACKUPDIR/$BACKUPFILENAME"
  if [ $MAKEGZIP -eq 1 ]; then
    /usr/bin/mysqldump -u $MYSQLUSER $pMYSQLPASS --events --add-drop-database --add-drop-table --add-locks $DATABASE | gzip > $BACKUPFILEFULLNAME
  else
    /usr/bin/mysqldump -u $MYSQLUSER $pMYSQLPASS --events --add-drop-database --add-drop-table --add-locks $DATABASE > $BACKUPFILEFULLNAME
  fi
done
 
echo "$0: SQL Backups done"