#!/bin/sh ######################################################################### # # # MySQL performance tuning primer script # # Writen by: Matthew Montgomery # # Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/) # # Version: 1.5-r2 Released: 2009-04-21 # # Licenced under GPLv2 # # # ######################################################################### ######################################################################### # # # Little known feature: 1st argument is execution mode # # # # Usage: ./tuning-primer.sh [ mode ] # # # # Available Modes: # # all : perform all checks (default) # # prompt : prompt for login credintials and socket # # and execution mode # # mem, memory : run checks for tunable options which # # effect memory usage # # disk, file : run checks for options which effect # # i/o performance or file handle limits # # innodb : run InnoDB checks /* to be improved */ # # misc : run checks for that don't categorise # # well Slow Queries, Binary logs, # # Used Connections and Worker Threads # ######################################################################### # # # Set this socket variable ONLY if you have multiple instances running # # or we are unable to find your socket, and you don't want to to be # # prompted for input each time you run this script. # # # ######################################################################### socket= cecho () ## -- Function to easliy print colored text -- ## # Color-echo. # Argument $1 = message # Argument $2 = color { export black='\e[0m\c' export boldblack='\e[1;0m\c' export red='\e[31m\c' export boldred='\e[1;31m\c' export green='\e[32m\c' export boldgreen='\e[1;32m\c' export yellow='\e[33m\c' export boldyellow='\e[1;33m\c' export blue='\e[34m\c' export boldblue='\e[1;34m\c' export magenta='\e[35m\c' export boldmagenta='\e[1;35m\c' export cyan='\e[36m\c' export boldcyan='\e[1;36m\c' export white='\e[37m\c' export boldwhite='\e[1;37m\c' local default_msg="No message passed." # Doesn't really need to be a local variable. message=${1:-$default_msg} # Defaults to default message. color=${2:-$black} # Defaults to black, if not specified. echo -e "$color" echo -e "$message" tput sgr0 # Reset to normal. echo -e "$black" return } print_banner () { ## -- Banner -- ## cecho "\t\c " $black cecho "-- MYSQL PERFORMANCE TUNING PRIMER --" $boldblue cecho "\t - By: Matthew Montgomery -" $black } ## -- Find the location of the mysql.sock file -- ## check_for_socket () { if [ -z "$socket" ] ; then # Use ~/my.cnf version if [ -f ~/.my.cnf ] ; then cnf_socket=$(grep ^socket ~/.my.cnf | awk -F \= '{ print $2 }' | head -1) fi if [ -S "$cnf_socket" ] ; then socket=$cnf_socket elif [ -S /var/lib/mysql/mysql.sock ] ; then socket=/var/lib/mysql/mysql.sock elif [ -S /var/run/mysqld/mysqld.sock ] ; then socket=/var/run/mysqld/mysqld.sock elif [ -S /tmp/mysql.sock ] ; then socket=/tmp/mysql.sock else if [ -S "$ps_socket" ] ; then socket=$ps_socket fi fi fi if [ -S "$socket" ] ; then echo UP > /dev/null else cecho "\c" cecho "No valid socket file \"$socket\" found!" $boldred cecho "The mysqld process is not running or it is installed in a custom location." $red cecho "If you are sure mysqld is running, execute script in \"prompt\" mode or set " $red cecho "the socket= variable at the top of this script" $red exit 1 fi } check_for_plesk_passwords () { ## -- Check for the existance of plesk and login using it's credentials -- ## if [ -f /etc/psa/.psa.shadow ] ; then mysql="mysql -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)" mysqladmin="mysqladmin -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)" else mysql="mysql" mysqladmin="mysqladmin" # mysql="mysql -S $socket" # mysqladmin="mysqladmin -S $socket" fi } check_mysql_login () { ## -- Test for running mysql -- ## is_up=$($mysqladmin ping 2>&1) if [ "$is_up" = "mysqld is alive" ] ; then echo UP > /dev/null # echo $is_up elif [ "$is_up" != "mysqld is alive" ] ; then cecho "\n\c" cecho "Using login values from ~/.my.cnf\n" cecho "- INITIAL LOGIN ATTEMPT FAILED -\n" $boldred if [ -z $prompted ] ; then find_webmin_passwords else return 1 fi else cecho "Unknow exit status" $red exit -1 fi } final_login_attempt () { is_up=$($mysqladmin ping 2>&1) if [ "$is_up" = "mysqld is alive" ] ; then echo UP > /dev/null elif [ "$is_up" != "mysqld is alive" ] ; then cecho "- FINAL LOGIN ATTEMPT FAILED -\n" $boldred cecho "Unable to log into socket: $socket" $boldred exit 1 fi } second_login_failed () { ## -- create a ~/.my.cnf and exit when all else fails -- ## cecho "Could not auto detect login info!\n" cecho "Found Sockets: \n$found_socks\n" cecho "Using: $socket" $red read -p "Would you like to provide a different socket?: [y/N] " REPLY case $REPLY in yes | y | Y | YES) read -p "Socket: " socket ;; esac read -p "Do you have your login handy ? [y/N] : " REPLY case $REPLY in yes | y | Y | YES) answer1='yes' read -p "User: " user read -rp "Password: " pass if [ -z $pass ] ; then export mysql="$mysql -S$socket -u$user" export mysqladmin="$mysqladmin -S$socket -u$user" else export mysql="$mysql -S$socket -u$user -p$pass" export mysqladmin="$mysqladmin -S$socket -u$user -p$pass" fi ;; *) cecho "\nPlease create a valid login to MySQL" cecho "Or, set correct values for 'user=' and 'password=' in ~/.my.cnf" ;; esac cecho "\n\c" read -p "Would you like me to create a ~/.my.cnf file for you? [y/N] : " REPLY case $REPLY in yes | y | Y | YES) answer2='yes' if [ ! -f ~/.my.cnf ] ; then umask 077 echo -e "[client]\nuser=$user\npassword=$pass\nsocket=$socket" > ~/.my.cnf if [ "$answer1" != 'yes' ] ; then exit 1 else final_login_attempt return 0 fi else cecho "\n~/.my.cnf already exists!\n" $boldred read -p "Replace ? [y/N] : " REPLY if [ "$REPLY" = 'y' ] || [ "$REPLY" = 'Y' ] ; then echo -e "[client]\nuser=$user\npassword=$pass\socket=$socket" > ~/.my.cnf if [ "$answer1" != 'yes' ] ; then exit 1 else final_login_attempt return 0 fi else cecho "Please set the 'user=' and 'password=' and 'socket=' values in ~/.my.cnf" exit 1 fi fi ;; *) if [ "$answer1" != 'yes' ] ; then exit 1 else final_login_attempt return 0 fi ;; esac } find_webmin_passwords () { ## -- populate the .my.cnf file using values harvested from Webmin -- ## cecho "Testing for stored webmin passwords:\c" if [ -f /etc/webmin/mysql/config ] ; then user=$(grep ^login= /etc/webmin/mysql/config | cut -d "=" -f 2) pass=$(grep ^pass= /etc/webmin/mysql/config | cut -d "=" -f 2) if [ $user ] && [ $pass ] && [ ! -f ~/.my.cnf ] ; then cecho "Setting login info as User: $user Password: $pass" touch ~/.my.cnf chmod 600 ~/.my.cnf echo -e "[client]\nuser=$user\npassword=$pass" > ~/.my.cnf cecho "Retrying login" is_up=$($mysqladmin ping 2>&1) if [ "$is_up" = "mysqld is alive" ] ; then echo UP > /dev/null else second_login_failed fi echo else second_login_failed echo fi else cecho " None Found\n" $boldred second_login_failed fi } ######################################################################### # # # Function to pull MySQL status variable # # # # Call using : # # mysql_status \'Mysql_status_variable\' bash_dest_variable # # # ######################################################################### mysql_status () { local status=$($mysql -Bse "show /*!50000 global */ status like $1" | awk '{ print $2 }') export "$2"=$status } ######################################################################### # # # Function to pull MySQL server runtime variable # # # # Call using : # # mysql_variable \'Mysql_server_variable\' bash_dest_variable # # - OR - # # mysql_variableTSV \'Mysql_server_variable\' bash_dest_variable # # # ######################################################################### mysql_variable () { local variable=$($mysql -Bse "show /*!50000 global */ variables like $1" | awk '{ print $2 }') export "$2"=$variable } mysql_variableTSV () { local variable=$($mysql -Bse "show /*!50000 global */ variables like $1" | awk -F \t '{ print $2 }') export "$2"=$variable } float2int () { local variable=$(echo "$1 / 1" | bc -l) export "$2"=$variable } divide () { # -- Divide two intigers -- # usage="$0 dividend divisor '$variable' scale" if [ $1 -ge 1 ] ; then dividend=$1 else cecho "Invalid Dividend" $red echo $usage exit 1 fi if [ $2 -ge 1 ] ; then divisor=$2 else cecho "Invalid Divisor" $red echo $usage exit 1 fi if [ ! -n $3 ] ; then cecho "Invalid variable name" $red echo $usage exit 1 fi if [ -z $4 ] ; then scale=2 elif [ $4 -ge 0 ] ; then scale=$4 else cecho "Invalid scale" $red echo $usage exit 1 fi export $3=$(echo "scale=$scale; $dividend / $divisor" | bc -l) } human_readable () { ######################################################################### # # # Convert a value in to human readable size and populate a variable # # with the result. # # # # Call using: # # human_readable $value 'variable name' [ places of precision] # # # ######################################################################### ## value=$1 ## variable=$2 scale=$3 if [ $1 -ge 1073741824 ] ; then if [ -z $3 ] ; then scale=2 fi divide $1 1073741824 "$2" $scale unit="G" elif [ $1 -ge 1048576 ] ; then if [ -z $3 ] ; then scale=0 fi divide $1 1048576 "$2" $scale unit="M" elif [ $1 -ge 1024 ] ; then if [ -z $3 ] ; then scale=0 fi divide $1 1024 "$2" $scale unit="K" else export "$2"=$1 unit="bytes" fi # let "$2"=$HR } human_readable_time () { ######################################################################## # # # Function to produce human readable time # # # ######################################################################## usage="$0 seconds 'variable'" if [ -z $1 ] || [ -z $2 ] ; then cecho $usage $red exit 1 fi days=$(echo "scale=0 ; $1 / 86400" | bc -l) remainder=$(echo "scale=0 ; $1 % 86400" | bc -l) hours=$(echo "scale=0 ; $remainder / 3600" | bc -l) remainder=$(echo "scale=0 ; $remainder % 3600" | bc -l) minutes=$(echo "scale=0 ; $remainder / 60" | bc -l) seconds=$(echo "scale=0 ; $remainder % 60" | bc -l) export $2="$days days $hours hrs $minutes min $seconds sec" } check_mysql_version () { ## -- Print Version Info -- ## mysql_variable \'version\' mysql_version mysql_variable \'version_compile_machine\' mysql_version_compile_machine if [ "$major_version" = '3.23' ] || [ "$major_version" = '4.0' ] ; then cecho "MySQL Version $mysql_version $mysql_version_compile_machine is EOL please upgrade to MySQL 4.1 or later" $boldred else cecho "MySQL Version $mysql_version $mysql_version_compile_machine" fi } post_uptime_warning () { ######################################################################### # # # Present a reminder that mysql must run for a couple of days to # # build up good numbers in server status variables before these tuning # # suggestions should be used. # # # ######################################################################### mysql_status \'Uptime\' uptime mysql_status \'Threads_connected\' threads queries_per_sec=$(($questions/$uptime)) human_readable_time $uptime uptimeHR cecho "Uptime = $uptimeHR" cecho "Avg. qps = $queries_per_sec" cecho "Total Questions = $questions" cecho "Threads Connected = $threads" echo if [ $uptime -gt 172800 ] ; then cecho "Server has been running for over 48hrs." cecho "It should be safe to follow these recommendations" else cecho "Warning: \c" $boldred cecho "Server has not been running for at least 48hrs." $boldred cecho "It may not be safe to use these recommendations" $boldred fi echo "" cecho "To find out more information on how each of these" $red cecho "runtime variables effects performance visit:" $red if [ "$major_version" = '3.23' ] || [ "$major_version" = '4.0' ] || [ "$major_version" = '4.1' ] ; then cecho "http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html" $boldblue elif [ "$major_version" = '5.0' ] || [ "$major_version" = '5.1' ] ; then cecho "http://dev.mysql.com/doc/refman/$major_version/en/server-system-variables.html" $boldblue else cecho "UNSUPPORTED MYSQL VERSION" $boldred exit 1 fi cecho "Visit http://www.mysql.com/products/enterprise/advisors.html" $boldblue cecho "for info about MySQL's Enterprise Monitoring and Advisory Service" $boldblue } check_slow_queries () { ## -- Slow Queries -- ## cecho "SLOW QUERIES" $boldblue mysql_status \'Slow_queries\' slow_queries mysql_variable \'long_query_time\' long_query_time mysql_variable \'log%queries\' log_slow_queries prefered_query_time=5 if [ -e /etc/my.cnf ] ; then if [ -z $log_slow_queries ] ; then log_slow_queries=$(grep log-slow-queries /etc/my.cnf) fi fi if [ "$log_slow_queries" = 'ON' ] ; then cecho "The slow query log is enabled." elif [ "$log_slow_queries" = 'OFF' ] ; then cecho "The slow query log is \c" cecho "NOT \c" $boldred cecho "enabled." elif [ -z $log_slow_queries ] ; then cecho "The slow query log is \c" cecho "NOT \c" $boldred cecho "enabled." else cecho "Error: $log_slow_queries" $boldred fi cecho "Current long_query_time = $long_query_time sec." cecho "You have \c" cecho "$slow_queries \c" $boldred cecho "out of \c" cecho "$questions \c" $boldred cecho "that take longer than $long_query_time sec. to complete" if [ "$major_version" = '5.1' ]; then float2int long_query_time long_query_timeInt else long_query_timeInt=$(($long_query_time)) fi if [ $long_query_timeInt -gt $prefered_query_time ] ; then cecho "Your long_query_time may be too high, I typically set this under $prefered_query_time sec." $red else cecho "Your long_query_time seems to be fine" $green fi } check_binary_log () { ## -- Binary Log -- ## cecho "BINARY UPDATE LOG" $boldblue mysql_variable \'log_bin\' log_bin mysql_variable \'max_binlog_size\' max_binlog_size mysql_variable \'expire_logs_days\' expire_logs_days mysql_variable \'sync_binlog\' sync_binlog # mysql_variable \'max_binlog_cache_size\' max_binlog_cache_size if [ "$log_bin" = 'ON' ] ; then cecho "The binary update log is enabled" if [ -z "$max_binlog_size" ] ; then cecho "The max_binlog_size is not set. The binary log will rotate when it reaches 1GB." $red fi if [ "$expire_logs_days" -eq 0 ] ; then cecho "The expire_logs_days is not set." $boldred cecho "The mysqld will retain the entire binary log until \c" $red cecho "RESET MASTER or PURGE MASTER LOGS commands are run manually" $red cecho "Setting expire_logs_days will allow you to remove old binary logs automatically" $yellow cecho "See http://dev.mysql.com/doc/refman/$major_version/en/purge-master-logs.html" $yellow fi if [ "$sync_binlog" = 0 ] ; then cecho "Binlog sync is not enabled, you could loose binlog records during a server crash" $red fi else cecho "The binary update log is \c" cecho "NOT \c" $boldred cecho "enabled." cecho "You will not be able to do point in time recovery" $red cecho "See http://dev.mysql.com/doc/refman/$major_version/en/point-in-time-recovery.html" $yellow fi } check_used_connections () { ## -- Used Connections -- ## mysql_variable \'max_connections\' max_connections mysql_status \'Max_used_connections\' max_used_connections mysql_status \'Threads_connected\' threads_connected connections_ratio=$(($max_used_connections*100/$max_connections)) cecho "MAX CONNECTIONS" $boldblue cecho "Current max_connections = $max_connections" cecho "Current threads_connected = $threads_connected" cecho "Historic max_used_connections = $max_used_connections" cecho "The number of used connections is \c" if [ $connections_ratio -ge 85 ] ; then txt_color=$red error=1 elif [ $connections_ratio -le 10 ] ; then txt_color=$red error=2 else txt_color=$green error=0 fi # cecho "$max_used_connections \c" $txt_color # cecho "which is \c" cecho "$connections_ratio% \c" $txt_color cecho "of the configured maximum." if [ $error -eq 1 ] ; then cecho "You should raise max_connections" $txt_color elif [ $error -eq 2 ] ; then cecho "You are using less than 10% of your configured max_connections." $txt_color cecho "Lowering max_connections could help to avoid an over-allocation of memory" $txt_color cecho "See \"MEMORY USAGE\" section to make sure you are not over-allocating" $txt_color else cecho "Your max_connections variable seems to be fine." $txt_color fi unset txt_color } check_threads() { ## -- Worker Threads -- ## cecho "WORKER THREADS" $boldblue mysql_status \'Threads_created\' threads_created1 sleep 1 mysql_status \'Threads_created\' threads_created2 mysql_status \'Threads_cached\' threads_cached mysql_status \'Uptime\' uptime mysql_variable \'thread_cache_size\' thread_cache_size historic_threads_per_sec=$(($threads_created1/$uptime)) current_threads_per_sec=$(($threads_created2-$threads_created1)) cecho "Current thread_cache_size = $thread_cache_size" cecho "Current threads_cached = $threads_cached" cecho "Current threads_per_sec = $current_threads_per_sec" cecho "Historic threads_per_sec = $historic_threads_per_sec" if [ $historic_threads_per_sec -ge 2 ] && [ $threads_cached -le 1 ] ; then cecho "Threads created per/sec are overrunning threads cached" $red cecho "You should raise thread_cache_size" $red elif [ $current_threads_per_sec -ge 2 ] ; then cecho "Threads created per/sec are overrunning threads cached" $red cecho "You should raise thread_cache_size" $red else cecho "Your thread_cache_size is fine" $green fi } check_key_buffer_size () { ## -- Key buffer Size -- ## cecho "KEY BUFFER" $boldblue mysql_status \'Key_read_requests\' key_read_requests mysql_status \'Key_reads\' key_reads mysql_status \'Key_blocks_used\' key_blocks_used mysql_status \'Key_blocks_unused\' key_blocks_unused mysql_variable \'key_cache_block_size\' key_cache_block_size mysql_variable \'key_buffer_size\' key_buffer_size mysql_variable \'datadir\' datadir mysql_variable \'version_compile_machine\' mysql_version_compile_machine myisam_indexes=$($mysql -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM' */") if [ -z $myisam_indexes ] ; then myisam_indexes=$(find $datadir -name '*.MYI' -exec du $duflags '{}' \; 2>&1 | awk '{ s += $1 } END { printf("%.0f\n", s )}') fi if [ $key_reads -eq 0 ] ; then cecho "No key reads?!" $boldred cecho "Seriously look into using some indexes" $red key_cache_miss_rate=0 key_buffer_free=$(echo "$key_blocks_unused * $key_cache_block_size / $key_buffer_size * 100" | bc -l ) key_buffer_freeRND=$(echo "scale=0; $key_buffer_free / 1" | bc -l) else key_cache_miss_rate=$(($key_read_requests/$key_reads)) if [ ! -z $key_blocks_unused ] ; then key_buffer_free=$(echo "$key_blocks_unused * $key_cache_block_size / $key_buffer_size * 100" | bc -l ) key_buffer_freeRND=$(echo "scale=0; $key_buffer_free / 1" | bc -l) else key_buffer_free='Unknown' key_buffer_freeRND=75 fi fi human_readable $myisam_indexes myisam_indexesHR cecho "Current MyISAM index space = $myisam_indexesHR $unit" human_readable $key_buffer_size key_buffer_sizeHR cecho "Current key_buffer_size = $key_buffer_sizeHR $unit" cecho "Key cache miss rate is 1 : $key_cache_miss_rate" cecho "Key buffer free ratio = $key_buffer_freeRND %" if [ "$major_version" = '5.1' ] && [ $mysql_version_num -lt '5123' ] ; then if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.1.23 " $boldred cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred fi fi if [ "$major_version" = '5.0' ] && [ $mysql_version_num -lt '5052' ] ; then if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " $boldred cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred fi fi if [ "$major_version" = '4.1' -o "$major_version" = '4.0' ] && [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " $boldred cecho "Reduce key_buffer_size to a safe value" $boldred cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred fi if [ $key_cache_miss_rate -le 100 ] && [ $key_cache_miss_rate -gt 0 ] && [ $key_buffer_freeRND -le 20 ]; then cecho "You could increase key_buffer_size" $boldred cecho "It is safe to raise this up to 1/4 of total system memory;" cecho "assuming this is a dedicated database server." elif [ $key_buffer_freeRND -le 20 ] && [ $key_buffer_size -le $myisam_indexes ] ; then cecho "You could increase key_buffer_size" $boldred cecho "It is safe to raise this up to 1/4 of total system memory;" cecho "assuming this is a dedicated database server." elif [ $key_cache_miss_rate -ge 10000 ] || [ $key_buffer_freeRND -le 50 ] ; then cecho "Your key_buffer_size seems to be too high." $red cecho "Perhaps you can use these resources elsewhere" $red else cecho "Your key_buffer_size seems to be fine" $green fi } check_query_cache () { ## -- Query Cache -- ## cecho "QUERY CACHE" $boldblue mysql_variable \'version\' mysql_version mysql_variable \'query_cache_size\' query_cache_size mysql_variable \'query_cache_limit\' query_cache_limit mysql_variable \'query_cache_min_res_unit\' query_cache_min_res_unit mysql_status \'Qcache_free_memory\' qcache_free_memory mysql_status \'Qcache_total_blocks\' qcache_total_blocks mysql_status \'Qcache_free_blocks\' qcache_free_blocks mysql_status \'Qcache_lowmem_prunes\' qcache_lowmem_prunes if [ -z $query_cache_size ] ; then cecho "You are using MySQL $mysql_version, no query cache is supported." $red cecho "I recommend an upgrade to MySQL 4.1 or better" $red elif [ $query_cache_size -eq 0 ] ; then cecho "Query cache is supported but not enabled" $red cecho "Perhaps you should set the query_cache_size" $red else qcache_used_memory=$(($query_cache_size-$qcache_free_memory)) qcache_mem_fill_ratio=$(echo "scale=2; $qcache_used_memory * 100 / $query_cache_size" | bc -l) qcache_mem_fill_ratioHR=$(echo "scale=0; $qcache_mem_fill_ratio / 1" | bc -l) cecho "Query cache is enabled" $green human_readable $query_cache_size query_cache_sizeHR cecho "Current query_cache_size = $query_cache_sizeHR $unit" human_readable $qcache_used_memory qcache_used_memoryHR cecho "Current query_cache_used = $qcache_used_memoryHR $unit" human_readable $query_cache_limit query_cache_limitHR cecho "Current query_cache_limit = $query_cache_limitHR $unit" cecho "Current Query cache Memory fill ratio = $qcache_mem_fill_ratio %" human_readable $query_cache_min_res_unit query_cache_min_res_unitHR cecho "Current query_cache_min_res_unit = $query_cache_min_res_unitHR $unit" if [ $qcache_free_blocks -gt 2 ] && [ $qcache_total_blocks -gt 0 ] ; then qcache_percent_fragmented=$(echo "scale=2; $qcache_free_blocks * 100 / $qcache_total_blocks" | bc -l) qcache_percent_fragmentedHR=$(echo "scale=0; $qcache_percent_fragmented / 1" | bc -l) if [ $qcache_percent_fragmentedHR -gt 20 ] ; then cecho "Query Cache is $qcache_percent_fragmentedHR % fragmented" $red cecho "Run \"FLUSH QUERY CACHE\" periodically to defragment the query cache memory" $red cecho "If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation." $red fi fi if [ $qcache_mem_fill_ratioHR -le 25 ] ; then cecho "Your query_cache_size seems to be too high." $red cecho "Perhaps you can use these resources elsewhere" $red fi if [ $qcache_lowmem_prunes -ge 50 ] && [ $qcache_mem_fill_ratioHR -ge 80 ]; then cecho "However, \c" cecho "$qcache_lowmem_prunes \c" $boldred cecho "queries have been removed from the query cache due to lack of memory" cecho "Perhaps you should raise query_cache_size" $boldred fi cecho "MySQL won't cache query results that are larger than query_cache_limit in size" $yellow fi } check_sort_operations () { ## -- Sort Operations -- ## cecho "SORT OPERATIONS" $boldblue mysql_status \'Sort_merge_passes\' sort_merge_passes mysql_status \'Sort_scan\' sort_scan mysql_status \'Sort_range\' sort_range mysql_variable \'sort_buffer%\' sort_buffer_size mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size total_sorts=$(($sort_scan+$sort_range)) if [ -z $read_rnd_buffer_size ] ; then mysql_variable \'record_buffer\' read_rnd_buffer_size fi ## Correct for rounding error in mysqld where 512K != 524288 ## sort_buffer_size=$(($sort_buffer_size+8)) read_rnd_buffer_size=$(($read_rnd_buffer_size+8)) human_readable $sort_buffer_size sort_buffer_sizeHR cecho "Current sort_buffer_size = $sort_buffer_sizeHR $unit" human_readable $read_rnd_buffer_size read_rnd_buffer_sizeHR cecho "Current \c" if [ "$major_version" = '3.23' ] ; then cecho "record_rnd_buffer \c" else cecho "read_rnd_buffer_size \c" fi cecho "= $read_rnd_buffer_sizeHR $unit" if [ $total_sorts -eq 0 ] ; then cecho "No sort operations have been performed" passes_per_sort=0 fi if [ $sort_merge_passes -ne 0 ] ; then passes_per_sort=$(($sort_merge_passes/$total_sorts)) else passes_per_sort=0 fi if [ $passes_per_sort -ge 2 ] ; then cecho "On average \c" cecho "$passes_per_sort \c" $boldred cecho "sort merge passes are made per sort operation" cecho "You should raise your sort_buffer_size" cecho "You should also raise your \c" if [ "$major_version" = '3.23' ] ; then cecho "record_rnd_buffer_size" else cecho "read_rnd_buffer_size" fi else cecho "Sort buffer seems to be fine" $green fi } check_join_operations () { ## -- Joins -- ## cecho "JOINS" $boldblue mysql_status \'Select_full_join\' select_full_join mysql_status \'Select_range_check\' select_range_check mysql_variable \'join_buffer%\' join_buffer_size ## Some 4K is dropped from join_buffer_size adding it back to make sane ## ## handling of human-readable conversion ## join_buffer_size=$(($join_buffer_size+4096)) human_readable $join_buffer_size join_buffer_sizeHR 2 cecho "Current join_buffer_size = $join_buffer_sizeHR $unit" cecho "You have had $select_full_join queries where a join could not use an index properly" if [ $select_range_check -eq 0 ] && [ $select_full_join -eq 0 ] ; then cecho "Your joins seem to be using indexes properly" $green fi if [ $select_full_join -gt 0 ] ; then print_error='true' raise_buffer='true' fi if [ $select_range_check -gt 0 ] ; then cecho "You have had $select_range_check joins without keys that check for key usage after each row" $red print_error='true' raise_buffer='true' fi ## For Debuging ## # print_error='true' if [ $join_buffer_size -ge 4194304 ] ; then cecho "join_buffer_size >= 4 M" $boldred cecho "This is not advised" $boldred raise_buffer= fi if [ $print_error ] ; then if [ "$major_version" = '3.23' ] || [ "$major_version" = '4.0' ] ; then cecho "You should enable \"log-long-format\" " elif [ "$major_version" = '4.1' ] || [ "$major_version" = '5.0' ] || [ "$major_version" = '5.1' ] ; then cecho "You should enable \"log-queries-not-using-indexes\"" fi cecho "Then look for non indexed joins in the slow query log." if [ $raise_buffer ] ; then cecho "If you are unable to optimize your queries you may want to increase your" cecho "join_buffer_size to accommodate larger joins in one pass.\n" cecho "Note! This script will still suggest raising the join_buffer_size when" $boldred cecho "ANY joins not using indexes are found." $boldred fi fi # XXX Add better tests for join_buffer_size XXX # } check_tmp_tables () { ## -- Temp Tables -- ## cecho "TEMP TABLES" $boldblue mysql_status \'Created_tmp_tables\' created_tmp_tables mysql_status \'Created_tmp_disk_tables\' created_tmp_disk_tables mysql_variable \'tmp_table_size\' tmp_table_size mysql_variable \'max_heap_table_size\' max_heap_table_size if [ $created_tmp_tables -eq 0 ] ; then tmp_disk_tables=0 else tmp_disk_tables=$((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))) fi human_readable $max_heap_table_size max_heap_table_sizeHR cecho "Current max_heap_table_size = $max_heap_table_sizeHR $unit" human_readable $tmp_table_size tmp_table_sizeHR cecho "Current tmp_table_size = $tmp_table_sizeHR $unit" cecho "Of $created_tmp_tables temp tables, $tmp_disk_tables% were created on disk" if [ $tmp_table_size -gt $max_heap_table_size ] ; then cecho "Effective in-memory tmp_table_size is limited to max_heap_table_size." $yellow fi if [ $tmp_disk_tables -ge 25 ] ; then cecho "Perhaps you should increase your tmp_table_size and/or max_heap_table_size" $boldred cecho "to reduce the number of disk-based temporary tables" $boldred cecho "Note! BLOB and TEXT columns are not allow in memory tables." $yellow cecho "If you are using these columns raising these values might not impact your " $yellow cecho "ratio of on disk temp tables." $yellow else cecho "Created disk tmp tables ratio seems fine" $green fi } check_open_files () { ## -- Open Files Limit -- ## cecho "OPEN FILES LIMIT" $boldblue mysql_variable \'open_files_limit\' open_files_limit mysql_status \'Open_files\' open_files if [ -z $open_files_limit ] || [ $open_files_limit -eq 0 ] ; then open_files_limit=$(ulimit -n) cant_override=1 else cant_override=0 fi cecho "Current open_files_limit = $open_files_limit files" open_files_ratio=$(($open_files*100/$open_files_limit)) cecho "The open_files_limit should typically be set to at least 2x-3x" $yellow cecho "that of table_cache if you have heavy MyISAM usage." $yellow if [ $open_files_ratio -ge 75 ] ; then cecho "You currently have open more than 75% of your open_files_limit" $boldred if [ $cant_override -eq 1 ] ; then cecho "You should set a higer value for ulimit -u in the mysql startup script then restart mysqld" $boldred cecho "MySQL 3.23 users : This is just a guess based upon the current shell's ulimit -u value" $yellow elif [ $cant_override -eq 0 ] ; then cecho "You should set a higher value for open_files_limit in my.cnf" $boldred else cecho "ERROR can't determine if mysqld override of ulimit is allowed" $boldred exit 1 fi else cecho "Your open_files_limit value seems to be fine" $green fi } check_table_cache () { ## -- Table Cache -- ## cecho "TABLE CACHE" $boldblue mysql_variable \'datadir\' datadir mysql_variable \'table_cache\' table_cache ## /* MySQL +5.1 version of table_cache */ ## mysql_variable \'table_open_cache\' table_open_cache mysql_variable \'table_definition_cache\' table_definition_cache mysql_status \'Open_tables\' open_tables mysql_status \'Opened_tables\' opened_tables mysql_status \'Open_table_definitions\' open_table_definitions table_count=$($mysql -Bse "/*!50000 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' */") if [ -z "$table_count" ] ; then if [ "$UID" != "$socket_owner" ] && [ "$UID" != "0" ] ; then cecho "You are not '$socket_owner' or 'root'" $red cecho "I am unable to determine the table_count!" $red else table_count=$(find $datadir 2>&1 | grep -c .frm$) fi fi if [ $table_open_cache ] ; then table_cache=$table_open_cache fi if [ $opened_tables -ne 0 ] && [ $table_cache -ne 0 ] ; then table_cache_hit_rate=$(($open_tables*100/$opened_tables)) table_cache_fill=$(($open_tables*100/$table_cache)) elif [ $opened_tables -eq 0 ] && [ $table_cache -ne 0 ] ; then table_cache_hit_rate=100 table_cache_fill=$(($open_tables*100/$table_cache)) else cecho "ERROR no table_cache ?!" $boldred exit 1 fi if [ $table_cache ] && [ ! $table_open_cache ] ; then cecho "Current table_cache value = $table_cache tables" fi if [ $table_open_cache ] ; then cecho "Current table_open_cache = $table_open_cache tables" cecho "Current table_definition_cache = $table_definition_cache tables" fi if [ $table_count ] ; then cecho "You have a total of $table_count tables" fi if [ $table_cache_fill -lt 95 ] ; then cecho "You have \c" cecho "$open_tables \c" $green cecho "open tables." cecho "The table_cache value seems to be fine" $green elif [ $table_cache_hit_rate -le 85 -o $table_cache_fill -ge 95 ]; then cecho "You have \c" cecho "$open_tables \c" $boldred cecho "open tables." cecho "Current table_cache hit rate is \c" cecho "$table_cache_hit_rate%\c" $boldred cecho ", while \c" cecho "$table_cache_fill% \c" $boldred cecho "of your table cache is in use" cecho "You should probably increase your table_cache" $red else cecho "Current table_cache hit rate is \c" cecho "$table_cache_hit_rate%\c" $green cecho ", while \c" cecho "$table_cache_fill% \c" $green cecho "of your table cache is in use" cecho "The table cache value seems to be fine" $green fi if [ $table_definition_cache ] && [ $table_definition_cache -le $table_count ] && [ $table_count -ge 100 ] ; then cecho "You should probably increase your table_definition_cache value." $red fi } check_table_locking () { ## -- Table Locking -- ## cecho "TABLE LOCKING" $boldblue mysql_status \'Table_locks_waited\' table_locks_waited mysql_status \'Table_locks_immediate\' table_locks_immediate mysql_variable \'concurrent_insert\' concurrent_insert mysql_variable \'low_priority_updates\' low_priority_updates if [ "$concurrent_insert" = 'ON' ]; then concurrent_insert=1 elif [ "$concurrent_insert" = 'OFF' ]; then concurrent_insert=0 fi cecho "Current Lock Wait ratio = \c" if [ $table_locks_waited -gt 0 ]; then immediate_locks_miss_rate=$(($table_locks_immediate/$table_locks_waited)) cecho "1 : $immediate_locks_miss_rate" $red else immediate_locks_miss_rate=99999 # perfect cecho "0 : $questions" fi if [ $immediate_locks_miss_rate -lt 5000 ] ; then cecho "You may benefit from selective use of InnoDB." if [ "$low_priority_updates" = 'OFF' ] ; then cecho "If you have long running SELECT's against MyISAM tables and perform" cecho "frequent updates consider setting 'low_priority_updates=1'" fi if [ $concurrent_insert -le 1 ] && [ "$major_version" = '5.0' -o "$major_version" = '5.1' ] ; then cecho "If you have a high concurrency of inserts on Dynamic row-length tables" cecho "consider setting 'concurrent_insert=2'." fi else cecho "Your table locking seems to be fine" $green fi } check_table_scans () { ## -- Table Scans -- ## cecho "TABLE SCANS" $boldblue mysql_status \'Com_select\' com_select mysql_status \'Handler_read_rnd_next\' read_rnd_next mysql_variable \'read_buffer_size\' read_buffer_size if [ -z $read_buffer_size ] ; then mysql_variable \'record_buffer\' read_buffer_size fi human_readable $read_buffer_size read_buffer_sizeHR cecho "Current read_buffer_size = $read_buffer_sizeHR $unit" if [ $com_select -gt 0 ] ; then full_table_scans=$(($read_rnd_next/$com_select)) cecho "Current table scan ratio = $full_table_scans : 1" if [ $full_table_scans -ge 4000 ] && [ $read_buffer_size -le 2097152 ] ; then cecho "You have a high ratio of sequential access requests to SELECTs" $red cecho "You may benefit from raising \c" $red if [ "$major_version" = '3.23' ] ; then cecho "record_buffer \c" $red else cecho "read_buffer_size \c" $red fi cecho "and/or improving your use of indexes." $red elif [ $read_buffer_size -gt 8388608 ] ; then cecho "read_buffer_size is over 8 MB \c" $red cecho "there is probably no need for such a large read_buffer" $red else cecho "read_buffer_size seems to be fine" $green fi else cecho "read_buffer_size seems to be fine" $green fi } check_innodb_status () { ## -- InnoDB -- ## mysql_variable \'have_innodb\' have_innodb if [ "$have_innodb" = "YES" ] ; then mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size mysql_variable \'innodb_fast_shutdown\' innodb_fast_shutdown mysql_variable \'innodb_flush_log_at_trx_commit\' innodb_flush_log_at_trx_commit mysql_variable \'innodb_locks_unsafe_for_binlog\' innodb_locks_unsafe_for_binlog mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size mysql_variable \'innodb_log_file_size\' innodb_log_file_size mysql_variable \'innodb_log_files_in_group\' innodb_log_files_in_group mysql_variable \'innodb_safe_binlog\' innodb_safe_binlog mysql_variable \'innodb_thread_concurrency\' innodb_thread_concurrency echo cecho "INNODB STATUS" $boldblue innodb_indexes=$($mysql -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='InnoDB' */") if [ ! -z "$innodb_indexes" ] ; then mysql_status \'Innodb_buffer_pool_pages_data\' innodb_buffer_pool_pages_data mysql_status \'Innodb_buffer_pool_pages_misc\' innodb_buffer_pool_pages_misc mysql_status \'Innodb_buffer_pool_pages_free\' innodb_buffer_pool_pages_free mysql_status \'Innodb_buffer_pool_pages_total\' innodb_buffer_pool_pages_total mysql_status \'Innodb_buffer_pool_read_ahead_seq\' innodb_buffer_pool_read_ahead_seq mysql_status \'Innodb_buffer_pool_read_requests\' innodb_buffer_pool_read_requests mysql_status \'Innodb_os_log_pending_fsyncs\' innodb_os_log_pending_fsyncs mysql_status \'Innodb_os_log_pending_writes\' innodb_os_log_pending_writes mysql_status \'Innodb_log_waits\' innodb_log_waits mysql_status \'Innodb_row_lock_time\' innodb_row_lock_time mysql_status \'Innodb_row_lock_waits\' innodb_row_lock_waits human_readable $innodb_indexes innodb_indexesHR cecho "Current InnoDB index space = $innodb_indexesHR $unit" else cecho "Cannot find InnoDB index space prior to 5.0.x" $red fi human_readable $innodb_buffer_pool_size innodb_buffer_pool_sizeHR cecho "Current innodb_buffer_pool_size = $innodb_buffer_pool_sizeHR $unit" cecho "Depending on how much space your innodb indexes take up it may be safe" cecho "to increase this value to up to 1 / 3 of total system memory" echo $mysql -s -e "SHOW /*!50000 ENGINE */ INNODB STATUS\G" else cecho "No InnoDB Support Enabled!" $boldred fi } total_memory_used () { ## -- Total Memory Usage -- ## cecho "MEMORY USAGE" $boldblue mysql_variable \'read_buffer_size\' read_buffer_size mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size mysql_variable \'sort_buffer_size\' sort_buffer_size mysql_variable \'thread_stack\' thread_stack mysql_variable \'max_connections\' max_connections mysql_variable \'join_buffer_size\' join_buffer_size mysql_variable \'tmp_table_size\' tmp_table_size mysql_variable \'max_heap_table_size\' max_heap_table_size mysql_variable \'log_bin\' log_bin mysql_status \'Max_used_connections\' max_used_connections if [ "$major_version" = "3.23" ] ; then mysql_variable \'record_buffer\' read_buffer_size mysql_variable \'record_rnd_buffer\' read_rnd_buffer_size mysql_variable \'sort_buffer\' sort_buffer_size fi if [ "$log_bin" = "ON" ] ; then mysql_variable \'binlog_cache_size\' binlog_cache_size else binlog_cache_size=0 fi if [ $max_heap_table_size -le $tmp_table_size ] ; then effective_tmp_table_size=$max_heap_table_size else effective_tmp_table_size=$tmp_table_size fi per_thread_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections" | bc -l) per_thread_max_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_used_connections" | bc -l) mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size if [ -z $innodb_buffer_pool_size ] ; then innodb_buffer_pool_size=0 fi mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size if [ -z $innodb_additional_mem_pool_size ] ; then innodb_additional_mem_pool_size=0 fi mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size if [ -z $innodb_log_buffer_size ] ; then innodb_log_buffer_size=0 fi mysql_variable \'key_buffer_size\' key_buffer_size mysql_variable \'query_cache_size\' query_cache_size if [ -z $query_cache_size ] ; then query_cache_size=0 fi global_buffers=$(echo "$innodb_buffer_pool_size+$innodb_additional_mem_pool_size+$innodb_log_buffer_size+$key_buffer_size+$query_cache_size" | bc -l) max_memory=$(echo "$global_buffers+$per_thread_max_buffers" | bc -l) total_memory=$(echo "$global_buffers+$per_thread_buffers" | bc -l) pct_of_sys_mem=$(echo "scale=0; $total_memory*100/$physical_memory" | bc -l) if [ $pct_of_sys_mem -gt 90 ] ; then txt_color=$boldred error=1 else txt_color= error=0 fi human_readable $max_memory max_memoryHR cecho "Max Memory Ever Allocated : $max_memoryHR $unit" $txt_color human_readable $per_thread_buffers per_thread_buffersHR cecho "Configured Max Per-thread Buffers : $per_thread_buffersHR $unit" $txt_color human_readable $global_buffers global_buffersHR cecho "Configured Max Global Buffers : $global_buffersHR $unit" $txt_color human_readable $total_memory total_memoryHR cecho "Configured Max Memory Limit : $total_memoryHR $unit" $txt_color # human_readable $effective_tmp_table_size effective_tmp_table_sizeHR # cecho "Plus $effective_tmp_table_sizeHR $unit per temporary table created" human_readable $physical_memory physical_memoryHR cecho "Physical Memory : $physical_memoryHR $unit" $txt_color if [ $error -eq 1 ] ; then cecho "\nMax memory limit exceeds 90% of physical memory" $txt_color else cecho "Max memory limit seem to be within acceptable norms" $green fi unset txt_color } snarky () { ## -- Be Snarky -- ## fortune=$(which fortune 2>/dev/null) if [ -z $fortune ] ; then echo "What the hell sort of straight-lace bastard doesn't have fortune installed?" else $fortune fi } ## Required Functions ## login_validation () { check_for_socket # determine the socket location -- 1st login check_for_plesk_passwords # determine the login method -- 2nd login check_mysql_login # determine if mysql is accepting login -- 3rd login export major_version=$($mysql -Bse "SELECT SUBSTRING_INDEX(VERSION(), '.', +2)") export mysql_version_num=$($mysql -Bse "SELECT LEFT(REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', ''),4)" ) } shared_info () { export major_version=$($mysql -Bse "SELECT SUBSTRING_INDEX(VERSION(), '.', +2)") export mysql_version_num=$($mysql -Bse "SELECT LEFT(REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', ''),4)" ) mysql_status \'Questions\' questions # socket_owner=$(find -L $socket -printf '%u\n') socket_owner=$(ls -nH $socket | awk '{ print $3 }') } get_system_info () { export OS=$(uname) # Get information for various UNIXes if [ "$OS" = 'Darwin' ]; then ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }' | head -1) found_socks=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }') export physical_memory=$(sysctl -n hw.memsize) export duflags='' elif [ "$OS" = 'FreeBSD' ] || [ "$OS" = 'OpenBSD' ]; then ## On FreeBSD must be root to locate sockets. ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }' | head -1) found_socks=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }') export physical_memory=$(sysctl -n hw.realmem) export duflags='' elif [ "$OS" = 'Linux' ] ; then ## Includes SWAP ## export physical_memory=$(free -b | grep -v buffers | awk '{ s += $2 } END { printf("%.0f\n", s ) }') ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }' | head -1) found_socks=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }') export physical_memory=$(awk '/^MemTotal/ { printf("%.0f", $2*1024 ) }' < /proc/meminfo) export duflags='-b' elif [ "$OS" = 'SunOS' ] ; then ps_socket=$(netstat -a | awk '/mysql(d)?.sock/ { print $5 }' | head -1) found_socks=$(netstat -a | awk '/mysql(d)?.sock/ { print $5 }') export physical_memory=$(prtconf | awk '/^Memory\ size:/ { print $3*1048576 }') fi } ## Optional Components Groups ## banner_info () { shared_info print_banner ; echo check_mysql_version ; echo post_uptime_warning ; echo } misc () { shared_info check_slow_queries ; echo check_binary_log ; echo check_threads ; echo check_used_connections ; echo } memory () { shared_info total_memory_used ; echo check_key_buffer_size ; echo check_query_cache ; echo check_sort_operations ; echo check_join_operations ; echo } file () { shared_info check_open_files ; echo check_table_cache ; echo check_tmp_tables ; echo check_table_scans ; echo check_table_locking ; echo } all () { banner_info misc memory file # snarky } prompt () { prompted='true' read -p "Username [anonymous] : " user read -rp "Password [] : " pass cecho "\n\c" read -p "Socket [ /var/lib/mysql/mysql.sock ] : " socket if [ -z $socket ] ; then export socket='/var/lib/mysql/mysql.sock' fi if [ -z $pass ] ; then export mysql="mysql -S $socket -u$user" export mysqladmin="mysqladmin -S $socket -u$user" else export mysql="mysql -S $socket -u$user -p$pass" export mysqladmin="mysqladmin -S $socket -u$user -p$pass" fi check_for_socket check_mysql_login if [ $? = 1 ] ; then exit 1 fi read -p "Mode to test - banner, file, misc, mem, innodb, [all] : " REPLY if [ -z $REPLY ] ; then REPLY='all' fi case $REPLY in banner | BANNER | header | HEADER | head | HEAD) banner_info ;; misc | MISC | miscelaneous ) misc ;; mem | memory | MEM | MEMORY ) memory ;; file | FILE | disk | DISK ) file ;; innodb | INNODB ) innodb ;; all | ALL ) cecho "\n\c" all ;; * ) cecho "Invalid Mode! Valid options are 'banner', 'misc', 'memory', 'file', 'innodb' or 'all'" $boldred exit 1 ;; esac } ## Address environmental differences ## get_system_info # echo $ps_socket if [ -z "$1" ] ; then login_validation mode='ALL' elif [ "$1" = "prompt" ] || [ "$1" = "PROMPT" ] ; then mode=$1 elif [ "$1" != "prompt" ] || [ "$1" != "PROMPT" ] ; then login_validation mode=$1 fi case $mode in all | ALL ) cecho "\n\c" all ;; mem | memory | MEM | MEMORY ) cecho "\n\c" memory ;; file | FILE | disk | DISK ) cecho "\n\c" file ;; banner | BANNER | header | HEADER | head | HEAD ) banner_info ;; misc | MISC | miscelaneous ) cecho "\n\c" misc ;; innodb | INNODB ) banner_info check_innodb_status ; echo ;; prompt | PROMPT ) prompt ;; *) cecho "usage: $0 [ all | banner | file | innodb | memory | misc | promp ]" $boldred exit 1 ;; esac