D7net
Home
Console
Upload
information
Create File
Create Folder
About
Tools
:
/
opt
/
dedrads
/
mysql
/
Filename :
mysql-genqlog.sh
back
Copy
#!/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 ##