Solved

help with troubleshooting a Perl script for mySql

Posted on 2010-09-06
8
1,119 Views
Last Modified: 2012-05-10
Hi,

I tried running the script below on my Linux RHES 5.4 using
"mysql" Unix login id & we're sure we've entered the correct
admin id & password but it kept giving :
  "Attempted to use login credentials, but they were invalid"

Can someone have a look at the mysqltuner.pl script below to
see if the codes need some fixing.  On my server, I don't have
the .psa.shadow file

===========================================================

#!/usr/bin/perl -w
# mysqltuner.pl - Version 1.1.0
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2009 Major Hayden - major@mhtx.net
#
# For the latest updates, please visit http://mysqltuner.com/
# Subversion repository available at http://tools.assembla.com/svn/mysqltuner/
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# This project would not be possible without help from:
#   Matthew Montgomery     Paul Kehrer
#   Dave Burgess           Jonathan Hinds
#   Mike Jackson           Nils Breunese
#   Shawn Ashlee           Luuk Vosslamber
#   Ville Skytta           Trent Hornibrook
#   Jason Gill             Mark Imbriaco
#   Greg Eden              Aubin Galinotti
#   Giovanni Bechis        Bill Bradford
#   Ryan Novosielski       Michael Scheidell
#   Blair Christensen      Hans du Plooy
#   Victor Trac            Everett Barnes
#   Tom Krouper            Gary Barrueto
#   Simon Greenaway        Adam Stein
#
# Inspired by Matthew Montgomery's tuning-primer.sh script:
# http://forge.mysql.com/projects/view.php?id=44
#
use strict;
use warnings;
use diagnostics;
use Getopt::Long;

# Set up a few variables for use in the script
my $tunerversion = "1.0.1";
my (@adjvars, @generalrec);

# Set defaults
my %opt = (
            "nobad" => 0,
            "nogood" => 0,
            "noinfo" => 0,
            "nocolor" => 0,
            "forcemem" => 0,
            "forceswap" => 0,
            "host" => 0,
            "socket" => 0,
            "port" => 0,
            "user" => 0,
            "pass" => 0,
            "skipsize" => 0,
            "checkversion" => 0,
      );
      
# Gather the options from the command line
GetOptions(\%opt,
            'nobad',
            'nogood',
            'noinfo',
            'nocolor',
            'forcemem=i',
            'forceswap=i',
            'host=s',
            'socket=s',
            'port=i',
            'user=s',
            'pass=s',
            'skipsize',
            'checkversion',
            'help',
      );

if (defined $opt{'help'} && $opt{'help'} == 1) { usage(); }

sub usage {
      # Shown with --help option passed
      print "\n".
            "   MySQLTuner $tunerversion - MySQL High Performance Tuning Script\n".
            "   Bug reports, feature requests, and downloads at http://mysqltuner.com/\n".
            "   Maintained by Major Hayden (major\@mhtx.net) - Licensed under GPL\n".
            "\n".
            "   Important Usage Guidelines:\n".
            "      To run the script with the default options, run the script without arguments\n".
            "      Allow MySQL server to run for at least 24-48 hours before trusting suggestions\n".
            "      Some routines may require root level privileges (script will provide warnings)\n".
            "      You must provide the remote server's total memory when connecting to other servers\n".
            "\n".
            "   Connection and Authentication\n".
            "      --host <hostname>    Connect to a remote host to perform tests (default: localhost)\n".
            "      --socket <socket>    Use a different socket for a local connection\n".
            "      --port <port>        Port to use for connection (default: 3306)\n".
            "      --user <username>    Username to use for authentication\n".
            "      --pass <password>    Password to use for authentication\n".
            "\n".
            "   Performance and Reporting Options\n".
            "      --skipsize           Don't enumerate tables and their types/sizes (default: on)\n".
            "                             (Recommended for servers with many tables)\n".
            "      --checkversion       Check for updates to MySQLTuner (default: don't check)\n".
            "      --forcemem <size>    Amount of RAM installed in megabytes\n".
            "      --forceswap <size>   Amount of swap memory configured in megabytes\n".
            "\n".
            "   Output Options:\n".
            "      --nogood             Remove OK responses\n".
            "      --nobad              Remove negative/suggestion responses\n".
            "      --noinfo             Remove informational responses\n".
            "      --nocolor            Don't print output in color\n".
            "\n";
      exit;
}

# Setting up the colors for the print styles
my $good = ($opt{nocolor} == 0)? "[\e[0;32mOK\e[0m]" : "[OK]" ;
my $bad = ($opt{nocolor} == 0)? "[\e[0;31m!!\e[0m]" : "[!!]" ;
my $info = ($opt{nocolor} == 0)? "[\e[0;34m--\e[0m]" : "[--]" ;

# Functions that handle the print styles
sub goodprint { print $good." ".$_[0] unless ($opt{nogood} == 1); }
sub infoprint { print $info." ".$_[0] unless ($opt{noinfo} == 1); }
sub badprint { print $bad." ".$_[0] unless ($opt{nobad} == 1); }
sub redwrap { return ($opt{nocolor} == 0)? "\e[0;31m".$_[0]."\e[0m" : $_[0] ; }
sub greenwrap { return ($opt{nocolor} == 0)? "\e[0;32m".$_[0]."\e[0m" : $_[0] ; }

# Calculates the parameter passed in bytes, and then rounds it to one decimal place
sub hr_bytes {
      my $num = shift;
      if ($num >= (1024**3)) { #GB
            return sprintf("%.1f",($num/(1024**3)))."G";
      } elsif ($num >= (1024**2)) { #MB
            return sprintf("%.1f",($num/(1024**2)))."M";
      } elsif ($num >= 1024) { #KB
            return sprintf("%.1f",($num/1024))."K";
      } else {
            return $num."B";
      }
}

# Calculates the parameter passed in bytes, and then rounds it to the nearest integer
sub hr_bytes_rnd {
      my $num = shift;
      if ($num >= (1024**3)) { #GB
            return int(($num/(1024**3)))."G";
      } elsif ($num >= (1024**2)) { #MB
            return int(($num/(1024**2)))."M";
      } elsif ($num >= 1024) { #KB
            return int(($num/1024))."K";
      } else {
            return $num."B";
      }
}

