sqltun1

From marc, 3 Years ago, written in Plain Text, viewed 3'001 times.
URL http://bin.flmt.es/view/da142909 Embed
Download Paste or View Raw
  1. #!/bin/sh
  2.  
  3. # vim: ts=8
  4. #########################################################################
  5. #                                                                       #
  6. #       MySQL performance tuning primer script                          #
  7. #       Writen by: Matthew Montgomery                                   #
  8. #       Report bugs to: https://bugs.launchpad.net/mysql-tuning-primer  #
  9. #       Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/)        #
  10. #       Version: 1.6-r1         Released: 2011-08-06                    #
  11. #       Licenced under GPLv2                                            #
  12. #                                                                       #
  13. #########################################################################
  14.  
  15. #########################################################################
  16. #                                                                       #
  17. #       Usage: ./tuning-primer.sh [ mode ]                              #
  18. #                                                                       #
  19. #       Available Modes:                                                #
  20. #               all :           perform all checks (default)            #
  21. #               prompt :        prompt for login credintials and socket #
  22. #                               and execution mode                      #
  23. #               mem, memory :   run checks for tunable options which    #
  24. #                               effect memory usage                     #
  25. #               disk, file :    run checks for options which effect     #
  26. #                               i/o performance or file handle limits   #
  27. #               innodb :        run InnoDB checks /* to be improved */  #
  28. #               misc :          run checks for that don't categorise    #
  29. #                               well Slow Queries, Binary logs,         #
  30. #                               Used Connections and Worker Threads     #
  31. #########################################################################
  32. #                                                                       #
  33. # Set this socket variable ONLY if you have multiple instances running  #
  34. # or we are unable to find your socket, and you don't want to to be     #
  35. # prompted for input each time you run this script.                     #
  36. #                                                                       #
  37. #########################################################################
  38. socket=
  39.  
  40. export black='\033[0m'
  41. export boldblack='\033[1;0m'
  42. export red='\033[31m'
  43. export boldred='\033[1;31m'
  44. export green='\033[32m'
  45. export boldgreen='\033[1;32m'
  46. export yellow='\033[33m'
  47. export boldyellow='\033[1;33m'
  48. export blue='\033[34m'
  49. export boldblue='\033[1;34m'
  50. export magenta='\033[35m'
  51. export boldmagenta='\033[1;35m'
  52. export cyan='\033[36m'
  53. export boldcyan='\033[1;36m'
  54. export white='\033[37m'
  55. export boldwhite='\033[1;37m'
  56.  
  57.  
  58. cecho ()
  59.  
  60. ## -- Function to easliy print colored text -- ##
  61.        
  62.         # Color-echo.
  63.         # Argument $1 = message
  64.         # Argument $2 = color
  65. {
  66. local default_msg="No message passed."
  67.  
  68. message=${1:-$default_msg}      # Defaults to default message.
  69.  
  70. #change it for fun
  71. #We use pure names
  72. color=${2:-black}               # Defaults to black, if not specified.
  73.  
  74. case $color in
  75.         black)
  76.                  printf "$black" ;;
  77.         boldblack)
  78.                  printf "$boldblack" ;;
  79.         red)
  80.                  printf "$red" ;;
  81.         boldred)
  82.                  printf "$boldred" ;;
  83.         green)
  84.                  printf "$green" ;;
  85.         boldgreen)
  86.                  printf "$boldgreen" ;;
  87.         yellow)
  88.                  printf "$yellow" ;;
  89.         boldyellow)
  90.                  printf "$boldyellow" ;;
  91.         blue)
  92.                  printf "$blue" ;;
  93.         boldblue)
  94.                  printf "$boldblue" ;;
  95.         magenta)
  96.                  printf "$magenta" ;;
  97.         boldmagenta)
  98.                  printf "$boldmagenta" ;;
  99.         cyan)
  100.                  printf "$cyan" ;;
  101.         boldcyan)
  102.                  printf "$boldcyan" ;;
  103.         white)
  104.                  printf "$white" ;;
  105.         boldwhite)
  106.                  printf "$boldwhite" ;;
  107. esac
  108.   printf "%s\n"  "$message"
  109.   tput sgr0                     # Reset to normal.
  110.   printf "$black"
  111.  
  112. return
  113. }
  114.  
  115.  
  116. cechon ()              
  117.  
  118. ## -- Function to easliy print colored text -- ##
  119.  
  120.         # Color-echo.
  121.         # Argument $1 = message
  122.         # Argument $2 = color
  123. {
  124. local default_msg="No message passed."
  125.                                 # Doesn't really need to be a local variable.
  126.  
  127. message=${1:-$default_msg}      # Defaults to default message.
  128.  
  129. #change it for fun
  130. #We use pure names
  131. color=${2:-black}               # Defaults to black, if not specified.
  132.  
  133. case $color in
  134.         black)
  135.                 printf "$black" ;;
  136.         boldblack)
  137.                 printf "$boldblack" ;;
  138.         red)
  139.                 printf "$red" ;;
  140.         boldred)
  141.                 printf "$boldred" ;;
  142.         green)
  143.                 printf "$green" ;;
  144.         boldgreen)
  145.                 printf "$boldgreen" ;;
  146.         yellow)
  147.                 printf "$yellow" ;;
  148.         boldyellow)
  149.                 printf "$boldyellow" ;;
  150.         blue)
  151.                 printf "$blue" ;;
  152.         boldblue)
  153.                 printf "$boldblue" ;;
  154.         magenta)
  155.                 printf "$magenta" ;;
  156.         boldmagenta)
  157.                 printf "$boldmagenta" ;;
  158.         cyan)
  159.                 printf "$cyan" ;;
  160.         boldcyan)
  161.                 printf "$boldcyan" ;;
  162.         white)
  163.                 printf "$white" ;;
  164.         boldwhite)
  165.                 printf "$boldwhite" ;;
  166. esac
  167.   printf "%s"  "$message"
  168.   tput sgr0                     # Reset to normal.
  169.   printf "$black"
  170.  
  171. return
  172. }
  173.  
  174.  
  175. print_banner () {
  176.  
  177. ## -- Banner -- ##
  178.  
  179. cecho " -- MYSQL PERFORMANCE TUNING PRIMER --" boldblue
  180. cecho "      - By: Matthew Montgomery -" black
  181.  
  182. }
  183.  
  184. ## -- Find the location of the mysql.sock file -- ##
  185.  
  186. check_for_socket () {
  187.         if [ -z "$socket" ] ; then
  188.                 # Use ~/my.cnf version
  189.                 if [ -f ~/.my.cnf ] ; then
  190.                         cnf_socket=$(grep ^socket ~/.my.cnf | awk -F \= '{ print $2 }' | head -1)
  191.                 fi
  192.                 if [ -S "$cnf_socket" ] ; then
  193.                         socket=$cnf_socket
  194.                 elif [ -S /var/lib/mysql/mysql.sock ] ; then
  195.                         socket=/var/lib/mysql/mysql.sock
  196.                 elif [ -S /var/run/mysqld/mysqld.sock ] ; then
  197.                         socket=/var/run/mysqld/mysqld.sock
  198.                 elif [ -S /tmp/mysql.sock ] ; then
  199.                         socket=/tmp/mysql.sock
  200.                 else
  201.                         if [ -S "$ps_socket" ] ; then
  202.                         socket=$ps_socket
  203.                         fi
  204.                 fi
  205.         fi
  206.         if [ -S "$socket" ] ; then
  207.                 echo UP > /dev/null
  208.         else
  209.                 cecho "No valid socket file \"$socket\" found!" boldred
  210.                 cecho "The mysqld process is not running or it is installed in a custom location." red
  211.                 cecho "If you are sure mysqld is running, execute script in \"prompt\" mode or set " red
  212.                 cecho "the socket= variable at the top of this script" red
  213.                 exit 1
  214.         fi
  215. }
  216.  
  217.  
  218. check_for_plesk_passwords () {
  219.  
  220. ## -- Check for the existance of plesk and login using it's credentials -- ##
  221.  
  222.         if [ -f /etc/psa/.psa.shadow ] ; then
  223.                 mysql="mysql -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)"
  224.                 mysqladmin="mysqladmin -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)"
  225.         else
  226.                 mysql="mysql"
  227.                 mysqladmin="mysqladmin"
  228.                 # mysql="mysql -S $socket"
  229.                 # mysqladmin="mysqladmin -S $socket"
  230.         fi
  231. }
  232.  
  233. check_mysql_login () {
  234.  
  235. ## -- Test for running mysql -- ##
  236.  
  237.         is_up=$($mysqladmin ping 2>&1)
  238.         if [ "$is_up" = "mysqld is alive" ] ; then
  239.                 echo UP > /dev/null
  240.                 # echo $is_up
  241.         elif [ "$is_up" != "mysqld is alive" ] ; then
  242.                 printf "\n"
  243.                 cecho "Using login values from ~/.my.cnf"
  244.                 cecho "- INITIAL LOGIN ATTEMPT FAILED -" boldred
  245.                 if [ -z $prompted ] ; then
  246.                 find_webmin_passwords
  247.                 else
  248.                         return 1
  249.                 fi
  250.                
  251.         else
  252.                 cecho "Unknow exit status" red
  253.                 exit -1
  254.         fi
  255. }
  256.  
  257. final_login_attempt () {
  258.         is_up=$($mysqladmin ping 2>&1)
  259.         if [ "$is_up" = "mysqld is alive" ] ; then
  260.                 echo UP > /dev/null
  261.         elif [ "$is_up" != "mysqld is alive" ] ; then
  262.                 cecho "- FINAL LOGIN ATTEMPT FAILED -" boldred
  263.                 cecho "Unable to log into socket: $socket" boldred
  264.                 exit 1
  265.         fi
  266. }
  267.  
  268. second_login_failed () {
  269.  
  270. ## -- create a ~/.my.cnf and exit when all else fails -- ##
  271.  
  272.         cecho "Could not auto detect login info!"
  273.         cecho "Found potential sockets: $found_socks"
  274.         cecho "Using: $socket" red
  275.         read -p "Would you like to provide a different socket?: [y/N] " REPLY
  276.                 case $REPLY in
  277.                         yes | y | Y | YES)
  278.                         read -p "Socket: " socket
  279.                         ;;
  280.                 esac
  281.         read -p "Do you have your login handy ? [y/N] : " REPLY
  282.         case $REPLY in
  283.                 yes | y | Y | YES)
  284.                 answer1='yes'
  285.                 read -p "User: " user
  286.                 read -rp "Password: " pass
  287.                 if [ -z $pass ] ; then
  288.                 export mysql="$mysql -S$socket -u$user"
  289.                 export mysqladmin="$mysqladmin -S$socket -u$user"
  290.                 else
  291.                 export mysql="$mysql -S$socket -u$user -p$pass"
  292.                 export mysqladmin="$mysqladmin -S$socket -u$user -p$pass"
  293.                 fi
  294.                 ;;
  295.                 *)
  296.                 cecho "Please create a valid login to MySQL"
  297.                 cecho "Or, set correct values for  'user=' and 'password=' in ~/.my.cnf"
  298.                 ;;
  299.         esac
  300.         cecho " "
  301.         read -p "Would you like me to create a ~/.my.cnf file for you? [y/N] : " REPLY
  302.         case $REPLY in
  303.                 yes | y | Y | YES)
  304.                 answer2='yes'
  305.                 if [ ! -f ~/.my.cnf ] ; then
  306.                         umask 077
  307.                         printf "[client]\nuser=$user\npassword=$pass\nsocket=$socket" > ~/.my.cnf
  308.                         if [ "$answer1" != 'yes' ] ; then
  309.                                 exit 1
  310.                         else
  311.                                 final_login_attempt
  312.                                 return 0
  313.                         fi
  314.                 else
  315.                         printf "\n"
  316.                         cecho "~/.my.cnf already exists!" boldred
  317.                         printf "\n"
  318.                         read -p "Replace ? [y/N] : " REPLY
  319.                         if [ "$REPLY" = 'y' ] || [ "$REPLY" = 'Y' ] ; then
  320.                         printf "[client]\nuser=$user\npassword=$pass\socket=$socket" > ~/.my.cnf
  321.                                 if [ "$answer1" != 'yes' ] ; then
  322.                                         exit 1
  323.                                 else
  324.                                         final_login_attempt
  325.                                         return 0
  326.                                 fi
  327.                         else
  328.                                 cecho "Please set the 'user=' and 'password=' and 'socket=' values in ~/.my.cnf"
  329.                                 exit 1
  330.                         fi
  331.                 fi
  332.                 ;;
  333.                 *)
  334.                 if [ "$answer1" != 'yes' ] ; then
  335.                         exit 1
  336.                 else
  337.                         final_login_attempt
  338.                         return 0
  339.                 fi
  340.                 ;;
  341.         esac
  342. }
  343.  
  344. find_webmin_passwords () {
  345.  
  346. ## -- populate the .my.cnf file using values harvested from Webmin -- ##
  347.  
  348.         cecho "Testing for stored webmin passwords:"
  349.         if [ -f /etc/webmin/mysql/config ] ; then
  350.                 user=$(grep ^login= /etc/webmin/mysql/config | cut -d "=" -f 2)
  351.                 pass=$(grep ^pass= /etc/webmin/mysql/config | cut -d "=" -f 2)
  352.                 if [  $user ] && [ $pass ] && [ ! -f ~/.my.cnf  ] ; then
  353.                         cecho "Setting login info as User: $user Password: $pass"
  354.                         touch ~/.my.cnf
  355.                         chmod 600 ~/.my.cnf
  356.                         printf "[client]\nuser=$user\npassword=$pass" > ~/.my.cnf
  357.                         cecho "Retrying login"
  358.                         is_up=$($mysqladmin ping 2>&1)
  359.                         if [ "$is_up" = "mysqld is alive"  ] ; then
  360.                                 echo UP > /dev/null
  361.                         else
  362.                                 second_login_failed
  363.                         fi
  364.                 echo
  365.                 else
  366.                         second_login_failed
  367.                 echo
  368.                 fi
  369.         else
  370.         cecho " None Found" boldred
  371.                 second_login_failed
  372.         fi
  373. }
  374.  
  375. #########################################################################
  376. #                                                                       #
  377. #  Function to pull MySQL status variable                               #
  378. #                                                                       #
  379. #  Call using :                                                         #
  380. #       mysql_status \'Mysql_status_variable\' bash_dest_variable       #
  381. #                                                                       #
  382. #########################################################################
  383.  
  384. mysql_status () {
  385.         local status=$($mysql -Bse "show /*!50000 global */ status like $1" | awk '{ print $2 }')
  386.         export "$2"=$status
  387. }
  388.  
  389. #########################################################################
  390. #                                                                       #
  391. #  Function to pull MySQL server runtime variable                       #
  392. #                                                                       #
  393. #  Call using :                                                         #
  394. #       mysql_variable \'Mysql_server_variable\' bash_dest_variable     #
  395. #       - OR -                                                          #
  396. #       mysql_variableTSV \'Mysql_server_variable\' bash_dest_variable  #
  397. #                                                                       #
  398. #########################################################################
  399.  
  400. mysql_variable () {
  401.         local variable=$($mysql -Bse "show /*!50000 global */ variables like $1" | awk '{ print $2 }')
  402.         export "$2"=$variable
  403. }
  404. mysql_variableTSV () {
  405.         local variable=$($mysql -Bse "show /*!50000 global */ variables like $1" | awk -F \t '{ print $2 }')
  406.         export "$2"=$variable
  407. }
  408.  
  409. float2int () {
  410.         local variable=$(echo "$1 / 1" | bc -l)
  411.         export "$2"=$variable
  412. }
  413.  
  414. divide () {
  415.  
  416. # -- Divide two intigers -- #
  417.  
  418.         usage="$0 dividend divisor '$variable' scale"
  419.         if [ $1 -ge 1 ] ; then
  420.                 dividend=$1
  421.         else
  422.                 cecho "Invalid Dividend" red
  423.                 echo $usage
  424.                 exit 1
  425.         fi
  426.         if [ $2 -ge 1 ] ; then
  427.                 divisor=$2
  428.         else
  429.                 cecho "Invalid Divisor" red
  430.                 echo $usage
  431.                 exit 1
  432.         fi
  433.         if [ ! -n $3 ] ; then
  434.                 cecho "Invalid variable name" red
  435.                 echo $usage
  436.                 exit 1
  437.         fi
  438.         if [ -z $4 ] ; then
  439.                 scale=2
  440.         elif [ $4 -ge 0 ] ; then
  441.                 scale=$4
  442.         else
  443.                 cecho "Invalid scale" red
  444.                 echo $usage
  445.                 exit 1
  446.         fi
  447.         export $3=$(echo "scale=$scale; $dividend / $divisor" | bc -l)
  448. }
  449.  
  450. human_readable () {
  451.  
  452. #########################################################################
  453. #                                                                       #
  454. #  Convert a value in to human readable size and populate a variable    #
  455. #  with the result.                                                     #
  456. #                                                                       #
  457. #  Call using:                                                          #
  458. #       human_readable $value 'variable name' [ places of precision]    #
  459. #                                                                       #
  460. #########################################################################
  461.  
  462.         ## value=$1
  463.         ## variable=$2
  464.         scale=$3
  465.  
  466.         if [ $1 -ge 1073741824 ] ; then
  467.                 if [ -z $3 ] ; then
  468.                         scale=2
  469.                 fi
  470.                 divide $1 1073741824 "$2" $scale
  471.                 unit="G"
  472.         elif [ $1 -ge 1048576 ] ; then
  473.                 if [ -z $3 ] ; then
  474.                         scale=0
  475.                 fi
  476.                 divide $1 1048576 "$2" $scale
  477.                 unit="M"
  478.         elif [ $1 -ge 1024 ] ; then
  479.                 if [ -z $3 ] ; then
  480.                         scale=0
  481.                 fi
  482.                 divide $1 1024 "$2" $scale
  483.                 unit="K"
  484.         else
  485.                 export "$2"=$1
  486.                 unit="bytes"
  487.         fi
  488.         # let "$2"=$HR
  489. }
  490.  
  491. human_readable_time () {
  492.  
  493. ########################################################################
  494. #                                                                      #
  495. #       Function to produce human readable time                        #
  496. #                                                                      #
  497. ########################################################################
  498.  
  499.         usage="$0 seconds 'variable'"
  500.         if [ -z $1 ] || [ -z $2 ] ; then
  501.                 cecho $usage red
  502.                 exit 1
  503.         fi
  504.         days=$(echo "scale=0 ; $1 / 86400" | bc -l)
  505.         remainder=$(echo "scale=0 ; $1 % 86400" | bc -l)
  506.         hours=$(echo "scale=0 ; $remainder / 3600" | bc -l)
  507.         remainder=$(echo "scale=0 ; $remainder % 3600" | bc -l)
  508.         minutes=$(echo "scale=0 ; $remainder / 60" | bc -l)
  509.         seconds=$(echo "scale=0 ; $remainder % 60" | bc -l)
  510.         export $2="$days days $hours hrs $minutes min $seconds sec"
  511. }
  512.  
  513. check_mysql_version () {
  514.  
  515. ## -- Print Version Info -- ##
  516.  
  517.         mysql_variable \'version\' mysql_version
  518.         mysql_variable \'version_compile_machine\' mysql_version_compile_machine
  519.        
  520. if [ "$mysql_version_num" -lt 050000 ]; then
  521.         cecho "MySQL Version $mysql_version $mysql_version_compile_machine is EOL please upgrade to MySQL 4.1 or later" boldred
  522. else
  523.         cecho "MySQL Version $mysql_version $mysql_version_compile_machine"
  524. fi
  525.  
  526.  
  527. }
  528.  
  529. post_uptime_warning () {
  530.  
  531. #########################################################################
  532. #                                                                       #
  533. #  Present a reminder that mysql must run for a couple of days to       #
  534. #  build up good numbers in server status variables before these tuning #
  535. #  suggestions should be used.                                          #
  536. #                                                                       #
  537. #########################################################################
  538.  
  539.         mysql_status \'Uptime\' uptime
  540.         mysql_status \'Threads_connected\' threads
  541.         queries_per_sec=$(($questions/$uptime))
  542.         human_readable_time $uptime uptimeHR
  543.  
  544.         cecho "Uptime = $uptimeHR"
  545.         cecho "Avg. qps = $queries_per_sec"
  546.         cecho "Total Questions = $questions"
  547.         cecho "Threads Connected = $threads"
  548.         echo
  549.  
  550.         if [ $uptime -gt 172800 ] ; then
  551.                 cecho "Server has been running for over 48hrs."
  552.                 cecho "It should be safe to follow these recommendations"
  553.         else
  554.                 cechon "Warning: " boldred
  555.                 cecho "Server has not been running for at least 48hrs." boldred
  556.                 cecho "It may not be safe to use these recommendations" boldred
  557.  
  558.         fi
  559.         echo ""
  560.         cecho "To find out more information on how each of these" red
  561.         cecho "runtime variables effects performance visit:" red
  562.         if [ "$major_version" = '3.23' ] || [ "$major_version" = '4.0' ] || [ "$major_version" = '4.1' ] ; then
  563.         cecho "http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html" boldblue
  564.         elif [ "$major_version" = '5.0' ] || [ "$mysql_version_num" -gt '050100' ]; then
  565.         cecho "http://dev.mysql.com/doc/refman/$major_version/en/server-system-variables.html" boldblue
  566.         else
  567.         cecho "UNSUPPORTED MYSQL VERSION" boldred
  568.         exit 1
  569.         fi
  570.         cecho "Visit http://www.mysql.com/products/enterprise/advisors.html" boldblue
  571.         cecho "for info about MySQL's Enterprise Monitoring and Advisory Service" boldblue
  572. }
  573.  
  574. check_slow_queries () {
  575.  
  576. ## -- Slow Queries -- ##
  577.  
  578.         cecho "SLOW QUERIES" boldblue
  579.  
  580.         mysql_status \'Slow_queries\' slow_queries
  581.         mysql_variable \'long_query_time\' long_query_time
  582.         mysql_variable \'log%queries\' log_slow_queries
  583.        
  584.         prefered_query_time=5
  585.         if [ -e /etc/my.cnf ] ; then
  586.                 if [ -z $log_slow_queries ] ; then
  587.                         log_slow_queries=$(grep log-slow-queries /etc/my.cnf)
  588.                 fi
  589.         fi
  590.  
  591.         if [ "$log_slow_queries" = 'ON' ] ; then
  592.                 cecho "The slow query log is enabled."
  593.         elif [ "$log_slow_queries" = 'OFF' ] ; then
  594.                 cechon "The slow query log is "
  595.                 cechon "NOT" boldred
  596.                 cecho " enabled."
  597.         elif [ -z $log_slow_queries ] ; then
  598.                 cechon "The slow query log is "
  599.                 cechon "NOT" boldred
  600.                 cecho " enabled."
  601.         else
  602.                 cecho "Error: $log_slow_queries" boldred
  603.         fi
  604.         cecho "Current long_query_time = $long_query_time sec."
  605.         cechon "You have "
  606.         cechon "$slow_queries" boldred
  607.         cechon " out of "
  608.         cechon "$questions" boldred
  609.         cecho " that take longer than $long_query_time sec. to complete"
  610.        
  611.         float2int long_query_time long_query_timeInt
  612.  
  613.         if [ $long_query_timeInt -gt $prefered_query_time ] ; then
  614.                 cecho "Your long_query_time may be too high, I typically set this under $prefered_query_time sec." red
  615.         else
  616.                 cecho "Your long_query_time seems to be fine" green
  617.         fi
  618.  
  619. }
  620.  
  621. check_binary_log () {
  622.  
  623. ## -- Binary Log -- ##
  624.  
  625.         cecho "BINARY UPDATE LOG" boldblue
  626.  
  627.         mysql_variable \'log_bin\' log_bin
  628.         mysql_variable \'max_binlog_size\' max_binlog_size
  629.         mysql_variable \'expire_logs_days\' expire_logs_days
  630.         mysql_variable \'sync_binlog\' sync_binlog
  631.         #  mysql_variable \'max_binlog_cache_size\' max_binlog_cache_size
  632.  
  633.         if [ "$log_bin" = 'ON' ] ; then
  634.                 cecho "The binary update log is enabled"
  635.                 if [ -z "$max_binlog_size" ] ; then
  636.                         cecho "The max_binlog_size is not set. The binary log will rotate when it reaches 1GB." red
  637.                 fi
  638.                 if [ "$expire_logs_days" -eq 0 ] ; then
  639.                         cecho "The expire_logs_days is not set." boldred
  640.                         cechon "The mysqld will retain the entire binary log until " red
  641.                         cecho "RESET MASTER or PURGE MASTER LOGS commands are run manually" red
  642.                         cecho "Setting expire_logs_days will allow you to remove old binary logs automatically"  yellow
  643.                         cecho "See http://dev.mysql.com/doc/refman/$major_version/en/purge-master-logs.html" yellow
  644.                 fi
  645.                 if [ "$sync_binlog" = 0 ] ; then
  646.                         cecho "Binlog sync is not enabled, you could loose binlog records during a server crash" red
  647.                 fi
  648.         else
  649.                 cechon "The binary update log is "
  650.                 cechon "NOT " boldred
  651.                 cecho "enabled."
  652.                 cecho "You will not be able to do point in time recovery" red
  653.                 cecho "See http://dev.mysql.com/doc/refman/$major_version/en/point-in-time-recovery.html" yellow
  654.         fi
  655. }
  656.  
  657. check_used_connections () {
  658.  
  659. ## -- Used Connections -- ##
  660.  
  661.         mysql_variable \'max_connections\' max_connections
  662.         mysql_status \'Max_used_connections\' max_used_connections
  663.         mysql_status \'Threads_connected\' threads_connected
  664.  
  665.         connections_ratio=$(($max_used_connections*100/$max_connections))
  666.  
  667.         cecho "MAX CONNECTIONS" boldblue
  668.         cecho "Current max_connections = $max_connections"
  669.         cecho "Current threads_connected = $threads_connected"
  670.         cecho "Historic max_used_connections = $max_used_connections"
  671.         cechon "The number of used connections is "
  672.         if [ $connections_ratio -ge 85 ] ; then
  673.                 txt_color=red
  674.                 error=1
  675.         elif [ $connections_ratio -le 10 ] ; then
  676.                 txt_color=red
  677.                 error=2
  678.         else
  679.                 txt_color=green
  680.                 error=0
  681.         fi
  682.         # cechon "$max_used_connections " $txt_color
  683.         # cechon "which is "
  684.         cechon "$connections_ratio% " $txt_color
  685.         cecho "of the configured maximum."
  686.  
  687.         if [ $error -eq 1 ] ; then
  688.                 cecho "You should raise max_connections" $txt_color
  689.         elif [ $error -eq 2 ] ; then
  690.                 cecho "You are using less than 10% of your configured max_connections." $txt_color
  691.                 cecho "Lowering max_connections could help to avoid an over-allocation of memory" $txt_color
  692.                 cecho "See \"MEMORY USAGE\" section to make sure you are not over-allocating" $txt_color
  693.         else
  694.                 cecho "Your max_connections variable seems to be fine." $txt_color
  695.         fi
  696.         unset txt_color
  697. }
  698.  
  699. check_threads() {
  700.  
  701. ## -- Worker Threads -- ##
  702.  
  703.         cecho "WORKER THREADS" boldblue
  704.  
  705.         mysql_status \'Threads_created\' threads_created1
  706.         sleep 1
  707.         mysql_status \'Threads_created\' threads_created2
  708.  
  709.         mysql_status \'Threads_cached\' threads_cached
  710.         mysql_status \'Uptime\' uptime
  711.         mysql_variable \'thread_cache_size\' thread_cache_size
  712.  
  713.         historic_threads_per_sec=$(($threads_created1/$uptime))
  714.         current_threads_per_sec=$(($threads_created2-$threads_created1))
  715.  
  716.         cecho "Current thread_cache_size = $thread_cache_size"
  717.         cecho "Current threads_cached = $threads_cached"
  718.         cecho "Current threads_per_sec = $current_threads_per_sec"
  719.         cecho "Historic threads_per_sec = $historic_threads_per_sec"
  720.  
  721.         if [ $historic_threads_per_sec -ge 2 ] && [ $threads_cached -le 1 ] ; then
  722.                 cecho "Threads created per/sec are overrunning threads cached" red
  723.                 cecho "You should raise thread_cache_size" red
  724.         elif [ $current_threads_per_sec -ge 2 ] ; then
  725.                 cecho "Threads created per/sec are overrunning threads cached" red
  726.                 cecho "You should raise thread_cache_size" red
  727.         else
  728.                 cecho "Your thread_cache_size is fine" green
  729.         fi
  730. }
  731.  
  732. check_key_buffer_size () {
  733.  
  734. ## -- Key buffer Size -- ##
  735.  
  736.         cecho "KEY BUFFER" boldblue
  737.  
  738.         mysql_status \'Key_read_requests\' key_read_requests
  739.         mysql_status \'Key_reads\' key_reads
  740.         mysql_status \'Key_blocks_used\' key_blocks_used
  741.         mysql_status \'Key_blocks_unused\' key_blocks_unused
  742.         mysql_variable \'key_cache_block_size\' key_cache_block_size
  743.         mysql_variable \'key_buffer_size\' key_buffer_size
  744.         mysql_variable \'datadir\' datadir
  745.         mysql_variable \'version_compile_machine\' mysql_version_compile_machine
  746.         myisam_indexes=$($mysql -Bse "/*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='MyISAM' */")
  747.  
  748.         if [ -z $myisam_indexes ] ; then
  749.                 myisam_indexes=$(find $datadir -name '*.MYI' -exec du $duflags '{}' \; 2>&1 | awk '{ s += $1 } END { printf("%.0f\n", s )}')
  750.         fi
  751.  
  752.         if [ $key_reads -eq 0 ] ; then
  753.                 cecho "No key reads?!" boldred
  754.                 cecho "Seriously look into using some indexes" red
  755.                 key_cache_miss_rate=0
  756.                 key_buffer_free=$(echo "$key_blocks_unused * $key_cache_block_size / $key_buffer_size * 100" | bc -l )
  757.                 key_buffer_freeRND=$(echo "scale=0; $key_buffer_free / 1" | bc -l)
  758.         else
  759.                 key_cache_miss_rate=$(($key_read_requests/$key_reads))
  760.                 if [ ! -z $key_blocks_unused ] ; then
  761.                         key_buffer_free=$(echo "$key_blocks_unused * $key_cache_block_size / $key_buffer_size * 100" | bc -l )
  762.                         key_buffer_freeRND=$(echo "scale=0; $key_buffer_free / 1" | bc -l)
  763.                 else
  764.                         key_buffer_free='Unknown'
  765.                         key_buffer_freeRND=75
  766.                 fi
  767.         fi
  768.  
  769.         human_readable $myisam_indexes myisam_indexesHR
  770.         cecho "Current MyISAM index space = $myisam_indexesHR $unit"
  771.  
  772.         human_readable  $key_buffer_size key_buffer_sizeHR
  773.         cecho "Current key_buffer_size = $key_buffer_sizeHR $unit"
  774.         cecho "Key cache miss rate is 1 : $key_cache_miss_rate"
  775.         cecho "Key buffer free ratio = $key_buffer_freeRND %"
  776.  
  777.         if [ "$major_version" = '5.1' ] && [ $mysql_version_num -lt 050123 ] ; then
  778.                 if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then
  779.                         cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.1.23 " boldred
  780.                         cecho "See Bug#5731, Bug#29419, Bug#29446" boldred
  781.                 fi
  782.         fi
  783.         if [ "$major_version" = '5.0' ] && [ $mysql_version_num -lt 050052 ] ; then
  784.                 if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then
  785.                         cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " boldred
  786.                         cecho "See Bug#5731, Bug#29419, Bug#29446" boldred
  787.                 fi
  788.         fi
  789.         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
  790.                 cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " boldred
  791.                 cecho "Reduce key_buffer_size to a safe value" boldred
  792.                 cecho "See Bug#5731, Bug#29419, Bug#29446" boldred
  793.         fi
  794.  
  795.         if [ $key_cache_miss_rate -le 100 ] && [ $key_cache_miss_rate -gt 0 ] && [ $key_buffer_freeRND -le 20 ]; then
  796.                 cecho "You could increase key_buffer_size" boldred
  797.                 cecho "It is safe to raise this up to 1/4 of total system memory;"
  798.                 cecho "assuming this is a dedicated database server."
  799.         elif [ $key_buffer_freeRND -le 20 ] && [ $key_buffer_size -le $myisam_indexes ] ; then
  800.                 cecho "You could increase key_buffer_size" boldred
  801.                 cecho "It is safe to raise this up to 1/4 of total system memory;"
  802.                 cecho "assuming this is a dedicated database server."
  803.         elif [ $key_cache_miss_rate -ge 10000 ] || [ $key_buffer_freeRND -le 50  ] ; then
  804.                 cecho "Your key_buffer_size seems to be too high." red
  805.                 cecho "Perhaps you can use these resources elsewhere" red
  806.         else
  807.                 cecho "Your key_buffer_size seems to be fine" green
  808.         fi
  809. }
  810.  
  811. check_query_cache () {
  812.  
  813. ## -- Query Cache -- ##
  814.  
  815.         cecho "QUERY CACHE" boldblue
  816.  
  817.         mysql_variable \'version\' mysql_version
  818.         mysql_variable \'query_cache_size\' query_cache_size
  819.         mysql_variable \'query_cache_limit\' query_cache_limit
  820.         mysql_variable \'query_cache_min_res_unit\' query_cache_min_res_unit
  821.         mysql_status \'Qcache_free_memory\' qcache_free_memory
  822.         mysql_status \'Qcache_total_blocks\' qcache_total_blocks
  823.         mysql_status \'Qcache_free_blocks\' qcache_free_blocks
  824.         mysql_status \'Qcache_lowmem_prunes\' qcache_lowmem_prunes
  825.  
  826.         if [ -z $query_cache_size ] ; then
  827.                 cecho "You are using MySQL $mysql_version, no query cache is supported." red
  828.                 cecho "I recommend an upgrade to MySQL 4.1 or better" red
  829.         elif [ $query_cache_size -eq 0 ] ; then
  830.                 cecho "Query cache is supported but not enabled" red
  831.                 cecho "Perhaps you should set the query_cache_size" red
  832.         else
  833.                 qcache_used_memory=$(($query_cache_size-$qcache_free_memory))
  834.                 qcache_mem_fill_ratio=$(echo "scale=2; $qcache_used_memory * 100 / $query_cache_size" | bc -l)
  835.                 qcache_mem_fill_ratioHR=$(echo "scale=0; $qcache_mem_fill_ratio / 1" | bc -l)
  836.  
  837.                 cecho "Query cache is enabled" green
  838.                 human_readable $query_cache_size query_cache_sizeHR
  839.                 cecho "Current query_cache_size = $query_cache_sizeHR $unit"
  840.                 human_readable $qcache_used_memory qcache_used_memoryHR
  841.                 cecho "Current query_cache_used = $qcache_used_memoryHR $unit"
  842.                 human_readable $query_cache_limit query_cache_limitHR
  843.                 cecho "Current query_cache_limit = $query_cache_limitHR $unit"
  844.                 cecho "Current Query cache Memory fill ratio = $qcache_mem_fill_ratio %"
  845.                 if [ -z $query_cache_min_res_unit ] ; then
  846.                         cecho "No query_cache_min_res_unit is defined.  Using MySQL < 4.1 cache fragmentation can be inpredictable" %yellow
  847.                 else
  848.                         human_readable $query_cache_min_res_unit query_cache_min_res_unitHR
  849.                         cecho "Current query_cache_min_res_unit = $query_cache_min_res_unitHR $unit"
  850.                 fi
  851.                 if [ $qcache_free_blocks -gt 2 ] && [ $qcache_total_blocks -gt 0 ] ; then
  852.                         qcache_percent_fragmented=$(echo "scale=2; $qcache_free_blocks * 100 / $qcache_total_blocks" | bc -l)
  853.                         qcache_percent_fragmentedHR=$(echo "scale=0; $qcache_percent_fragmented / 1" | bc -l)
  854.                         if [ $qcache_percent_fragmentedHR -gt 20 ] ; then
  855.                                 cecho "Query Cache is $qcache_percent_fragmentedHR % fragmented" red
  856.                                 cecho "Run \"FLUSH QUERY CACHE\" periodically to defragment the query cache memory" red
  857.                                 cecho "If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation." red
  858.                         fi
  859.                 fi
  860.  
  861.                 if [ $qcache_mem_fill_ratioHR -le 25 ] ; then
  862.                         cecho "Your query_cache_size seems to be too high." red
  863.                         cecho "Perhaps you can use these resources elsewhere" red
  864.                 fi
  865.                 if [ $qcache_lowmem_prunes -ge 50 ] && [ $qcache_mem_fill_ratioHR -ge 80 ]; then
  866.                         cechon "However, "
  867.                         cechon "$qcache_lowmem_prunes " boldred
  868.                         cecho "queries have been removed from the query cache due to lack of memory"
  869.                         cecho "Perhaps you should raise query_cache_size" boldred
  870.                 fi
  871.                 cecho "MySQL won't cache query results that are larger than query_cache_limit in size" yellow
  872.         fi
  873.  
  874. }
  875.  
  876. check_sort_operations () {
  877.  
  878. ## -- Sort Operations -- ##
  879.  
  880.         cecho "SORT OPERATIONS" boldblue
  881.  
  882.         mysql_status \'Sort_merge_passes\' sort_merge_passes
  883.         mysql_status \'Sort_scan\' sort_scan
  884.         mysql_status \'Sort_range\' sort_range
  885.         mysql_variable \'sort_buffer%\' sort_buffer_size
  886.         mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size
  887.  
  888.         total_sorts=$(($sort_scan+$sort_range))
  889.         if [ -z $read_rnd_buffer_size ] ; then
  890.                 mysql_variable \'record_buffer\' read_rnd_buffer_size
  891.         fi
  892.  
  893.         ## Correct for rounding error in mysqld where 512K != 524288 ##
  894.         sort_buffer_size=$(($sort_buffer_size+8))
  895.         read_rnd_buffer_size=$(($read_rnd_buffer_size+8))
  896.  
  897.         human_readable $sort_buffer_size sort_buffer_sizeHR
  898.         cecho "Current sort_buffer_size = $sort_buffer_sizeHR $unit"
  899.  
  900.         human_readable $read_rnd_buffer_size read_rnd_buffer_sizeHR
  901.         cechon "Current "
  902.         if [ "$major_version" = '3.23' ] ; then
  903.                 cechon "record_rnd_buffer "
  904.         else
  905.                 cechon "read_rnd_buffer_size "
  906.         fi
  907.         cecho "= $read_rnd_buffer_sizeHR $unit"
  908.  
  909.         if [ $total_sorts -eq 0 ] ; then
  910.                 cecho "No sort operations have been performed"
  911.                 passes_per_sort=0
  912.         fi
  913.         if [ $sort_merge_passes -ne 0 ] ; then
  914.                 passes_per_sort=$(($sort_merge_passes/$total_sorts))
  915.         else
  916.                 passes_per_sort=0
  917.         fi
  918.  
  919.         if [ $passes_per_sort -ge 2 ] ; then
  920.                 cechon "On average "
  921.                 cechon "$passes_per_sort " boldred
  922.                 cecho "sort merge passes are made per sort operation"
  923.                 cecho "You should raise your sort_buffer_size"
  924.                 cechon "You should also raise your "
  925.                 if [ "$major_version" = '3.23' ] ; then
  926.                         cecho "record_rnd_buffer_size"
  927.                 else
  928.                         cecho "read_rnd_buffer_size"
  929.                 fi
  930.         else
  931.                 cecho "Sort buffer seems to be fine" green
  932.         fi
  933. }
  934.  
  935. check_join_operations () {
  936.  
  937. ## -- Joins -- ##
  938.  
  939.         cecho "JOINS" boldblue
  940.  
  941.         mysql_status \'Select_full_join\' select_full_join
  942.         mysql_status \'Select_range_check\' select_range_check
  943.         mysql_variable \'join_buffer%\' join_buffer_size
  944.        
  945.         ## Some 4K is dropped from join_buffer_size adding it back to make sane ##
  946.         ## handling of human-readable conversion ##
  947.  
  948.         join_buffer_size=$(($join_buffer_size+4096))
  949.  
  950.         human_readable $join_buffer_size join_buffer_sizeHR 2
  951.  
  952.         cecho "Current join_buffer_size = $join_buffer_sizeHR $unit"
  953.         cecho "You have had $select_full_join queries where a join could not use an index properly"
  954.  
  955.         if [ $select_range_check -eq 0 ] && [ $select_full_join -eq 0 ] ; then
  956.                 cecho "Your joins seem to be using indexes properly" green
  957.         fi
  958.         if [ $select_full_join -gt 0 ] ; then
  959.                 print_error='true'
  960.                 raise_buffer='true'
  961.         fi
  962.         if [ $select_range_check -gt 0 ] ; then
  963.                 cecho "You have had $select_range_check joins without keys that check for key usage after each row" red
  964.                 print_error='true'
  965.                 raise_buffer='true'
  966.         fi
  967.  
  968.         ## For Debuging ##
  969.         # print_error='true'
  970.         if [ $join_buffer_size -ge 4194304 ] ; then
  971.                 cecho "join_buffer_size >= 4 M" boldred
  972.                 cecho "This is not advised" boldred
  973.                 raise_buffer=
  974.         fi
  975.  
  976.         if [ $print_error ] ; then
  977.                 if [ "$major_version" = '3.23' ] || [ "$major_version" = '4.0' ] ; then
  978.                         cecho "You should enable \"log-long-format\" "
  979.                 elif [ "$mysql_version_num" -gt 040100 ]; then
  980.                         cecho "You should enable \"log-queries-not-using-indexes\""
  981.                 fi
  982.                 cecho "Then look for non indexed joins in the slow query log."
  983.                 if [ $raise_buffer ] ; then
  984.                 cecho "If you are unable to optimize your queries you may want to increase your"
  985.                 cecho "join_buffer_size to accommodate larger joins in one pass."
  986.                 printf "\n"
  987.                 cecho "Note! This script will still suggest raising the join_buffer_size when" boldred
  988.                 cecho "ANY joins not using indexes are found." boldred
  989.                 fi
  990.         fi
  991.  
  992.         # XXX Add better tests for join_buffer_size pending mysql bug #15088  XXX #
  993. }
  994.  
  995. check_tmp_tables () {
  996.  
  997. ## -- Temp Tables -- ##
  998.  
  999.         cecho "TEMP TABLES" boldblue
  1000.  
  1001.         mysql_status \'Created_tmp_tables\' created_tmp_tables
  1002.         mysql_status \'Created_tmp_disk_tables\' created_tmp_disk_tables
  1003.         mysql_variable \'tmp_table_size\' tmp_table_size
  1004.         mysql_variable \'max_heap_table_size\' max_heap_table_size
  1005.  
  1006.  
  1007.         if [ $created_tmp_tables -eq 0 ] ; then
  1008.                 tmp_disk_tables=0
  1009.         else
  1010.                 tmp_disk_tables=$((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables)))
  1011.         fi
  1012.         human_readable $max_heap_table_size max_heap_table_sizeHR
  1013.         cecho "Current max_heap_table_size = $max_heap_table_sizeHR $unit"
  1014.  
  1015.         human_readable $tmp_table_size tmp_table_sizeHR
  1016.         cecho "Current tmp_table_size = $tmp_table_sizeHR $unit"
  1017.  
  1018.         cecho "Of $created_tmp_tables temp tables, $tmp_disk_tables% were created on disk"
  1019.         if [ $tmp_table_size -gt $max_heap_table_size ] ; then
  1020.                 cecho "Effective in-memory tmp_table_size is limited to max_heap_table_size." yellow
  1021.         fi
  1022.         if [ $tmp_disk_tables -ge 25 ] ; then
  1023.                 cecho "Perhaps you should increase your tmp_table_size and/or max_heap_table_size" boldred
  1024.                 cecho "to reduce the number of disk-based temporary tables" boldred
  1025.                 cecho "Note! BLOB and TEXT columns are not allow in memory tables." yellow
  1026.                 cecho "If you are using these columns raising these values might not impact your " yellow
  1027.                 cecho  "ratio of on disk temp tables." yellow
  1028.         else
  1029.                 cecho "Created disk tmp tables ratio seems fine" green
  1030.         fi
  1031. }
  1032.  
  1033. check_open_files () {
  1034.  
  1035. ## -- Open Files Limit -- ##
  1036.         cecho "OPEN FILES LIMIT" boldblue
  1037.  
  1038.         mysql_variable \'open_files_limit\' open_files_limit
  1039.         mysql_status   \'Open_files\' open_files
  1040.        
  1041.         if [ -z $open_files_limit ] || [ $open_files_limit -eq 0 ] ; then
  1042.                 open_files_limit=$(ulimit -n)
  1043.                 cant_override=1
  1044.         else
  1045.                 cant_override=0
  1046.         fi
  1047.         cecho "Current open_files_limit = $open_files_limit files"
  1048.        
  1049.         open_files_ratio=$(($open_files*100/$open_files_limit))
  1050.  
  1051.         cecho "The open_files_limit should typically be set to at least 2x-3x" yellow
  1052.         cecho "that of table_cache if you have heavy MyISAM usage." yellow
  1053.         if [ $open_files_ratio -ge 75 ] ; then
  1054.                 cecho "You currently have open more than 75% of your open_files_limit" boldred
  1055.                 if [ $cant_override -eq 1 ] ; then
  1056.                         cecho "You should set a higer value for ulimit -u in the mysql startup script then restart mysqld" boldred
  1057.                         cecho "MySQL 3.23 users : This is just a guess based upon the current shell's ulimit -u value" yellow
  1058.                 elif [ $cant_override -eq 0 ] ; then
  1059.                         cecho "You should set a higher value for open_files_limit in my.cnf" boldred
  1060.                 else
  1061.                         cecho "ERROR can't determine if mysqld override of ulimit is allowed" boldred
  1062.                         exit 1
  1063.                 fi
  1064.         else
  1065.                 cecho "Your open_files_limit value seems to be fine" green
  1066.         fi
  1067.  
  1068.        
  1069.  
  1070. }
  1071.  
  1072. check_table_cache () {
  1073.  
  1074. ## -- Table Cache -- ##
  1075.  
  1076.         cecho "TABLE CACHE" boldblue
  1077.  
  1078.         mysql_variable \'datadir\' datadir
  1079.         mysql_variable \'table_cache\' table_cache
  1080.  
  1081.         ## /* MySQL +5.1 version of table_cache */ ##
  1082.         mysql_variable \'table_open_cache\' table_open_cache
  1083.         mysql_variable \'table_definition_cache\' table_definition_cache
  1084.  
  1085.         mysql_status \'Open_tables\' open_tables
  1086.         mysql_status \'Opened_tables\' opened_tables
  1087.         mysql_status \'Open_table_definitions\' open_table_definitions
  1088.  
  1089.         table_count=$($mysql -Bse "/*!50000 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' */")
  1090.  
  1091.         if [ -z "$table_count" ] ; then
  1092.                 if [ "$UID" != "$socket_owner" ] && [ "$UID" != "0" ] ; then
  1093.                         cecho "You are not '$socket_owner' or 'root'" red
  1094.                         cecho "I am unable to determine the table_count!" red
  1095.                 else
  1096.                         table_count=$(find $datadir 2>&1 | grep -c .frm$)
  1097.                 fi
  1098.         fi
  1099.         if [ $table_open_cache ] ; then
  1100.                 table_cache=$table_open_cache
  1101.         fi
  1102.  
  1103.         if [ $opened_tables -ne 0 ] && [ $table_cache -ne 0 ] ; then
  1104.                 table_cache_hit_rate=$(($open_tables*100/$opened_tables))
  1105.                 table_cache_fill=$(($open_tables*100/$table_cache))
  1106.         elif [ $opened_tables -eq 0 ] && [ $table_cache -ne 0 ] ; then
  1107.                 table_cache_hit_rate=100
  1108.                 table_cache_fill=$(($open_tables*100/$table_cache))
  1109.         else
  1110.                 cecho "ERROR no table_cache ?!" boldred
  1111.                 exit 1
  1112.         fi
  1113.         if [ $table_cache ] && [ ! $table_open_cache ] ; then
  1114.                 cecho "Current table_cache value = $table_cache tables"
  1115.         fi
  1116.         if [ $table_open_cache ] ; then
  1117.                 cecho "Current table_open_cache = $table_open_cache tables"
  1118.                 cecho "Current table_definition_cache = $table_definition_cache tables"
  1119.         fi
  1120.         if [ $table_count ] ; then
  1121.         cecho "You have a total of $table_count tables"
  1122.         fi
  1123.  
  1124.         if  [ $table_cache_fill -lt 95 ] ; then
  1125.                 cechon "You have "
  1126.                 cechon "$open_tables " green
  1127.                 cecho "open tables."
  1128.                 cecho "The table_cache value seems to be fine" green
  1129.         elif [ $table_cache_hit_rate -le 85 -o  $table_cache_fill -ge 95 ]; then
  1130.                 cechon "You have "
  1131.                 cechon "$open_tables " boldred
  1132.                 cecho "open tables."
  1133.                 cechon "Current table_cache hit rate is "
  1134.                 cecho "$table_cache_hit_rate%" boldred
  1135.                 cechon ", while "
  1136.                 cechon "$table_cache_fill% " boldred
  1137.                 cecho "of your table cache is in use"
  1138.                 cecho "You should probably increase your table_cache" red
  1139.         else
  1140.                 cechon "Current table_cache hit rate is "
  1141.                 cechon "$table_cache_hit_rate%" green
  1142.                 cechon ", while "
  1143.                 cechon "$table_cache_fill% " green
  1144.                 cecho "of your table cache is in use"
  1145.                 cecho "The table cache value seems to be fine" green
  1146.         fi
  1147.         if [ $table_definition_cache ] && [ $table_definition_cache -le $table_count ] && [ $table_count -ge 100 ] ; then
  1148.                 cecho "You should probably increase your table_definition_cache value." red
  1149.         fi
  1150. }
  1151.  
  1152. check_table_locking () {
  1153.  
  1154. ## -- Table Locking -- ##
  1155.  
  1156.         cecho "TABLE LOCKING" boldblue
  1157.  
  1158.         mysql_status \'Table_locks_waited\' table_locks_waited
  1159.         mysql_status \'Table_locks_immediate\' table_locks_immediate
  1160.         mysql_variable \'concurrent_insert\' concurrent_insert
  1161.         mysql_variable \'low_priority_updates\' low_priority_updates
  1162.         if [ "$concurrent_insert" = 'ON' ]; then
  1163.                 concurrent_insert=1
  1164.         elif [ "$concurrent_insert" = 'OFF' ]; then
  1165.                 concurrent_insert=0
  1166.         fi
  1167.  
  1168.         cechon "Current Lock Wait ratio = "
  1169.         if [ $table_locks_waited -gt 0 ]; then
  1170.                 immediate_locks_miss_rate=$(($table_locks_immediate/$table_locks_waited))
  1171.                 cecho "1 : $immediate_locks_miss_rate" red
  1172.         else
  1173.                 immediate_locks_miss_rate=99999 # perfect
  1174.                 cecho "0 : $questions"
  1175.         fi
  1176.         if [ $immediate_locks_miss_rate -lt 5000 ] ; then
  1177.                 cecho "You may benefit from selective use of InnoDB."
  1178.                 if [ "$low_priority_updates" = 'OFF' ] ; then
  1179.                 cecho "If you have long running SELECT's against MyISAM tables and perform"
  1180.                 cecho "frequent updates consider setting 'low_priority_updates=1'"
  1181.                 fi
  1182.                 if [ "$mysql_version_num" -gt 050000 ] && [ "$mysql_version_num" -lt 050500 ]; then
  1183.                         if [ $concurrent_insert -le 1 ] ; then
  1184.                         cecho "If you have a high concurrency of inserts on Dynamic row-length tables"
  1185.                         cecho "consider setting 'concurrent_insert=2'."
  1186.                         fi
  1187.                 elif [ "$mysql_version_num" -gt 050500 ] ; then
  1188.                         if [ "$concurrent_insert" = 'AUTO' ] || [ "$concurrent_insert" = 'NEVER' ] ; then
  1189.                         cecho "If you have a high concurrency of inserts on Dynamic row-length tables"
  1190.                         cecho "consider setting 'concurrent_insert=ALWAYS'."
  1191.                         fi
  1192.                 fi
  1193.         else
  1194.                 cecho "Your table locking seems to be fine" green
  1195.         fi
  1196. }
  1197.  
  1198. check_table_scans () {
  1199.  
  1200. ## -- Table Scans -- ##
  1201.  
  1202.         cecho "TABLE SCANS" boldblue
  1203.  
  1204.         mysql_status \'Com_select\' com_select
  1205.         mysql_status \'Handler_read_rnd_next\' read_rnd_next
  1206.         mysql_variable \'read_buffer_size\' read_buffer_size
  1207.  
  1208.         if [ -z $read_buffer_size ] ; then
  1209.                 mysql_variable \'record_buffer\' read_buffer_size
  1210.         fi
  1211.  
  1212.         human_readable $read_buffer_size read_buffer_sizeHR
  1213.         cecho "Current read_buffer_size = $read_buffer_sizeHR $unit"
  1214.        
  1215.         if [ $com_select -gt 0 ] ; then
  1216.                 full_table_scans=$(($read_rnd_next/$com_select))
  1217.                 cecho "Current table scan ratio = $full_table_scans : 1"
  1218.                 if [ $full_table_scans -ge 4000 ] && [ $read_buffer_size -le 2097152 ] ; then
  1219.                         cecho "You have a high ratio of sequential access requests to SELECTs" red
  1220.                         cechon "You may benefit from raising " red
  1221.                         if [ "$major_version" = '3.23' ] ; then
  1222.                                 cechon "record_buffer " red
  1223.                         else
  1224.                                 cechon "read_buffer_size " red
  1225.                         fi
  1226.                         cecho "and/or improving your use of indexes." red
  1227.                 elif [ $read_buffer_size -gt 8388608 ] ; then
  1228.                         cechon "read_buffer_size is over 8 MB " red
  1229.                         cecho "there is probably no need for such a large read_buffer" red
  1230.                
  1231.                 else
  1232.                         cecho "read_buffer_size seems to be fine" green
  1233.                 fi
  1234.         else
  1235.                 cecho "read_buffer_size seems to be fine" green
  1236.         fi
  1237. }
  1238.  
  1239.  
  1240. check_innodb_status () {
  1241.  
  1242. ## -- InnoDB -- ##
  1243.  
  1244.         ## See http://bugs.mysql.com/59393
  1245.  
  1246.         if [ "$mysql_version_num" -lt 050603 ] ; then
  1247.         mysql_variable \'have_innodb\' have_innodb
  1248.         fi
  1249.         if [ "$mysql_version_num" -lt 050500 ] && [ "$have_innodb" = "YES" ] ; then
  1250.         innodb_enabled=1
  1251.         fi
  1252.         if [ "$mysql_version_num" -ge 050500 ] && [ "$mysql_version_num" -lt 050512 ] ; then
  1253.         mysql_variable \'ignore_builtin_innodb\' ignore_builtin_innodb
  1254.                 if [ "$ignore_builtin_innodb" = "ON" ] || [ $have_innodb = "NO" ] ; then
  1255.                 innodb_enabled=0
  1256.                 else
  1257.                 innodb_enabled=1
  1258.                 fi
  1259.         elif [ "$major_version"  = '5.5' ] && [ "$mysql_version_num" -ge 050512 ] ; then
  1260.         mysql_variable \'ignore_builtin_innodb\' ignore_builtin_innodb
  1261.                 if [ "$ignore_builtin_innodb" = "ON" ] ; then
  1262.                 innodb_enabled=0
  1263.                 else
  1264.                 innodb_enabled=1
  1265.                 fi
  1266.         elif [ "$mysql_version_num" -ge 050600 ] && [ "$mysql_version_num" -lt 050603 ] ; then
  1267.         mysql_variable \'ignore_builtin_innodb\' ignore_builtin_innodb
  1268.                 if [ "$ignore_builtin_innodb" = "ON" ] || [ $have_innodb = "NO" ] ; then
  1269.                 innodb_enabled=0
  1270.                 else
  1271.                 innodb_enabled=1
  1272.                 fi
  1273.         elif [ "$major_version" = '5.6' ] && [ "$mysql_version_num" -ge 050603 ] ; then
  1274.         mysql_variable \'ignore_builtin_innodb\' ignore_builtin_innodb
  1275.                 if [ "$ignore_builtin_innodb" = "ON" ] ; then
  1276.                 innodb_enabled=0
  1277.                 else
  1278.                 innodb_enabled=1
  1279.                 fi
  1280.         fi
  1281.         if [ "$innodb_enabled" = 1 ] ; then
  1282.                 mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size
  1283.                 mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size
  1284.                 mysql_variable \'innodb_fast_shutdown\' innodb_fast_shutdown
  1285.                 mysql_variable \'innodb_flush_log_at_trx_commit\' innodb_flush_log_at_trx_commit
  1286.                 mysql_variable \'innodb_locks_unsafe_for_binlog\' innodb_locks_unsafe_for_binlog
  1287.                 mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size
  1288.                 mysql_variable \'innodb_log_file_size\' innodb_log_file_size
  1289.                 mysql_variable \'innodb_log_files_in_group\' innodb_log_files_in_group
  1290.                 mysql_variable \'innodb_safe_binlog\' innodb_safe_binlog
  1291.                 mysql_variable \'innodb_thread_concurrency\' innodb_thread_concurrency
  1292.  
  1293.                 cecho "INNODB STATUS" boldblue
  1294.                 innodb_indexes=$($mysql -Bse "/*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */")
  1295.                 innodb_data=$($mysql -Bse "/*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */")
  1296.                
  1297.                 if [ ! -z "$innodb_indexes" ] ; then
  1298.  
  1299.                 mysql_status \'Innodb_buffer_pool_pages_data\' innodb_buffer_pool_pages_data
  1300.                 mysql_status \'Innodb_buffer_pool_pages_misc\' innodb_buffer_pool_pages_misc
  1301.                 mysql_status \'Innodb_buffer_pool_pages_free\' innodb_buffer_pool_pages_free
  1302.                 mysql_status \'Innodb_buffer_pool_pages_total\' innodb_buffer_pool_pages_total
  1303.  
  1304.                 mysql_status \'Innodb_buffer_pool_read_ahead_seq\' innodb_buffer_pool_read_ahead_seq
  1305.                 mysql_status \'Innodb_buffer_pool_read_requests\' innodb_buffer_pool_read_requests
  1306.  
  1307.                 mysql_status \'Innodb_os_log_pending_fsyncs\' innodb_os_log_pending_fsyncs
  1308.                 mysql_status \'Innodb_os_log_pending_writes\'   innodb_os_log_pending_writes
  1309.                 mysql_status \'Innodb_log_waits\' innodb_log_waits
  1310.  
  1311.                 mysql_status \'Innodb_row_lock_time\' innodb_row_lock_time
  1312.                 mysql_status \'Innodb_row_lock_waits\' innodb_row_lock_waits
  1313.  
  1314.                 human_readable $innodb_indexes innodb_indexesHR
  1315.                 cecho "Current InnoDB index space = $innodb_indexesHR $unit"
  1316.                 human_readable $innodb_data innodb_dataHR
  1317.                 cecho "Current InnoDB data space = $innodb_dataHR $unit"
  1318.                 percent_innodb_buffer_pool_free=$(($innodb_buffer_pool_pages_free*100/$innodb_buffer_pool_pages_total))
  1319.                 cecho "Current InnoDB buffer pool free = "$percent_innodb_buffer_pool_free" %"
  1320.  
  1321.                 else
  1322.                 cecho "Cannot parse InnoDB stats prior to 5.0.x" red
  1323.                 $mysql -s -e "SHOW /*!50000 ENGINE */ INNODB STATUS\G"
  1324.                 fi
  1325.  
  1326.                 human_readable $innodb_buffer_pool_size innodb_buffer_pool_sizeHR
  1327.                 cecho "Current innodb_buffer_pool_size = $innodb_buffer_pool_sizeHR $unit"
  1328.                 cecho "Depending on how much space your innodb indexes take up it may be safe"  
  1329.                 cecho "to increase this value to up to 2 / 3 of total system memory"
  1330.         else
  1331.                 cecho "No InnoDB Support Enabled!" boldred
  1332.         fi
  1333. }
  1334.  
  1335. total_memory_used () {
  1336.  
  1337. ## -- Total Memory Usage -- ##
  1338.         cecho "MEMORY USAGE" boldblue
  1339.  
  1340.         mysql_variable \'read_buffer_size\' read_buffer_size
  1341.         mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size
  1342.         mysql_variable \'sort_buffer_size\' sort_buffer_size
  1343.         mysql_variable \'thread_stack\' thread_stack
  1344.         mysql_variable \'max_connections\' max_connections
  1345.         mysql_variable \'join_buffer_size\' join_buffer_size
  1346.         mysql_variable \'tmp_table_size\' tmp_table_size
  1347.         mysql_variable \'max_heap_table_size\' max_heap_table_size
  1348.         mysql_variable \'log_bin\' log_bin
  1349.         mysql_status \'Max_used_connections\' max_used_connections
  1350.  
  1351.         if [ "$major_version" = "3.23" ] ; then
  1352.                 mysql_variable \'record_buffer\' read_buffer_size
  1353.                 mysql_variable \'record_rnd_buffer\' read_rnd_buffer_size
  1354.                 mysql_variable \'sort_buffer\' sort_buffer_size
  1355.         fi
  1356.  
  1357.         if [ "$log_bin" = "ON" ] ; then
  1358.                 mysql_variable \'binlog_cache_size\' binlog_cache_size
  1359.         else
  1360.                 binlog_cache_size=0
  1361.         fi
  1362.  
  1363.         if [ $max_heap_table_size -le $tmp_table_size ] ; then
  1364.                 effective_tmp_table_size=$max_heap_table_size
  1365.         else
  1366.                 effective_tmp_table_size=$tmp_table_size
  1367.         fi
  1368.  
  1369.  
  1370.         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)
  1371.         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)
  1372.  
  1373.         mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size
  1374.         if [ -z $innodb_buffer_pool_size ] ; then
  1375.         innodb_buffer_pool_size=0
  1376.         fi
  1377.  
  1378.         mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size
  1379.         if [ -z $innodb_additional_mem_pool_size ] ; then
  1380.         innodb_additional_mem_pool_size=0
  1381.         fi
  1382.  
  1383.         mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size
  1384.         if [ -z $innodb_log_buffer_size ] ; then
  1385.         innodb_log_buffer_size=0
  1386.         fi
  1387.  
  1388.         mysql_variable \'key_buffer_size\' key_buffer_size
  1389.  
  1390.         mysql_variable \'query_cache_size\' query_cache_size
  1391.         if [ -z $query_cache_size ] ; then
  1392.         query_cache_size=0
  1393.         fi
  1394.  
  1395.         global_buffers=$(echo "$innodb_buffer_pool_size+$innodb_additional_mem_pool_size+$innodb_log_buffer_size+$key_buffer_size+$query_cache_size" | bc -l)
  1396.  
  1397.  
  1398.         max_memory=$(echo "$global_buffers+$per_thread_max_buffers" | bc -l)
  1399.         total_memory=$(echo "$global_buffers+$per_thread_buffers" | bc -l)
  1400.  
  1401.         pct_of_sys_mem=$(echo "scale=0; $total_memory*100/$physical_memory" | bc -l)
  1402.  
  1403.         if [ $pct_of_sys_mem -gt 90 ] ; then
  1404.                 txt_color=boldred
  1405.                 error=1
  1406.         else
  1407.                 txt_color=
  1408.                 error=0
  1409.         fi     
  1410.  
  1411.         human_readable $max_memory max_memoryHR
  1412.         cecho "Max Memory Ever Allocated : $max_memoryHR $unit" $txt_color
  1413.         human_readable $per_thread_buffers per_thread_buffersHR
  1414.         cecho "Configured Max Per-thread Buffers : $per_thread_buffersHR $unit" $txt_color
  1415.         human_readable $global_buffers global_buffersHR
  1416.         cecho "Configured Max Global Buffers : $global_buffersHR $unit" $txt_color
  1417.         human_readable $total_memory total_memoryHR
  1418.         cecho "Configured Max Memory Limit : $total_memoryHR $unit" $txt_color
  1419. #       human_readable $effective_tmp_table_size effective_tmp_table_sizeHR
  1420. #       cecho "Plus $effective_tmp_table_sizeHR $unit per temporary table created"
  1421.         human_readable $physical_memory physical_memoryHR
  1422.         cecho "Physical Memory : $physical_memoryHR $unit" $txt_color
  1423.         if [ $error -eq 1 ] ; then
  1424.                 printf "\n"
  1425.                 cecho "Max memory limit exceeds 90% of physical memory" $txt_color
  1426.         else
  1427.                 cecho "Max memory limit seem to be within acceptable norms" green
  1428.         fi
  1429.         unset txt_color
  1430. }
  1431.  
  1432. ## Required Functions  ##
  1433.  
  1434. login_validation () {
  1435.         check_for_socket                # determine the socket location -- 1st login
  1436.         check_for_plesk_passwords       # determine the login method -- 2nd login
  1437.         check_mysql_login               # determine if mysql is accepting login -- 3rd login
  1438.         export major_version=$($mysql -Bse "SELECT SUBSTRING_INDEX(VERSION(), '.', +2)")
  1439. #       export mysql_version_num=$($mysql -Bse "SELECT LEFT(REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', ''),4)" )
  1440.         export mysql_version_num=$($mysql -Bse "SELECT VERSION()" |
  1441.                 awk -F \. '{ printf "%02d", $1; printf "%02d", $2; printf "%02d", $3 }')
  1442.  
  1443. }
  1444.  
  1445. shared_info () {
  1446.         export major_version=$($mysql -Bse "SELECT SUBSTRING_INDEX(VERSION(), '.', +2)")
  1447.         # export mysql_version_num=$($mysql -Bse "SELECT LEFT(REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', ''),4)" )
  1448.         export mysql_version_num=$($mysql -Bse "SELECT VERSION()" |
  1449.                 awk -F \. '{ printf "%02d", $1; printf "%02d", $2; printf "%02d", $3 }')
  1450.         mysql_status \'Questions\' questions
  1451. #       socket_owner=$(find -L $socket -printf '%u\n')
  1452.         socket_owner=$(ls -nH $socket | awk '{ print $3 }')
  1453. }
  1454.        
  1455.  
  1456. get_system_info () {
  1457.  
  1458.     export OS=$(uname)
  1459.    
  1460.     # Get information for various UNIXes
  1461.     if [ "$OS" = 'Darwin' ]; then
  1462.         ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }' | head -1)
  1463.         found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
  1464.         export physical_memory=$(sysctl -n hw.memsize)
  1465.         export duflags=''
  1466.     elif [ "$OS" = 'FreeBSD' ] || [ "$OS" = 'OpenBSD' ]; then
  1467.         ## On FreeBSD must be root to locate sockets.
  1468.         ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }' | head -1)
  1469.         found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
  1470.         export physical_memory=$(sysctl -n hw.realmem)
  1471.         export duflags=''
  1472.     elif [ "$OS" = 'Linux' ] ; then
  1473.         ## Includes SWAP
  1474.         ## export physical_memory=$(free -b | grep -v buffers |  awk '{ s += $2 } END { printf("%.0f\n", s ) }')
  1475.         ps_socket=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }' | head -1)
  1476.         found_socks=$(netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }')
  1477.         export physical_memory=$(awk '/^MemTotal/ { printf("%.0f", $2*1024 ) }' < /proc/meminfo)
  1478.         export duflags='-b'
  1479.     elif [ "$OS" = 'SunOS' ] ; then
  1480.         ps_socket=$(netstat -an | awk '/mysql(.*)?.sock/ { print $5 }' | head -1)
  1481.         found_socks=$(netstat -an | awk '/mysql(.*)?.sock/ { print $5 }')
  1482.         export physical_memory=$(prtconf | awk '/^Memory\ size:/ { print $3*1048576 }')
  1483.     fi
  1484.     if [ -z $(which bc) ] ; then
  1485.         echo "Error: Command line calculator 'bc' not found!"
  1486.         exit
  1487.     fi
  1488. }
  1489.  
  1490.  
  1491. ## Optional Components Groups ##
  1492.  
  1493. banner_info () {
  1494.         shared_info
  1495.         print_banner            ; echo
  1496.         check_mysql_version     ; echo
  1497.         post_uptime_warning     ; echo
  1498. }
  1499.  
  1500. misc () {
  1501.         shared_info
  1502.         check_slow_queries      ; echo
  1503.         check_binary_log        ; echo
  1504.         check_threads           ; echo
  1505.         check_used_connections  ; echo
  1506.         check_innodb_status     ; echo
  1507. }
  1508.  
  1509. memory () {
  1510.         shared_info
  1511.         total_memory_used       ; echo
  1512.         check_key_buffer_size   ; echo
  1513.         check_query_cache       ; echo
  1514.         check_sort_operations   ; echo
  1515.         check_join_operations   ; echo
  1516. }
  1517.  
  1518. file () {
  1519.         shared_info
  1520.         check_open_files        ; echo
  1521.         check_table_cache       ; echo
  1522.         check_tmp_tables        ; echo
  1523.         check_table_scans       ; echo
  1524.         check_table_locking     ; echo
  1525. }
  1526.  
  1527. all () {
  1528.         banner_info
  1529.         misc
  1530.         memory
  1531.         file
  1532. }
  1533.  
  1534. prompt () {
  1535.         prompted='true'
  1536.         read -p "Username [anonymous] : " user
  1537.         read -rp "Password [<none>] : " pass
  1538.         cecho " "
  1539.         read -p "Socket [ /var/lib/mysql/mysql.sock ] : " socket
  1540.         if [ -z $socket ] ; then
  1541.                 export socket='/var/lib/mysql/mysql.sock'
  1542.         fi
  1543.  
  1544.         if [ -z $pass ] ; then
  1545.         export mysql="mysql -S $socket -u$user"
  1546.         export mysqladmin="mysqladmin -S $socket -u$user"
  1547.         else
  1548.         export mysql="mysql -S $socket -u$user -p$pass"
  1549.         export mysqladmin="mysqladmin -S $socket -u$user -p$pass"
  1550.         fi
  1551.  
  1552.         check_for_socket
  1553.         check_mysql_login
  1554.  
  1555.         if [ $? = 1 ] ; then
  1556.                 exit 1
  1557.         fi
  1558.         read -p "Mode to test - banner, file, misc, mem, innodb, [all] : " REPLY
  1559.         if [ -z $REPLY ] ; then
  1560.                 REPLY='all'
  1561.         fi
  1562.         case $REPLY in
  1563.                 banner | BANNER | header | HEADER | head | HEAD)
  1564.                 banner_info
  1565.                 ;;
  1566.                 misc | MISC | miscelaneous )
  1567.                 misc
  1568.                 ;;
  1569.                 mem | memory |  MEM | MEMORY )
  1570.                 memory
  1571.                 ;;
  1572.                 file | FILE | disk | DISK )
  1573.                 file
  1574.                 ;;
  1575.                 innodb | INNODB )
  1576.                 innodb
  1577.                 ;;
  1578.                 all | ALL )
  1579.                 cecho " "
  1580.                 all
  1581.                 ;;
  1582.                 * )
  1583.                 cecho "Invalid Mode!  Valid options are 'banner', 'misc', 'memory', 'file', 'innodb' or 'all'" boldred
  1584.                 exit 1
  1585.                 ;;
  1586.         esac
  1587. }
  1588.  
  1589. ## Address environmental differences ##
  1590. get_system_info
  1591. # echo $ps_socket
  1592.  
  1593. if [ -z "$1" ] ; then
  1594.         login_validation
  1595.         mode='ALL'
  1596. elif [ "$1" = "prompt" ] || [ "$1" = "PROMPT" ] ; then
  1597.         mode=$1
  1598. elif [ "$1" != "prompt" ] || [ "$1" != "PROMPT" ] ; then
  1599.         login_validation
  1600.         mode=$1
  1601. fi
  1602.  
  1603. case $mode in
  1604.         all | ALL )
  1605.         cecho " "
  1606.         all
  1607.         ;;
  1608.         mem | memory |  MEM | MEMORY )
  1609.         cecho " "
  1610.         memory
  1611.         ;;
  1612.         file | FILE | disk | DISK )
  1613.         cecho " "
  1614.         file
  1615.         ;;
  1616.         banner | BANNER | header | HEADER | head | HEAD )
  1617.         banner_info
  1618.         ;;
  1619.         misc | MISC | miscelaneous )
  1620.         cecho " "
  1621.         misc
  1622.         ;;
  1623.         innodb | INNODB )
  1624.         banner_info
  1625.         check_innodb_status ; echo
  1626.         ;;
  1627.         prompt | PROMPT )
  1628.         prompt
  1629.         ;;
  1630.         *)
  1631.         cecho "usage: $0 [ all | banner | file | innodb | memory | misc | prompt ]" boldred
  1632.         exit 1  
  1633.         ;;
  1634. esac
  1635.  

Reply to "sqltun1"

Here you can reply to the paste above