#!/bin/bash

#MySQL option: -m
#+Client dependencies: Bash, OpenSSH (daemon), mysqldump
#+Server dependencies: Bash, OpenSSH (client)
#+Create a file at /etc/mysql/mysql.cred which contains the MySQL credentials as such:
#user=someuser
#password=somepass
#+Make sure only your backupuser account can read this: chown backupuser:backupuser /etc/mysql/mysql.creds && chmod 660 /etc/mysql/mysql.creds
#+Use the following create statement to create a backup user in MySQL:
#CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'CHANGEPASSHERE';
#+Use the following grant statement to allow the user to back up the data:
#GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to backupuser@localhost IDENTIFIED BY 'CHANGEPASSHERE';FLUSH PRIVILEGES;
#+MySQL slaves also need to add an additional grant:
#GRANT SUPER ON *.* to backupuser@localhost IDENTIFIED BY 'CHANGEPASSHERE';FLUSH PRIVILEGES;
#+These lines may be useful for backing up a slave, do your research before using this and know how your servers are set up.
#ssh $SSHUSER@$MYSQLSRV "mysqladmin --user='$MYSQLUSER' --password='$MYSQLPASS' stop-slave" || _printerr "ERROR - $LINENO - Unable to stop MySQL slave replication on $MYSQLSRV." 1>> $LOG 2>> $LOG #Only needed for slave servers,
#ssh $SSHUSER@$MYSQLSRV "mysqladmin --user='$MYSQLUSER' --password='$MYSQLPASS' start-slave" || _printerr "ERROR - $LINENO - Unable to start MySQL slave replication on $MYSQLSRV." 1>> $LOG 2>> $LOG #Only needed for slave servers,
#MYSQLRSYNCFILES=( "/etc/my.cnf" "/var/lib/mysql/master.info" "/var/lib/mysql/relay-log.info")
#for EACHFILE in "${MYSQLRSYNCFILES[@]}";do
#scp -q $SSHUSER@$MYSQLSRV:$EACHFILE /backup/someserver/$EACHFILE || _printerr "ERROR - $LINENO - Unable to scp MySQL slave file $EACHFILE on $MYSQLSRV." 1>> $LOG 2>> $LOG
#done

#MySQL configuration
MYSQLSRV="Viridian" #The hostname of the MySQL server.
MYSQLUSER=$(awk -F'=' '$1 ~ /user/ { print $2 }' /etc/mysql/mysql.cred)
MYSQLPASS=$(awk -F'=' '$1 ~ /pass/ { print $2 }' /etc/mysql/mysql.cred)