# Calculates the parameter passed to the nearest power of 1000, then rounds it to the nearest integer
sub hr_num {
      my $num = shift;
      if ($num >= (1000**3)) { # Billions
            return int(($num/(1000**3)))."B";
      } elsif ($num >= (1000**2)) { # Millions
            return int(($num/(1000**2)))."M";
      } elsif ($num >= 1000) { # Thousands
            return int(($num/1000))."K";
      } else {
            return $num;
      }
}

# Calculates uptime to display in a more attractive form
sub pretty_uptime {
      my $uptime = shift;
      my $seconds = $uptime % 60;
      my $minutes = int(($uptime % 3600) / 60);
      my $hours = int(($uptime % 86400) / (3600));
      my $days = int($uptime / (86400));
      my $uptimestring;
      if ($days > 0) {
            $uptimestring = "${days}d ${hours}h ${minutes}m ${seconds}s";
      } elsif ($hours > 0) {
            $uptimestring = "${hours}h ${minutes}m ${seconds}s";
      } elsif ($minutes > 0) {
            $uptimestring = "${minutes}m ${seconds}s";
      } else {
            $uptimestring = "${seconds}s";
      }
      return $uptimestring;
}

# Retrieves the memory installed on this machine
my ($physical_memory,$swap_memory,$duflags);
sub os_setup {
      sub memerror {
            badprint "Unable to determine total memory/swap; use '--forcemem' and '--forceswap'\n";
            exit;
      }
      my $os = `uname`;
      $duflags = ($os =~ /Linux/) ? '-b' : '';
      if ($opt{'forcemem'} > 0) {
            $physical_memory = $opt{'forcemem'} * 1048576;
            infoprint "Assuming $opt{'forcemem'} MB of physical memory\n";
            if ($opt{'forceswap'} > 0) {
                  $swap_memory = $opt{'forceswap'} * 1048576;
                  infoprint "Assuming $opt{'forceswap'} MB of swap space\n";
            } else {
                  $swap_memory = 0;
                  badprint "Assuming 0 MB of swap space (use --forceswap to specify)\n";
            }
      } else {
            if ($os =~ /Linux/) {
                  $physical_memory = `free -b | grep Mem | awk '{print \$2}'` or memerror;
                  $swap_memory = `free -b | grep Swap | awk '{print \$2}'` or memerror;
            } elsif ($os =~ /Darwin/) {
                  $physical_memory = `sysctl -n hw.memsize` or memerror;
                  $swap_memory = `sysctl -n vm.swapusage | awk '{print \$3}' | sed 's/\..*\$//'` or memerror;
            } elsif ($os =~ /NetBSD|OpenBSD/) {
                  $physical_memory = `sysctl -n hw.physmem` or memerror;
                  if ($physical_memory < 0) {
                        $physical_memory = `sysctl -n hw.physmem64` or memerror;
                  }
                  $swap_memory = `swapctl -l | grep '^/' | awk '{ s+= \$2 } END { print s }'` or memerror;
            } elsif ($os =~ /BSD/) {
                  $physical_memory = `sysctl -n hw.realmem`;
                  $swap_memory = `swapinfo | grep '^/' | awk '{ s+= \$2 } END { print s }'`;
            } elsif ($os =~ /SunOS/) {
                  $physical_memory = `/usr/sbin/prtconf | grep Memory | cut -f 3 -d ' '` or memerror;
                  chomp($physical_memory);
                  $physical_memory = $physical_memory*1024*1024;
            }
      }
      chomp($physical_memory);
}

# Checks to see if a MySQL login is possible
my ($mysqllogin,$doremote,$remotestring);
sub mysql_setup {
      $doremote = 0;
      $remotestring = '';
      my $command = `which mysqladmin`;
      chomp($command);
      if (! -e $command) {
            badprint "Unable to find mysqladmin in your \$PATH.  Is MySQL installed?\n";
            exit;
      }
      # Are we being asked to connect via a socket?
      if ($opt{socket} ne 0) {
            $remotestring = " -S $opt{socket}";
      }
      # Are we being asked to connect to a remote server?
      if ($opt{host} ne 0) {
            chomp($opt{host});
            $opt{port} = ($opt{port} eq 0)? 3306 : $opt{port} ;
            # If we're doing a remote connection, but forcemem wasn't specified, we need to exit
            if ($opt{'forcemem'} eq 0) {
                  badprint "The --forcemem option is required for remote connections\n";
                  exit;
            }
            infoprint "Performing tests on $opt{host}:$opt{port}\n";
            $remotestring = " -h $opt{host} -P $opt{port}";
            $doremote = 1;
      }
      # Did we already get a username and password passed on the command line?
      if ($opt{user} ne 0 and $opt{pass} ne 0) {
            $mysqllogin = "-u $opt{user} -p'$opt{pass}'".$remotestring;
            my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
            if ($loginstatus =~ /mysqld is alive/) {
                  goodprint "Logged in using credentials passed on the command line\n";
                  return 1;
            } else {
                  badprint "Attempted to use login credentials, but they were invalid\n";
                  exit 0;
            }
      }
      if ( -r "/etc/psa/.psa.shadow" and $doremote == 0 ) {
            # It's a Plesk box, use the available credentials
            $mysqllogin = "-u admin -p`cat /etc/psa/.psa.shadow`";
            my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
            unless ($loginstatus =~ /mysqld is alive/) {
                  badprint "Attempted to use login credentials from Plesk, but they failed.\n";
                  exit 0;
            }
      } else {
            # It's not Plesk, we should try a login
            my $loginstatus = `mysqladmin $remotestring ping 2>&1`;
            if ($loginstatus =~ /mysqld is alive/) {
                  # Login went just fine
                  $mysqllogin = " $remotestring ";
                  # Did this go well because of a .my.cnf file or is there no password set?
                  my $userpath = `printenv HOME`;
                  if (length($userpath) > 0) {
                        chomp($userpath);
                  }
                  unless ( -e "${userpath}/.my.cnf" ) {
                        badprint "Successfully authenticated with no password - SECURITY RISK!\n";
                  }
                  return 1;
            } else {
                  print STDERR "Please enter your MySQL administrative login: ";
                  my $name = <>;
                  print STDERR "Please enter your MySQL administrative password: ";
                  system("stty -echo");
                  my $password = <>;
                  system("stty echo");
                  chomp($password);
                  chomp($name);
                  $mysqllogin = "-u $name";
                  if (length($password) > 0) {
                        $mysqllogin .= " -p'$password'";
                  }
                  $mysqllogin .= $remotestring;
                  my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
                  if ($loginstatus =~ /mysqld is alive/) {
                        print STDERR "\n";
                        if (! length($password)) {
                              # Did this go well because of a .my.cnf file or is there no password set?
                              my $userpath = `ls -d ~`;
                              chomp($userpath);
                              unless ( -e "$userpath/.my.cnf" ) {
                                    badprint "Successfully authenticated with no password - SECURITY RISK!\n";
                              }
                        }
                        return 1;
                  } else {
                        print "\n".$bad." Attempted to use login credentials, but they were invalid.\n";
                        exit 0;
                  }
                  exit 0;
            }
      }
}

