1#@!#!123s

��JFIF�� 1#@!#!123s
: / opt / sharedrads / mysql /
Filename : mysql-genqlog.sh
back
#!/bin/bash
# Shared RADS Tools, written by Erik Soroka (eriks@imhadmin.net, ext 834)
# Functions in this script written by Jacob N (jacobn@inmotionhosting.com)

#load functions + banners
source /opt/sharedrads/radsfunctions.sh

function help() {
    echo
    echo "mySQL General Query Logging Manipulation Tool"
    echo " This script stops/starts logging on the fly"
    echo
    echo "usage: mysql-genqlog [option]"
    echo
    echo "options:"
    echo "     --stop       Stop resource intensive genq logging by redirecting query logs to /dev/null"
    echo "     --start      Start intensive genq logging and begin writing queries to disk in /var/log/"
    echo "     --status     Check the current status of logging on this server  (enabled or stopped)"
    echo "     --parse      Takes the last genq log recorded and parses it into /var/log/mysql_qps/"
    echo "     --clean      Cleans and removes all old general query data from /var/log/mysql_qps/"
    echo
    echo
}

function check_mysql_config() {

    echo -n "[*] Validating local my.cnf settings are compatible with this script " ; sleep 1
    grep -qE -- '^(log|general_log_file)\s*=\s*/var/log/MySQLGenLog' /etc/my.cnf

    if [ "$?" -gt "0" ]; then
        echo -ne "\033[31;1m[ ERROR ]\033[0m"
        echo
        grep "log=" /etc/my.cnf
        echo
        echo "[*] Automatically making necessary changes to my.cnf configuration file..."
        echo

        # Remove any old logging references in my.cnf
	sed -i -re '/^(log|general_log_file)\s*=/d' /etc/my.cnf

	# Add our logging line
        awk 'BEGIN {
	         RET=3
             } {
	     if ($5 ~ /5\.[15]\.[0-9]/) {
	         RET=1
             }
	     else if ($5 ~ /(4\.[01]|5\.0)/) {
	         RET=0
             }
             } END {
	         exit RET
             }' <<<"$(mysql -V)"

        VERSION_RET=$?

        if [ $VERSION_RET = "0" ]; then
            LOGDIRECTIVE='log'
        elif [ $VERSION_RET = "1" ]; then
            LOGDIRECTIVE='general_log_file'
        fi

	if [ -z "$LOGDIRECTIVE" ]; then
            echo "Unable to determine the correct syntax to configure logging"
            echo "for this version of MySQL. Exiting..."
            exit 1
        fi

        echo "${LOGDIRECTIVE}=/var/log/MySQLGenLog" >> /etc/my.cnf
        echo "[*] Ready to restart mySQL with the new settings, press ENTER to continue."
        read -r qwerty
        service mysql restart
        echo
    else
        echo -ne "   \033[32;1m[ OK ]\033[0m"
    fi
}

function status() {

    # Get local mysql version
    fullversion=$(mysqladmin variables | grep " version " | awk '{print $4}')

    # Get major and minor version of mysql
    majorv=$(echo "$fullversion" | awk -F'.' '{print $1}')
    minorv=$(echo "$fullversion" | awk -F'.' '{print $2}')

    # Determine running version and get the appropriate log variable
    if [[ "$majorv" = "4" ]] || [[ "$majorv" = "5" && "$minorv" = "0" ]] ; then
        genq=$(mysqladmin variables | grep " log " | awk '{print $4}')
    else
        genq=$(mysqladmin variables | grep " general_log " | awk '{print $4}')
    fi

        if [ "$genq" == "ON" ] || [ "$genq" == 1 ]; then
            if [ -f /var/log/MySQLGenLog ]; then
                echo
                echo "[*] CURRENT STATUS: General Query logging is enabled and writing to /var/log"
                echo
            else
                echo
                echo "[*] CURRENT STATUS: General Query logging is enabled and writing to /dev/null"
                echo
            fi

        else
            echo "[*] CURRENT STATUS: General Query logging is disabled on the mySQL server."
            echo "[*] Please check my.cnf settings and restart mySQL to have logging re-enabled."
        fi

}

