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