# Populates all of the variable and status hashes
my (%mystat,%myvar,$dummyselect);
sub get_all_vars {
      # We need to initiate at least one query so that our data is useable
      $dummyselect = `mysql $mysqllogin -Bse "SELECT VERSION();"`;
      my @mysqlvarlist = `mysql $mysqllogin -Bse "SHOW /*!50000 GLOBAL */ VARIABLES;"`;
      foreach my $line (@mysqlvarlist) {
            $line =~ /([a-zA-Z_]*)\s*(.*)/;
            $myvar{$1} = $2;
      }
      my @mysqlstatlist = `mysql $mysqllogin -Bse "SHOW /*!50000 GLOBAL */ STATUS;"`;
      foreach my $line (@mysqlstatlist) {
            $line =~ /([a-zA-Z_]*)\s*(.*)/;
            $mystat{$1} = $2;
      }
}

# Checks for updates to MySQLTuner
sub validate_tuner_version {
      print "\n-------- General Statistics --------------------------------------------------\n";
      if ($opt{checkversion} eq 0) {
            infoprint "Skipped version check for MySQLTuner script\n";
            return;
      }
      my $update;
      my $url = "http://mysqltuner.com/versioncheck.php?v=$tunerversion";
      if (-e "/usr/bin/curl") {
            $update = `/usr/bin/curl --connect-timeout 5 '$url' 2>/dev/null`;
            chomp($update);
      } elsif (-e "/usr/bin/wget") {
            $update = `/usr/bin/wget -e timestamping=off -T 5 -O - '$url' 2>/dev/null`;
            chomp($update);
      }
      if ($update eq 1) {
            badprint "There is a new version of MySQLTuner available\n";
      } elsif ($update eq 0) {
            goodprint "You have the latest version of MySQLTuner\n";
      } else {
            infoprint "Unable to check for the latest MySQLTuner version\n";
      }
}

# Checks for supported or EOL'ed MySQL versions
my ($mysqlvermajor,$mysqlverminor);
sub validate_mysql_version {
      ($mysqlvermajor,$mysqlverminor) = $myvar{'version'} =~ /(\d)\.(\d)/;
      if ($mysqlvermajor < 5) {
            badprint "Your MySQL version ".$myvar{'version'}." is EOL software!  Upgrade soon!\n";
      } elsif ($mysqlvermajor == 5) {
            goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
      } else {
            badprint "Currently running unsupported MySQL version ".$myvar{'version'}."\n";
      }
}

# Checks for 32-bit boxes with more than 2GB of RAM
my ($arch);
sub check_architecture {
      if ($doremote eq 1) { return; }
      if (`uname` =~ /SunOS/ && `isainfo -b` =~ /64/) {
            $arch = 64;
            goodprint "Operating on 64-bit architecture\n";
      } elsif (`uname` !~ /SunOS/ && `uname -m` =~ /64/) {
            $arch = 64;
            goodprint "Operating on 64-bit architecture\n";
      } else {
            $arch = 32;
            if ($physical_memory > 2147483648) {
                  badprint "Switch to 64-bit OS - MySQL cannot currently use all of your RAM\n";
            } else {
                  goodprint "Operating on 32-bit architecture with less than 2GB RAM\n";
            }
      }
}

