#!/bin/bash
#Nifty: Install backupscript for MySQL database server

randpw(){ < /dev/urandom tr -dc A-Za-z0-9 | head -c12;echo;}


# Detect plesk
PLESK=`ls -la /etc/psa/.psa.shadow`
PLESK=$?

if [ "$PLESK" -eq "0" ]; then
        echo "Plesk detected.."
        mysqluser="admin"
        mysqlpass=`cat /etc/psa/.psa.shadow`
elif [ -f "/root/.mysql" ]; then
	echo "MySQL password file found.."
	mysqluser="root"
	mysqlpass=`cat /root/.mysql`
else
        echo "No Plesk detected"
        echo "NOTE! If there is no root password set, ctrl-c now and set one!!!"

	echo -en "MySQL root password: "
        read mysqlpass
        mysqluser="root"
fi

# Make dir for backup
if [ ! -d "/var/mysqlbackup" ]; then
        mkdir /var/mysqlbackup
        chmod 700 /var/mysqlbackup
fi


# Test if mysql backup user exists
echo "USE mysql; SELECT User,Host FROM user WHERE User = 'backup';" | mysql -u$mysqluser -p$mysqlpass -B -N |grep ^backup > /dev/null

if [ "$?" -eq "0" ]; then
        echo "A 'backup' user exists in MySQL - aborting!"
        exit 1
else
        echo "The backup user does not exist - continuing"
fi

BACKUPPASS=`randpw`
echo "FLUSH PRIVILEGES; CREATE USER 'backup'@'localhost' IDENTIFIED BY '$BACKUPPASS'; GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD, PROCESS, SHOW VIEW ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;" | mysql -u$mysqluser -p$mysqlpass
echo "Backup user created with password $BACKUPPASS"

# Make dir for the script
if [ ! -d "/scripts" ]; then
        mkdir /scripts
fi

# Write the backupscript

cat > /scripts/mysqlbackup <<EOF
BACKUPDIR=/var/mysqlbackup
BACKUPPASS="$BACKUPPASS"
TIMESTAMP=\`date +%Y%m%d-%H%M\`
IGNOREDB="information_schema performance_schema sys"

if [ ! -d "\$BACKUPDIR" ]; then
        mkdir \$BACKUPDIR
fi

rm -rf \$BACKUPDIR/*

cd \$BACKUPDIR || exit no backupdir

for DB in \`echo "SHOW DATABASES;" | mysql -u backup -p\$BACKUPPASS |sed 1d\`
do
        IGNORE=0
        for IGN in \$IGNOREDB
        do
                [ "\$IGN" = "\$DB" ] && IGNORE=1
        done

        if [ "\$IGNORE" = "0" ]
        then
                cd \$BACKUPDIR
                nice -n10 mysqldump --max_allowed_packet=512M --single-transaction -R --opt -u backup -p\$BACKUPPASS \$DB |gzip > \$DB.sql.gz
        fi
done
EOF

echo "Wrote backupscript to /scripts/mysqlbackup"

# Set permissions on /scripts/mysqlbackup
if [ -f /scripts/mysqlbackup ]; then
    echo "Setting permissions to 600 on /scripts/mysqlbackup"
    chmod 700 /scripts/mysqlbackup
fi


# Create crontab entry
echo "0 2 * * * /bin/bash /scripts/mysqlbackup > /dev/null 2>&1" >> /var/spool/cron/root
echo "Placed cron entry for backupscript."

echo "###
#
# Install completed.
# Please do a testrun: /bin/bash /scripts/mysqlbackup && ls -la /var/mysqlbackup
#
###"

du -sh /var/lib/mysql