function stop() {
    # First, we need to check to see if mySQL is already configured for us
    check_mysql_config

    # Remove symbolic link to current GenLog and point to /dev/null instead
    rm -rf /var/log/MySQLGenLog
    ln -s /dev/null /var/log/MySQLGenLog

    # Move GenLog into place suffixed with -month-day-hour
    if [ -f /var/log/mysql-generalquery.log ]; then
        mkdir -p /var/log/mysql_qps/logs/
        mv /var/log/mysql-generalquery.log /var/log/mysql_qps/logs/mysql-generalquery.log-$(date +%m-%d-%H)
    fi

    # Get local mysql version
    fullversion=$(mysqladmin variables | grep " version " | awk '{print $4}')
    # Get major and minor version of mysql
    majorv=$(echo "$fullversion" | awk -F'.' '{print $1}')
    minorv=$(echo "$fullversion" | awk -F'.' '{print $2}')

    # Determine running version and get the appropriate log variable
    if [[ "$majorv" = "5" && "$minorv" != "0" ]] ; then
        mysql -e "set global general_log = 'OFF';"
    elif [[ "$majorv" = "10" ]] ; then
	mysql -e "set global general_log = 0;"
    fi

    # Flush MySQL's logs, allows it to start writing to /dev/null instead of the file
    mysql -e "flush logs" 2>/dev/null

    echo
    echo "[*] Successfully stopped general query logging to disk - outputting to /dev/null."
    echo "[*] The log is saved in /var/log/mysql_qps/logs/mysql-generalquery.log-$(date +%m-%d-%H)"
    echo

}

function start() {
    # First, we need to check to see if mySQL is already configured for us
    check_mysql_config

    # Remove symbolic link to current MySQLGenLog
    rm -rf /var/log/MySQLGenLog

    # Move current MySQLGenLog into archives suffixed with -month-day-hour
    if [ -f /var/log/mysql-generalquery.log ]; then
        mkdir -p /var/log/mysql_qps/logs/
        mv /var/log/mysql-generalquery.log /var/log/mysql_qps/logs/mysql-generalquery.log-$(date +%m-%d-%H)
    fi

    # Create new MySQLGenLog
    touch /var/log/mysql-generalquery.log
    chown root:mysql /var/log/mysql-generalquery.log
    chmod 664 /var/log/mysql-generalquery.log

    # Create sybolic link to new MySQLGenLog
    ln -s /var/log/mysql-generalquery.log /var/log/MySQLGenLog

    # Flush MySQL's logs, allows it to start writing back to new file
    mysql -e "flush logs" 2>/dev/null

    # Get local mysql version
    fullversion=$(mysqladmin variables | grep " version " | awk '{print $4}')
    # Get major and minor version of mysql
    majorv=$(echo "$fullversion" | awk -F'.' '{print $1}')
    minorv=$(echo "$fullversion" | awk -F'.' '{print $2}')

    # Make sure the logging is set to the proper file
    mysql -e "set global general_log_file = '/var/log/MySQLGenLog';"

    # Determine running version and get the appropriate log variable
    if [[ "$majorv" = "5" && "$minorv" != "0" ]] ; then
        mysql -e "set global general_log = 'ON';"
    elif [[ "$majorv" = "10" ]] ; then
	mysql -e "set global general_log = 1;"
    fi

    echo
    echo "[*] Successfully started general query logging to disk @ /var/log/MySQLGenLog"
    echo
}