# Start up a ton of storage engine counts/statistics
my (%enginestats,%enginecount,$fragtables);
sub check_storage_engines {
      if ($opt{skipsize} eq 1) {
            print "\n-------- Storage Engine Statistics -------------------------------------------\n";
            infoprint "Skipped due to --skipsize option\n";
            return;
      }
      print "\n-------- Storage Engine Statistics -------------------------------------------\n";
      infoprint "Status: ";
      my $engines;
      $engines .= (defined $myvar{'have_archive'} && $myvar{'have_archive'} eq "YES")? greenwrap "+Archive " : redwrap "-Archive " ;
      $engines .= (defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES")? greenwrap "+BDB " : redwrap "-BDB " ;
      $engines .= (defined $myvar{'have_federated'} && $myvar{'have_federated'} eq "YES")? greenwrap "+Federated " : redwrap "-Federated " ;
      $engines .= (defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES")? greenwrap "+InnoDB " : redwrap "-InnoDB " ;
      $engines .= (defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES")? greenwrap "+ISAM " : redwrap "-ISAM " ;
      $engines .= (defined $myvar{'have_ndbcluster'} && $myvar{'have_ndbcluster'} eq "YES")? greenwrap "+NDBCluster " : redwrap "-NDBCluster " ;      
      print "$engines\n";
      if ($mysqlvermajor >= 5) {
            # MySQL 5 servers can have table sizes calculated quickly from information schema
            my @templist = `mysql $mysqllogin -Bse "SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;"`;
            foreach my $line (@templist) {
                  my ($engine,$size,$count);
                  ($engine,$size,$count) = $line =~ /([a-zA-Z_]*)\s+(\d+)\s+(\d+)/;
                  if (!defined($size)) { next; }
                  $enginestats{$engine} = $size;
                  $enginecount{$engine} = $count;
            }
            $fragtables = `mysql $mysqllogin -Bse "SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';"`;
            chomp($fragtables);
      } else {
            # MySQL < 5 servers take a lot of work to get table sizes
            my @tblist;
            # Now we build a database list, and loop through it to get storage engine stats for tables
            my @dblist = `mysql $mysqllogin -Bse "SHOW DATABASES"`;
            foreach my $db (@dblist) {
                  chomp($db);
                  if ($db eq "information_schema") { next; }
                  if ($mysqlvermajor == 3 || ($mysqlvermajor == 4 && $mysqlverminor == 0)) {
                        # MySQL 3.23/4.0 keeps Data_Length in the 6th column
                        push (@tblist,`mysql $mysqllogin -Bse "SHOW TABLE STATUS FROM \\\`$db\\\`" | awk '{print \$2,\$6,\$9}'`);
                  } else {
                        # MySQL 4.1+ keeps Data_Length in the 7th column
                        push (@tblist,`mysql $mysqllogin -Bse "SHOW TABLE STATUS FROM \\\`$db\\\`" | awk '{print \$2,\$7,\$10}'`);
                  }
            }
            # Parse through the table list to generate storage engine counts/statistics
            $fragtables = 0;
            foreach my $line (@tblist) {
                  chomp($line);
                  $line =~ /([a-zA-Z_]*)\s+(\d+)\s+(\d+)/;
                  my $engine = $1;
                  my $size = $2;
                  my $datafree = $3;
                  if ($size !~ /^\d+$/) { $size = 0; }
                  if (defined $enginestats{$engine}) {
                        $enginestats{$engine} += $size;
                        $enginecount{$engine} += 1;
                  } else {
                        $enginestats{$engine} = $size;
                        $enginecount{$engine} = 1;
                  }
                  if ($datafree > 0) {
                        $fragtables++;
                  }
            }
      }
      while (my ($engine,$size) = each(%enginestats)) {
            infoprint "Data in $engine tables: ".hr_bytes_rnd($size)." (Tables: ".$enginecount{$engine}.")"."\n";
      }
      # If the storage engine isn't being used, recommend it to be disabled
      if (!defined $enginestats{'InnoDB'} && defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES") {
            badprint "InnoDB is enabled but isn't being used\n";
            push(@generalrec,"Add skip-innodb to MySQL configuration to disable InnoDB");
      }
      if (!defined $enginestats{'BerkeleyDB'} && defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES") {
            badprint "BDB is enabled but isn't being used\n";
            push(@generalrec,"Add skip-bdb to MySQL configuration to disable BDB");
      }
      if (!defined $enginestats{'ISAM'} && defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES") {
            badprint "ISAM is enabled but isn't being used\n";
            push(@generalrec,"Add skip-isam to MySQL configuration to disable ISAM (MySQL > 4.1.0)");
      }
      # Fragmented tables
      if ($fragtables > 0) {
            badprint "Total fragmented tables: $fragtables\n";
            push(@generalrec,"Run OPTIMIZE TABLE to defragment tables for better performance");
      } else {
            goodprint "Total fragmented tables: $fragtables\n";
      }
}

my %mycalc;
sub calculations {
      if ($mystat{'Questions'} < 1) {
            badprint "Your server has not answered any queries - cannot continue...";
            exit 0;
      }
      # Per-thread memory
      if ($mysqlvermajor > 3) {
            $mycalc{'per_thread_buffers'} = $myvar{'read_buffer_size'} + $myvar{'read_rnd_buffer_size'} + $myvar{'sort_buffer_size'} + $myvar{'thread_stack'} + $myvar{'join_buffer_size'};
      } else {
            $mycalc{'per_thread_buffers'} = $myvar{'record_buffer'} + $myvar{'record_rnd_buffer'} + $myvar{'sort_buffer'} + $myvar{'thread_stack'} + $myvar{'join_buffer_size'};
      }
      $mycalc{'total_per_thread_buffers'} = $mycalc{'per_thread_buffers'} * $myvar{'max_connections'};
      $mycalc{'max_total_per_thread_buffers'} = $mycalc{'per_thread_buffers'} * $mystat{'Max_used_connections'};

      # Server-wide memory
      $mycalc{'max_tmp_table_size'} = ($myvar{'tmp_table_size'} > $myvar{'max_heap_table_size'}) ? $myvar{'max_heap_table_size'} : $myvar{'tmp_table_size'} ;
      $mycalc{'server_buffers'} = $myvar{'key_buffer_size'} + $mycalc{'max_tmp_table_size'};
      $mycalc{'server_buffers'} += (defined $myvar{'innodb_buffer_pool_size'}) ? $myvar{'innodb_buffer_pool_size'} : 0 ;
      $mycalc{'server_buffers'} += (defined $myvar{'innodb_additional_mem_pool_size'}) ? $myvar{'innodb_additional_mem_pool_size'} : 0 ;
      $mycalc{'server_buffers'} += (defined $myvar{'innodb_log_buffer_size'}) ? $myvar{'innodb_log_buffer_size'} : 0 ;
      $mycalc{'server_buffers'} += (defined $myvar{'query_cache_size'}) ? $myvar{'query_cache_size'} : 0 ;

      # Global memory
      $mycalc{'max_used_memory'} = $mycalc{'server_buffers'} + $mycalc{"max_total_per_thread_buffers"};
      $mycalc{'total_possible_used_memory'} = $mycalc{'server_buffers'} + $mycalc{'total_per_thread_buffers'};
      $mycalc{'pct_physical_memory'} = int(($mycalc{'total_possible_used_memory'} * 100) / $physical_memory);

      # Slow queries
      $mycalc{'pct_slow_queries'} = int(($mystat{'Slow_queries'}/$mystat{'Questions'}) * 100);
      
      # Connections
      $mycalc{'pct_connections_used'} = int(($mystat{'Max_used_connections'}/$myvar{'max_connections'}) * 100);
      $mycalc{'pct_connections_used'} = ($mycalc{'pct_connections_used'} > 100) ? 100 : $mycalc{'pct_connections_used'} ;
      
      # Key buffers
      if ($mysqlvermajor > 3 && !($mysqlvermajor == 4 && $mysqlverminor == 0)) {
            $mycalc{'pct_key_buffer_used'} = sprintf("%.1f",(1 - (($mystat{'Key_blocks_unused'} * $myvar{'key_cache_block_size'}) / $myvar{'key_buffer_size'})) * 100);
      }
      if ($mystat{'Key_read_requests'} > 0) {
            $mycalc{'pct_keys_from_mem'} = sprintf("%.1f",(100 - (($mystat{'Key_reads'} / $mystat{'Key_read_requests'}) * 100)));
      } else {
          $mycalc{'pct_keys_from_mem'} = 0;
      }
      if ($doremote eq 0 and $mysqlvermajor < 5) {
            $mycalc{'total_myisam_indexes'} = `find $myvar{'datadir'} -name '*.MYI' 2>&1 | xargs du -L $duflags '{}' 2>&1 | awk '{ s += \$1 } END { printf (\"%d\",s) }'`;
      } elsif ($mysqlvermajor >= 5) {
            $mycalc{'total_myisam_indexes'} = `mysql $mysqllogin -Bse "SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';"`;
      }
      if (defined $mycalc{'total_myisam_indexes'} and $mycalc{'total_myisam_indexes'} =~ /^0\n$/) {
            $mycalc{'total_myisam_indexes'} = "fail";
      } elsif (defined $mycalc{'total_myisam_indexes'}) {
            chomp($mycalc{'total_myisam_indexes'});
      }
      
      # Query cache
      if ($mysqlvermajor > 3) {
            $mycalc{'query_cache_efficiency'} = sprintf("%.1f",($mystat{'Qcache_hits'} / ($mystat{'Com_select'} + $mystat{'Qcache_hits'})) * 100);
            if ($myvar{'query_cache_size'}) {
                  $mycalc{'pct_query_cache_used'} = sprintf("%.1f",100 - ($mystat{'Qcache_free_memory'} / $myvar{'query_cache_size'}) * 100);
            }
      if ($mystat{'Qcache_lowmem_prunes'} == 0) {
                  $mycalc{'query_cache_prunes_per_day'} = 0;
            } else {
                  $mycalc{'query_cache_prunes_per_day'} = int($mystat{'Qcache_lowmem_prunes'} / ($mystat{'Uptime'}/86400));
            }
      }
      
      # Sorting
      $mycalc{'total_sorts'} = $mystat{'Sort_scan'} + $mystat{'Sort_range'};
      if ($mycalc{'total_sorts'} > 0) {
            $mycalc{'pct_temp_sort_table'} = int(($mystat{'Sort_merge_passes'} / $mycalc{'total_sorts'}) * 100);
      }
      
      # Joins
      $mycalc{'joins_without_indexes'} = $mystat{'Select_range_check'} + $mystat{'Select_full_join'};
      $mycalc{'joins_without_indexes_per_day'} = int($mycalc{'joins_without_indexes'} / ($mystat{'Uptime'}/86400));
      
      # Temporary tables
      if ($mystat{'Created_tmp_tables'} > 0) {
            if ($mystat{'Created_tmp_disk_tables'} > 0) {
                  $mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / ($mystat{'Created_tmp_tables'} + $mystat{'Created_tmp_disk_tables'})) * 100);
            } else {
                  $mycalc{'pct_temp_disk'} = 0;
            }
      }
      
      # Table cache
      if ($mystat{'Opened_tables'} > 0) {
            $mycalc{'table_cache_hit_rate'} = int($mystat{'Open_tables'}*100/$mystat{'Opened_tables'});
      } else {
            $mycalc{'table_cache_hit_rate'} = 100;
      }
      
      # Open files
      if ($myvar{'open_files_limit'} > 0) {
            $mycalc{'pct_files_open'} = int($mystat{'Open_files'}*100/$myvar{'open_files_limit'});
      }
      
      # Table locks
      if ($mystat{'Table_locks_immediate'} > 0) {
            if ($mystat{'Table_locks_waited'} == 0) {
                  $mycalc{'pct_table_locks_immediate'} = 100;
            } else {
                  $mycalc{'pct_table_locks_immediate'} = int($mystat{'Table_locks_immediate'}*100/($mystat{'Table_locks_waited'} + $mystat{'Table_locks_immediate'}));
            }
      }
      
      # Thread cache
      $mycalc{'thread_cache_hit_rate'} = int(100 - (($mystat{'Threads_created'} / $mystat{'Connections'}) * 100));

      # Other
      if ($mystat{'Connections'} > 0) {
            $mycalc{'pct_aborted_connections'} = int(($mystat{'Aborted_connects'}/$mystat{'Connections'}) * 100);
      }
      if ($mystat{'Questions'} > 0) {
            $mycalc{'total_reads'} = $mystat{'Com_select'};
            $mycalc{'total_writes'} = $mystat{'Com_delete'} + $mystat{'Com_insert'} + $mystat{'Com_update'} + $mystat{'Com_replace'};
            if ($mycalc{'total_reads'} == 0) {
                  $mycalc{'pct_reads'} = 0;
                  $mycalc{'pct_writes'} = 100;
            } else {
                  $mycalc{'pct_reads'} = int(($mycalc{'total_reads'}/($mycalc{'total_reads'}+$mycalc{'total_writes'})) * 100);
                  $mycalc{'pct_writes'} = 100-$mycalc{'pct_reads'};
            }
      }

      # InnoDB
      if ($myvar{'have_innodb'} eq "YES") {
            $mycalc{'innodb_log_size_pct'} = ($myvar{'innodb_log_file_size'} * 100 / $myvar{'innodb_buffer_pool_size'});
      }
}

sub mysql_stats {
      print "\n-------- Performance Metrics -------------------------------------------------\n";
      # Show uptime, queries per second, connections, traffic stats
      my $qps;
      if ($mystat{'Uptime'} > 0) { $qps = sprintf("%.3f",$mystat{'Questions'}/$mystat{'Uptime'}); }
      if ($mystat{'Uptime'} < 86400) { push(@generalrec,"MySQL started within last 24 hours - recommendations may be inaccurate"); }
      infoprint "Up for: ".pretty_uptime($mystat{'Uptime'})." (".hr_num($mystat{'Questions'}).
            " q [".hr_num($qps)." qps], ".hr_num($mystat{'Connections'})." conn,".
            " TX: ".hr_num($mystat{'Bytes_sent'}).", RX: ".hr_num($mystat{'Bytes_received'}).")\n";
      infoprint "Reads / Writes: ".$mycalc{'pct_reads'}."% / ".$mycalc{'pct_writes'}."%\n";

      # Memory usage
      infoprint "Total buffers: ".hr_bytes($mycalc{'server_buffers'})." global + ".hr_bytes($mycalc{'per_thread_buffers'})." per thread ($myvar{'max_connections'} max threads)\n";
      if ($mycalc{'total_possible_used_memory'} > 2*1024*1024*1024 && $arch eq 32) {
            badprint "Allocating > 2GB RAM on 32-bit systems can cause system instability\n";
            badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
      } elsif ($mycalc{'pct_physical_memory'} > 85) {
            badprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
            push(@generalrec,"Reduce your overall MySQL memory footprint for system stability");
      } else {
            goodprint "Maximum possible memory usage: ".hr_bytes($mycalc{'total_possible_used_memory'})." ($mycalc{'pct_physical_memory'}% of installed RAM)\n";
      }
      
      # Slow queries
      if ($mycalc{'pct_slow_queries'} > 5) {
            badprint "Slow queries: $mycalc{'pct_slow_queries'}% (".hr_num($mystat{'Slow_queries'})."/".hr_num($mystat{'Questions'}).")\n";
      } else {
            goodprint "Slow queries: $mycalc{'pct_slow_queries'}% (".hr_num($mystat{'Slow_queries'})."/".hr_num($mystat{'Questions'}).")\n";
      }
      if ($myvar{'long_query_time'} > 10) { push(@adjvars,"long_query_time (<= 10)"); }
      if (defined($myvar{'log_slow_queries'})) {
            if ($myvar{'log_slow_queries'} eq "OFF") { push(@generalrec,"Enable the slow query log to troubleshoot bad queries"); }
      }
      
      # Connections
      if ($mycalc{'pct_connections_used'} > 85) {
            badprint "Highest connection usage: $mycalc{'pct_connections_used'}%  ($mystat{'Max_used_connections'}/$myvar{'max_connections'})\n";
            push(@adjvars,"max_connections (> ".$myvar{'max_connections'}.")");
            push(@adjvars,"wait_timeout (< ".$myvar{'wait_timeout'}.")","interactive_timeout (< ".$myvar{'interactive_timeout'}.")");
            push(@generalrec,"Reduce or eliminate persistent connections to reduce connection usage")
      } else {
            goodprint "Highest usage of available connections: $mycalc{'pct_connections_used'}% ($mystat{'Max_used_connections'}/$myvar{'max_connections'})\n";
      }
      
      # Key buffer
      if (!defined($mycalc{'total_myisam_indexes'}) and $doremote == 1) {
            push(@generalrec,"Unable to calculate MyISAM indexes on remote MySQL server < 5.0.0");
      } elsif ($mycalc{'total_myisam_indexes'} =~ /^fail$/) {
            badprint "Cannot calculate MyISAM index size - re-run script as root user\n";
      } elsif ($mycalc{'total_myisam_indexes'} == "0") {
            badprint "None of your MyISAM tables are indexed - add indexes immediately\n";
      } else {
            if ($myvar{'key_buffer_size'} < $mycalc{'total_myisam_indexes'} && $mycalc{'pct_keys_from_mem'} < 95) {
                  badprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
                  push(@adjvars,"key_buffer_size (> ".hr_bytes($mycalc{'total_myisam_indexes'}).")");
            } else {
                  goodprint "Key buffer size / total MyISAM indexes: ".hr_bytes($myvar{'key_buffer_size'})."/".hr_bytes($mycalc{'total_myisam_indexes'})."\n";
            }
            if ($mystat{'Key_read_requests'} > 0) {
                  if ($mycalc{'pct_keys_from_mem'} < 95) {
                        badprint "Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)\n";
                  } else {
                        goodprint "Key buffer hit rate: $mycalc{'pct_keys_from_mem'}% (".hr_num($mystat{'Key_read_requests'})." cached / ".hr_num($mystat{'Key_reads'})." reads)\n";
                  }
            } else {
                  # No queries have run that would use keys
            }
      }
      
      # Query cache
      if ($mysqlvermajor < 4) {
            # MySQL versions < 4.01 don't support query caching
            push(@generalrec,"Upgrade MySQL to version 4+ to utilize query caching");
      } elsif ($myvar{'query_cache_size'} < 1) {
            badprint "Query cache is disabled\n";
            push(@adjvars,"query_cache_size (>= 8M)");
      } elsif ($mystat{'Com_select'} == 0) {
            badprint "Query cache cannot be analyzed - no SELECT statements executed\n";
      } else {
            if ($mycalc{'query_cache_efficiency'} < 20) {
                  badprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}% (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)\n";
                  push(@adjvars,"query_cache_limit (> ".hr_bytes_rnd($myvar{'query_cache_limit'}).", or use smaller result sets)");
            } else {
                  goodprint "Query cache efficiency: $mycalc{'query_cache_efficiency'}% (".hr_num($mystat{'Qcache_hits'})." cached / ".hr_num($mystat{'Qcache_hits'}+$mystat{'Com_select'})." selects)\n";
            }
            if ($mycalc{'query_cache_prunes_per_day'} > 98) {
                  badprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
                  if ($myvar{'query_cache_size'} > 128*1024*1024) {
                      push(@generalrec,"Increasing the query_cache size over 128M may reduce performance");
                    push(@adjvars,"query_cache_size (> ".hr_bytes_rnd($myvar{'query_cache_size'}).") [see warning above]");
                  } else {
                    push(@adjvars,"query_cache_size (> ".hr_bytes_rnd($myvar{'query_cache_size'}).")");
                  }
            } else {
                  goodprint "Query cache prunes per day: $mycalc{'query_cache_prunes_per_day'}\n";
            }
      }
      
      # Sorting
      if ($mycalc{'total_sorts'} == 0) {
            # For the sake of space, we will be quiet here
            # No sorts have run yet
      } elsif ($mycalc{'pct_temp_sort_table'} > 10) {
            badprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)\n";
            push(@adjvars,"sort_buffer_size (> ".hr_bytes_rnd($myvar{'sort_buffer_size'}).")");
            push(@adjvars,"read_rnd_buffer_size (> ".hr_bytes_rnd($myvar{'read_rnd_buffer_size'}).")");
      } else {
            goodprint "Sorts requiring temporary tables: $mycalc{'pct_temp_sort_table'}% (".hr_num($mystat{'Sort_merge_passes'})." temp sorts / ".hr_num($mycalc{'total_sorts'})." sorts)\n";
      }
      
      # Joins
      if ($mycalc{'joins_without_indexes_per_day'} > 250) {
            badprint "Joins performed without indexes: $mycalc{'joins_without_indexes'}\n";
            push(@adjvars,"join_buffer_size (> ".hr_bytes($myvar{'join_buffer_size'}).", or always use indexes with joins)");
            push(@generalrec,"Adjust your join queries to always utilize indexes");
      } else {
            # For the sake of space, we will be quiet here
            # No joins have run without indexes
      }
      
      # Temporary tables
      if ($mystat{'Created_tmp_tables'} > 0) {
            if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
                  badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)\n";
                  push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
                  push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
                  push(@generalrec,"When making adjustments, make tmp_table_size/max_heap_table_size equal");
                  push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
            } elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256) {
                  badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)\n";
                  push(@generalrec,"Temporary table size is already large - reduce result set size");
                  push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
            } else {
                  goodprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_disk_tables'} + $mystat{'Created_tmp_tables'})." total)\n";
            }
      } else {
            # For the sake of space, we will be quiet here
            # No temporary tables have been created
      }

      # Thread cache
      if ($myvar{'thread_cache_size'} eq 0) {
            badprint "Thread cache is disabled\n";
            push(@generalrec,"Set thread_cache_size to 4 as a starting value");
            push(@adjvars,"thread_cache_size (start at 4)");
      } else {
            if ($mycalc{'thread_cache_hit_rate'} <= 50) {
                  badprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)\n";
                  push(@adjvars,"thread_cache_size (> $myvar{'thread_cache_size'})");
            } else {
                  goodprint "Thread cache hit rate: $mycalc{'thread_cache_hit_rate'}% (".hr_num($mystat{'Threads_created'})." created / ".hr_num($mystat{'Connections'})." connections)\n";
            }
      }

      # Table cache
      if ($mystat{'Open_tables'} > 0) {
            if ($mycalc{'table_cache_hit_rate'} < 20) {
                  badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";
                  if ($mysqlvermajor eq 6 || ($mysqlvermajor eq 5 && $mysqlverminor ge 1)) {
                        push(@adjvars,"table_cache (> ".$myvar{'table_open_cache'}.")");
                  } else {
                        push(@adjvars,"table_cache (> ".$myvar{'table_cache'}.")");
                  }
                  push(@generalrec,"Increase table_cache gradually to avoid file descriptor limits");
            } else {
                  goodprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";
            }
      }

      # Open files
      if (defined $mycalc{'pct_files_open'}) {
            if ($mycalc{'pct_files_open'} > 85) {
                  badprint "Open file limit used: $mycalc{'pct_files_open'}% (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")\n";
                  push(@adjvars,"open_files_limit (> ".$myvar{'open_files_limit'}.")");
            } else {
                  goodprint "Open file limit used: $mycalc{'pct_files_open'}% (".hr_num($mystat{'Open_files'})."/".hr_num($myvar{'open_files_limit'}).")\n";
            }
      }

      # Table locks
      if (defined $mycalc{'pct_table_locks_immediate'}) {
            if ($mycalc{'pct_table_locks_immediate'} < 95) {
                  badprint "Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}%\n";
                  push(@generalrec,"Optimize queries and/or use InnoDB to reduce lock wait");
            } else {
                  goodprint "Table locks acquired immediately: $mycalc{'pct_table_locks_immediate'}% (".hr_num($mystat{'Table_locks_immediate'})." immediate / ".hr_num($mystat{'Table_locks_waited'}+$mystat{'Table_locks_immediate'})." locks)\n";
            }
      }

      # Performance options
      if ($mysqlvermajor == 3 || ($mysqlvermajor == 4 && $mysqlverminor == 0)) {
            push(@generalrec,"Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts");
      } elsif ($myvar{'concurrent_insert'} eq "OFF") {
            push(@generalrec,"Enable concurrent_insert by setting it to 'ON'");
      } elsif ($myvar{'concurrent_insert'} eq 0) {
            push(@generalrec,"Enable concurrent_insert by setting it to 1");
      }
      if ($mycalc{'pct_aborted_connections'} > 5) {
            badprint "Connections aborted: ".$mycalc{'pct_aborted_connections'}."%\n";
            push(@generalrec,"Your applications are not closing MySQL connections properly");
      }
      
      # InnoDB
      if (defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES" && defined $enginestats{'InnoDB'}) {
            if ($myvar{'innodb_buffer_pool_size'} > $enginestats{'InnoDB'}) {
                  goodprint "InnoDB data size / buffer pool: ".hr_bytes($enginestats{'InnoDB'})."/".hr_bytes($myvar{'innodb_buffer_pool_size'})."\n";
            } else {
                  badprint "InnoDB data size / buffer pool: ".hr_bytes($enginestats{'InnoDB'})."/".hr_bytes($myvar{'innodb_buffer_pool_size'})."\n";
                  push(@adjvars,"innodb_buffer_pool_size (>= ".hr_bytes_rnd($enginestats{'InnoDB'}).")");
            }
      }
}

