#!/bin/bash

# user & password
USER=mysqluser
PASS=mysqlpass

# number of backups to be saved
KEEP=100

# dir to backup
DIR=/home/mascotte/backup/mysql

NOW=$(date +"%Y-%m-%d")
DBS="$(mysql -u $USER -p$PASS -Bse 'show databases')"

for db in $DBS
do

  BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`
  while [ $BACKUPS -ge $KEEP ]
  do
    ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f
    BACKUPS=`expr $BACKUPS - 1`
  done

  FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz
  mysqldump -u $USER -p$PASS $db | gzip -9 > $FILE

done

exit 0