#!/bin/sh
#
# MySQL backup script
#
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
#
# Set MySQL root password, backup location and remove exit statement
MYSQLPASS="myjinga11sql"
BACKUPLOC="/data/backups"
MYSQLBACKUPLOC="$BACKUPLOC/mysql"
#mkdir -p $MYSQLBACKUPLOC
BACKUPFILE1="$MYSQLBACKUPLOC/backup-data-${NOW}.sql.gz"
BACKUPFILE2="$MYSQLBACKUPLOC/backup-user-${NOW}.sql.gz"
SQLFILE="/tmp/eu_${NOW}.sql"
INFILE="/tmp/in_${NOW}.sql"
RUNFILE="/tmp/run_${NOW}.sql"
#
# Backup Data
/usr/bin/mysqldump -uroot --password=${MYSQLPASS} --events --ignore-table=mysql.event --all-databases | gzip > $BACKUPFILE1
chmod 600 $BACKUPFILE1
#
# Backup Users
echo "SELECT DISTINCT CONCAT(\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user where user<>'root' ORDER BY 1;" > $SQLFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} --skip-column-names mysql < $SQLFILE  > $INFILE
cat $INFILE | grep show > $RUNFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} --skip-column-names mysql < $RUNFILE | gzip > $BACKUPFILE2
chmod 600 $BACKUPFILE2
rm $SQLFILE $INFILE $RUNFILE
#
/usr/bin/find $MYSQLBACKUPLOC/backup*.sql.gz -mtime +7 -exec rm {} \;
#
exit 0