# Take the two recommendation arrays and display them at the end of the output
sub make_recommendations {
      print "\n-------- Recommendations -----------------------------------------------------\n";
      if (@generalrec > 0) {
            print "General recommendations:\n";
            foreach (@generalrec) { print "    ".$_."\n"; }
      }
      if (@adjvars > 0) {
            print "Variables to adjust:\n";
            if ($mycalc{'pct_physical_memory'} > 90) {
                  print "  *** MySQL's maximum memory usage is dangerously high ***\n".
                          "  *** Add RAM before increasing MySQL buffer variables ***\n";
            }
            foreach (@adjvars) { print "    ".$_."\n"; }
      }
      if (@generalrec == 0 && @adjvars ==0) {
            print "No additional performance recommendations are available.\n"
      }
      print "\n";
}

# ---------------------------------------------------------------------------
# BEGIN 'MAIN'
# ---------------------------------------------------------------------------
print      "\n >>  MySQLTuner $tunerversion - Major Hayden <major\@mhtx.net>\n".
            " >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/\n".
            " >>  Run with '--help' for additional options and output filtering\n";
mysql_setup;                              # Gotta login first
os_setup;                                    # Set up some OS variables
get_all_vars;                              # Toss variables/status into hashes
validate_tuner_version;                  # Check current MySQLTuner version
validate_mysql_version;                  # Check current MySQL version
check_architecture;                        # Suggest 64-bit upgrade
check_storage_engines;                  # Show enabled storage engines
calculations;                              # Calculate everything we need
mysql_stats;                              # Print the server stats
make_recommendations;                  # Make recommendations based on stats
# ---------------------------------------------------------------------------
# END 'MAIN'
# ---------------------------------------------------------------------------