function parse() {
    # Find all MySQLGenLogs not parsed already, queryparser them out to a time formated file
    # mysql-generalquery.log-07-09-15 -> /var/log/mysql_qps/logs/07-09-15_parsed

    for log in $(ls -1 /var/log/mysql_qps/logs/ | grep mysql-generalquery.log-)
        do
            time=$(echo "$log" | cut -d- -f3,4,5)
            mkdir -p /var/log/mysql_qps/logs/parsed
            /opt/sharedrads/mysql/queryparser.py "/var/log/mysql_qps/logs/$log" > "/var/log/mysql_qps/logs/parsed/${time}_parsed"
            mv "/var/log/mysql_qps/logs/$log" /var/log/mysql_qps/logs/parsed/
    done

    # Grep out users from each parsed file, append them to that users file with timestamp
    # /var/log/mysql_qps/users/userna5

    for log in $(ls -1 /var/log/mysql_qps/logs/parsed/ | grep mysql-generalquery.log-)
        do
            time=$(echo "$log" | cut -d- -f3,4,5)

            for user in $(cat "/var/log/mysql_qps/logs/parsed/${time}_parsed" | grep -v root | awk '{print $1}')
                do
                    mkdir -p /var/log/mysql_qps/users/
                    echo -n "$time " >> "/var/log/mysql_qps/users/$user"
                    grep "$user" "/var/log/mysql_qps/logs/parsed/${time}_parsed" >> "/var/log/mysql_qps/users/$user"
            done
    done

    # Compile full stats for each user and drop into full summary
    for user in $(ls -1 /var/log/mysql_qps/users/ | grep -E -v "\.|\.\.|[0-9][0-9]|ALL|Access")
        do
                echo -n "$user " >> /var/log/mysql_qps/SUMMARY
                sed -e 's/qps//g' "/var/log/mysql_qps/users/$user" | awk 'BEGIN{QPS=0}{QPS=QPS+$4+$6+$8}END{print QPS" (Total)",$4" (Selects)",$6" (Updates)",$8" (Inserts)"}' >> /var/log/mysql_qps/SUMMARY
    done

    # Determine if we have a log file and what to do with it, if so...

    if [ "$log" == "" ]; then
        # no log file to parse.
        echo
        echo "[*] ERROR: Could not find a log file to parse."
        echo "[*] HINT: You need to run $0 --start first."
        echo
    else

        echo
        echo "[*] Successfully parsed the general query log and saved the data in /var/log/mysql_qps/"
        echo
        echo " - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "
        echo
        cd /var/log/mysql_qps/logs/parsed/ || exit 1
        ls -la "$log"
        echo "The above log file is no longer needed as the QPS stats have been extracted."
        echo -n "Would you like to remove this file now to save disk space? [Y/n]: "
        read -r YESORNO

        if [ "$YESORNO" == "n" ]; then
            mkdir -p /var/log/mysql_qps/logs/saved/
            mv "/var/log/mysql_qps/logs/parsed/$log" "/var/log/mysql_qps/logs/saved/$log"
            echo
            echo "[*] $log was moved to /var/log/mysql_qps/logs/saved/ for review."
            echo
        else
            rm -f "$log"
            echo
            echo "[*] Removed $log, operations complete.  Please review parsed data now."
            echo
        fi
    fi
}


function clean() {
    rm -rf /var/log/mysql_qps
    echo
    echo "[*] Successfully cleaned all old data from /var/log/mysql_qps/."
    echo
}

##################################################
##   ACTUAL FUNCTIONALITY GETS CALLED HERE
##################################################

echo

    if [ "$RADSCOLORS" == "off" ]; then
            radsbanner-nocolor
    else
            radsbanner
    fi

if [ -z "$1" ]; then
    help
    exit 0
fi

case "$1" in
    --help)
        help
        exit 1;
        ;;
    --status)
        status
        exit 1;
        ;;
    --stop)
        stop
        exit 1;
        ;;
    --start)
        start
        exit 1;
        ;;
    --parse)
        parse
        exit 1;
        ;;
    --clean)
        clean
        exit 1;
        ;;
    *)
	;;
esac


## EOF ##