if [ "$MYSQLOPT" = 1 ]; then #MySQL section
echo "$($TIME) - Backing up MySQL on $MYSQLSRV" | tee -a $LOG
  for EACHMYSQLDIR in "MySQL" "MySQL/Temp" "MySQL/Daily" "MySQL/Weekly" "MySQL/Monthly" "MySQL/Yearly"; do
    if [ ! -d $BKUPDIR/$MYSQLSRV/$EACHMYSQLDIR ];then
      mkdir -p $BKUPDIR/$MYSQLSRV/$EACHMYSQLDIR 1>>$LOG 2>>$LOG || _printerr "ERROR - $LINENO - Unable to create MySQL backup directory $EACHMYSQLDIR for $MYSQLSRV in $BKUPDIR." 1>>$LOG 2>>$LOG
    fi
  done
  if ! grep -i $MYSQLSRV /home/$SSHUSER/.ssh/known_hosts > /dev/null; then
    echo "Host key for $MYSQLSRV:" 1>> /home/$SSHUSER/.ssh/known_hosts
    ssh-keyscan -t rsa,dsa $MYSQLSRV 1>> /home/$SSHUSER/.ssh/known_hosts 2>>$LOG || _printerr "ERROR - $LINENO - Unable to add host key for $MYSQLSRV to known host key list." 2>>$LOG
  fi
  if ssh $SSHUSER@$MYSQLSRV : 1>> $LOG 2>> $LOG;then
    ssh $SSHUSER@$MYSQLSRV "echo 'show databases\g' | mysql --user='$MYSQLUSER' --password='$MYSQLPASS' | sed '/^information_schema\|^Database\|lost+found/d'" | (while read -r EACHDBNAME;do
    echo "Working on $EACHDBNAME"
    DUMPDAY=$(date +%F)
    DUMPTIME=$(date +%H-%M-%S)
    ssh $SSHUSER@$MYSQLSRV -n "mysqldump --user='$MYSQLUSER' --password='$MYSQLPASS' $EACHDBNAME | gzip" 1> $BKUPDIR/$MYSQLSRV/MySQL/Temp/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz || _printerr "ERROR - $LINENO - Unable to back up MySQL database $EACHDBNAME on $MYSQLSRV."
    if [ -s $BKUPDIR/$MYSQLSRV/MySQL/Temp/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz ];then #If the DB backup exists and is non-zero in size, copy the daily and move on.
      echo "Total bytes received for database $EACHDBNAME: $(/bin/ls $BKUPDIR/$MYSQLSRV/MySQL/Temp/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz -l | cut -d' ' -f5)"
      mv $BKUPDIR/$MYSQLSRV/MySQL/Temp/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz $BKUPDIR/$MYSQLSRV/MySQL/Daily/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz || _printerr "ERROR - $LINENO - Unable to copy new daily MySQL backup for $MYSQLSRV."
      ls -1 -t $BKUPDIR/$MYSQLSRV/MySQL/Daily/$EACHDBNAME* | awk --assign=NUMDAILYDUMPFILES=$NUMDAILYDUMPFILES '{ if (NR > NUMDAILYDUMPFILES) {print}}' | xargs rm -f ; [ $(echo "${PIPESTATUS[*]}" | sed 's/ //g') -eq "0" ] || _printerr "ERROR - $LINENO - Unable to remove old daily MySQL backup for $MYSQLSRV."
      if [ $(date +%a) = "Sat" ];then #Copy the weekly
    cp $BKUPDIR/$MYSQLSRV/MySQL/Daily/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz $BKUPDIR/$MYSQLSRV/MySQL/Weekly/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz || _printerr "ERROR - $LINENO - Unable to copy new weekly MySQL backup for $MYSQLSRV."
    ls -1 -t $BKUPDIR/$MYSQLSRV/MySQL/Weekly/$EACHDBNAME* | awk --assign=NUMWEEKLYDUMPFILES=$NUMWEEKLYDUMPFILES '{ if (NR > NUMWEEKLYDUMPFILES) {print}}' | xargs rm -f ; [ $(echo "${PIPESTATUS[*]}" | sed 's/ //g') -eq "0" ] || _printerr "ERROR - $LINENO - Unable to remove old weekly MySQL backup for $MYSQLSRV."
      fi
      if [ $(date +%d) = "01" ];then #Copy the monthly
    cp $BKUPDIR/$MYSQLSRV/MySQL/Daily/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz $BKUPDIR/$MYSQLSRV/MySQL/Monthly/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz || _printerr "ERROR - $LINENO - Unable to copy new monthly MySQL backup for $MYSQLSRV."
    ls -1 -t $BKUPDIR/$MYSQLSRV/MySQL/Monthly/$EACHDBNAME* | awk --assign=NUMMONTHLYDUMPFILES=$NUMMONTHLYDUMPFILES '{ if (NR > NUMMONTHLYDUMPFILES) {print}}' | xargs rm -f ; [ $(echo "${PIPESTATUS[*]}" | sed 's/ //g') -eq "0" ] || _printerr "ERROR - $LINENO - Unable to remove old monthly MySQL backup for $MYSQLSRV."
      fi
      if [ $(date +%j) = "001" ];then #Copy the yearly
    cp $BKUPDIR/$MYSQLSRV/MySQL/Daily/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz $BKUPDIR/$MYSQLSRV/MySQL/Yearly/$EACHDBNAME-on-$DUMPDAY-at-$DUMPTIME.sql.gz || _printerr "ERROR - $LINENO - Unable to copy new yearly MySQL backup for $MYSQLSRV."
    ls -1 -t $BKUPDIR/$MYSQLSRV/MySQL/Yearly/$EACHDBNAME* | awk --assign=NUMYEARLYDUMPFILES=$NUMYEARLYDUMPFILES '{ if (NR > NUMYEARLYDUMPFILES) {print}}' | xargs rm -f ; [ $(echo "${PIPESTATUS[*]}" | sed 's/ //g') -eq "0" ] || _printerr "ERROR - $LINENO - Unable to remove old yearly MySQL backup for $MYSQLSRV."
      fi
    else
      _printerr "ERROR - $LINENO - Backup of DB $EACHDBNAME failed (zero length backup file or it doesn't exist), keeping old backup (if one exist)."
    fi
    done) 1>>$LOG 2>>$LOG
  else
    _printerr "ERROR - $LINENO - SSH to $MYSQLSRV failed." 1>>$LOG 2>>$LOG
  fi
fi