0
Comment
Question by:sunhux
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:MadShiva
Comment Utility
Dear sunhux,

The .psa.shadow come from Plesk, a commercial software package.

Could you check the log file of mysql and provide some part when you tried to using your username & password ?


Best Regards
0
 

Author Comment

by:sunhux
Comment Utility

Hi Shiva

May I know the mySql log filename & its location.  Apologies, I'm totally new to mySql
0
 
LVL 10

Assisted Solution

by:MadShiva
MadShiva earned 80 total points
Comment Utility
Dear Sunhux,

The log files should be located at /var/log/mysql/

And the filename mysqld.log.

You could also look other filename. If the file is updated when you tried to login then it's the good file.

Best Regards
0
 
LVL 5

Accepted Solution

by:
fredmc earned 330 total points
Comment Utility
There are two places in the script that can give you the error message you quote. One is where it is attempting to log into MySQL with the user name and password provided on the command line when you started the program, and the other is if you didn't include them on the commmand line and it is asking you to enter them. Either way, it is clear that, as sure as you are that you are using the correct login info, you are not.

However, there are two parameters that could also cause the login to fail, besides the user name and password. Those are the host name of the server where MySQL is running, and the port that is used to connect. If MySQL is running on the same server, then the host name and port should be left blank. If it is a remote server, you need to enter the "host" and, if that server uses a port number other than the default of 3306, you need to enter the "port" number.

