1#@!#!123s

: / opt / dedrads / 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/dedrads/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
		CHECKMYCNF=$(egrep ^log=/var/log/MySQLGenLog /etc/my.cnf)

		if [ "$CHECKMYCNF" == "" ]; 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 '/log=\/var\/log/d' -i /etc/my.cnf
	                # Add our logging line
			echo "log=/var/log/MySQLGenLog" >> /etc/my.cnf
			echo "[*] Ready to restart mySQL with the new settings, press ENTER to continue."
			read 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" ]; 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

		# 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

		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/dedrads/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/ | egrep -v "\.|\.\.|[0-9][0-9]|ALL|Access"`
		do
		        echo -n "$user " >> /var/log/mysql_qps/SUMMARY
		        cat /var/log/mysql_qps/users/$user | sed 's/qps//g' | 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/
			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 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 ##