If you can ssh to the server, the easiest way to test your login is to enter this on the command line, if it is the same server on which MySQL is running:
mysql -u USERNAME -pPASSWORD

Do not put a space between -p and the password.

If it is a remote server, enter this:
mysql -u USERNAME -pPASSWORD -h HOST -P PORT

The host could be a domain name or an IP address. Again, if the port is the default of 3306, you do not need to enter it - though it won't hurt.

If you can successfully login with this method, then you are giving it the correct credentials, and there is some other problem in the script. But, I think you will find you are not able to log in. In that case, what to do next depends on whether MySQL is running on the same server, or a remote server. So, after checking your login, please provided that information, along with the error message MySQL gives you.

If it is a remote server, you need to be sure that port 3306 (or whatever is used) is open on that server. It could be IPTABLES or some other firewall is blocking access.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:sunhux
Comment Utility

This is what I gather from the two mySql pids in the Linux RHES 5.4 box that are being monitored
by CA monitoring tool.  Does the two outputs below help in any way.  Do the outputs below mean
I have 2 instances/databases or one of them is an admin instance while the other is the actual
service instance?  Pardon if my questions do not make any sense as I'm no DBA.
    the logfiles are: ss_project_mysqld.log  &  vvvv_mysqld.log


# cat xxxxxdbN.pid
11253
# ps -ef | grep 11253        
mysql    11253 11069  3 May26 ?        3-06:29:31 /usr/sbin/mysqld --defaults-fi
le=/etc/ss_project_mysqld.cnf --basedir=/ --datadir=/data/mysql/ss_project/data
--user=mysql --log-error=/var/log/ss_project_mysqld.log --pid-file=/data/mysql/s
s_project/data/xxxxxdbN.pid --socket=/data/mysql/ss_project/mysql.sock --port=80
 
For sure the 2 files that follows after  "-log-error= "  in the 2 outputs below do not
contain anything that we wanted as the latest entries in the logfiles are dated months ago:
   ie  /var/log/vvvv_mysqld.log  &  
 


# cat vvvv_mysqld.pid
18082
# ps -ef |grep 18082
mysql    18082 17895  0 May05 ?        00:44:42 /usr/sbin/mysqld --defaults-file
=/etc/vvvv_mysqld.cnf --basedir=/ --datadir=/data/mysql/vvvv/data --user=mysql -
-log-error=/var/log/vvvv_mysqld.log --pid-file=/var/run/mysqld/vvvv_mysqld.pid -
-socket=/data/mysql/vvvv/mysql.sock --port=8005
 
 

0
 

Author Comment

by:sunhux
Comment Utility

> If you can ssh to the server
Yes, I could ssh to the server but did not get a chance to do it today.
So Fred, I'll just try 2 different ports here, one is 80 & the other is 8005, right?
     mysql -u USERNAME -pPASSWORD -P PORT

& the two logfiles I last mentioned above have date stamp of a couple of months back so the
logs have not been updated for a while though Unix "fuser" indicates the logs are being
locked by the processes
0
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 90 total points
Comment Utility
Your script generates the "Attempted to use login credentials, but they were invalid"
message in more than one place, so it makes it harder to know what the issue is.

For starters, please modify these so that we know which one failed:
   "Attempted to use login credentials, but they were invalid 1"
   "Attempted to use login credentials, but they were invalid 2"

If you don't have the  .psa.shadow file as you describe, it must be prompting you for
the password for the "admin" MySQL id. This needs to be a special account set up by
your DBA on your server with a particular password. It also needs to have "host"
set up appropriately to allow access from a particular client. The most flexible is for
host to be set to '%' meaning allow access from any client or localhost.
0
 
LVL 5

Assisted Solution

by:fredmc
fredmc earned 330 total points
Comment Utility
sunhux,
Yes, it does look like you have two instances of MySQL running - while not normal, there are situations that require it. In my humble opinion, it is very strange that one instance uses the same port at that used for httpd. But, it is what it is, sometimes! So, try it with ports 80 and 8005 - unless you are connecting on/from the same server MySQL is running on.

When running your script you will need to use the correct port number for the instance of MySQL you want to deal with. If you can get into MySQL with "mysql" on the command line, get into the "mysql" database:
> use mysql;
then do
> select user,host from user;
to find out all the user namess that are configured, and what hosts they are allowed to connect from. If you are connecting remotely with your script, you will need to be sure that the host you are connecting from is included in the list. But, I'm guessing you are connecting from the same server MySQL is running on. So, be sure that "localhost" is in the list as a host for the user you are using.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now