Advice and answers from the Hostrare TeamKnowledge base

Troubleshoot - by Hostrare

Update On: 23 Jul 2021Stats and Monitoring Process tool

Mysqlmymonlite Stats and Monitoring method free tool, Bash shell text tool used to assume stats for Apache, Nginx, Litespeed web server, and MySQL servers. Helpful for troubleshooting & diagnostics and monitoring of server stats.
mysqlmymonlite.sh is a MySQL optimization tool – a lite variant of more unrestricted promoted mysqlmymon.sh – a MySQL and policy monitoring script written by George Liu (eva2000) for quick stats check for apache, Nginx, Litespeed web server, and Mysql stats for the next operating methods:
CentOS WHM/Cpanel
CentOS
Debian 6.x systems
mysqlmymon.sh is the big brother book to mysqlmymonlite.sh which is a much-added feature general and used for my individual paid consults – it offers a lot more specific info for stats report and server & MySQL optimization but a lot of the info is not satisfied to share in public. So I bared down mysqlmymon.sh to build mysqlmymonlite.sh with aim of giving as much stats output as possible without revealing any private server sensitive data.
Download zip file

root@server[~]#mkdir /root/toolsroot@server[~]#cd  /root/toolsroot@server [~/tools]#wget http://mysqlmymon.com/download/mysqlmymonlite.ziproot@server [~/tools]#unzip mysqlmymonlite.ziproot@server [~/tools]#cp centos_whm/*  /root/root@server [~/tools]#chmod +x /root/mysqlmymonlite.sh

Edit mysqlmymonlite.sh variables. There’s 2 methods available.

1. Use and upload to your server the config.ini. This voluntary separate config.ini, you can use to determine all variables required to set up mysqlmymonlite.sh. This begins upgrading mysqlmymonlite.sh easier (if there are no config.ini changes), as you can just overwrite old mysqlmymonlite.sh with a new variant without becoming to re-setup your settings as they would be in config.ini. Place config.ini in the same register that mysqlmymonlite.sh is placed.
2. 
   2. the Second way is to not upload config.ini (delete it from the record in which mysqlmymonlite.sh is), and only edit the mysqlmymonlite.sh settings within the folder

    MYCNF='/etc/my.cnf'    USER='mysqlrootuser'    PASS='yourmysqlrootpassword'

change USER to your mysql root user name and PASS to mysql root user’s password

optional change

MASKDB=’y’

to

MASKDB=’n’

to unhide original database names from the production, if you post on public forums you can leave at MASKDB=’y’
unforced change
By default PER TABLE DATABASE points is disabled as of v0.3.1 to decrease total text output. You can re-enable per table output by setting

SHOWPERTABLE=’n’

to

SHOWPERTABLE=’y’

Save and exit configuraiton file.

Execute script and get result on your txt file.

  cd /root
    ./mysqlmymonlite.sh run 2>/dev/null > mysqlmymonlite_stats.txt

or

    cd /root
    bash mysqlmymonlite.sh run 2>/dev/null > mysqlmymonlite_stats.txt

or to specify a different directory for txt file output

    cd /root
    ./mysqlmymonlite.sh run 2>/dev/null > /home/username/mysqlmymonstats/mysqlmymonlite_stats.txt

or

    cd /root
    bash mysqlmymonlite.sh run 2>/dev/null > /home/username/mysqlmymonstats/mysqlmymonlite_stats.txt

You’ll end up with a text file at /root/mysqlmymonlite_stats.txt if you changed to /root register above or if you defined a different register for saving to it would be at /home/username/mysqlmymonstats/mysqlmymonlite_stats.txt

Thants All…

Examples

root@server [~/tools]# ./mysqlmymonlite.sh
please see help for all options available
./mysqlmymonlite.sh –help
./mysqlmymonlite.sh check
./mysqlmymonlite.sh run
./mysqlmymonlite.sh mysql
./mysqlmymonlite.sh vmstat
./mysqlmymonlite.sh showcreate
./mysqlmymonlite.sh showindex
./mysqlmymonlite.sh vbshowtables
./mysqlmymonlite.sh dblist
./mysqlmymonlite.sh mysqlreport
./mysqlmymonlite.sh mysqlfullreport
./mysqlmymonlite.sh mysqltuner
./mysqlmymonlite.sh psmem
./mysqlmymonlite.sh pschema

Optional usage methods including

a). cron job email posting of stats or
b). saving a timestamped text file of stats each hour etc
You can also run mysqlmymonlite.sh as a cron job emailing you stats each hour or whatever period you set the cron job to. You may want to set up an email filter to correctly label and make emailed stats for your server.
Example usage where the email address is your email address you want to set:
for using the mail command send stats each hour:

0 * * * * /root/mysqlmymonlite.sh run 2>/dev/null | mail -s "`hostname` Monitoring Stats: `date`" emailaddressfor using mutt send stats every hour:0 * * * * /root/mysqlmymonlite.sh run 2>/dev/null | mutt -s "`hostname` Monitoring Stats: `date`" emailaddressOr save to timestamped text file every hour to directory at /home/username/mysqlmymonstats0 * * * * /root/mysqlmymonlite.sh run 2>/dev/null > /home/username/mysqlmymonstats/stats_mysqlmymonlite_`date +"\%d\%m\%y-\%H\%M\%S"`.txt

Of course /home/username/mysqlmymonstats directory needs to exist before hand.

—————————————————-

EXCLUDEDB option
This option reduces the amount of output text by excluding database names from the PER TABLE output for each database name. Usually for vB optimisation requests, I prefer all database tables for entire server to be viewable but for servers with many non-vB database names, it can be a lengthy list so you can reduce that output via EXCLUDEDB option in mysqlmymonlite.sh script.

For WHM/Cpanel mysqlmymonlite.sh extra database names have been added to the EXCLUDEDB list
1. cphulkd
2. eximstats
3. horde
4. leechprotect
5. Roundcube
To add extra databases to be excluded, edit your EXCLUDEDB choice and add the extra database names separated by a single space. Not sure what all your database names are? Don’t worry mysqlmymonlite.sh can simply output the entire list by the dblist option
run in ssh2 telnet the script with dblist option
./mysqlmymonlite.sh dblist
you’ll get a list of single space-separated database names located on your server to add to the EXCLUDEDB list. Just remove your vBulletin database names from the EXCLUDEDB list, so I can see PER TABLE production for vBulletin database names.
There’s only one downside, is that if another non-vBulletin database meals are InnoDB storage engine based, I wouldn’t be able to see which tables are if that non-vBulletin database is added to the EXCLUDEDB list.

######################################################## Excludb databases list from per Table listing# You can hide all other database names from having# their database table names listed leaving just# your vBulletin databasenames to the per# Table listing which is needed for optimisation# tuning to see which tables are innodb or myisam# to see their index and data sizes per table######################################################## to get a full list of database names type:# ./mysqlmymonlite.sh dblist# single spacing between each databasename#######################################################EXCLUDEDB="test information_schema mysql"

Custom command option

Added in mysqlmymonlite.sh v0.3.8, allows you to add your personal custom SSH bash shell companies to be outputted when you run custom option:
./mysqlmymonlite.sh custom
This option allows you to build a separate involves file name it incopt.inc and place it in the same straight mysqlmymonlite.sh is located in. You can add your personal custom SSH bash shell companies to this incopt.inc file to an additional extend mysqlmymonlite.sh’s functionality and usefulness.
example #1 if you want to add iostats output to mysqlmymonlite.sh
———
step 1.
create incopt.inc file in the same record as mysqlmymonlite.sh
———
step 2.
———
edit incopt.inc and join the iostats company, you may require to know a bit about bash shell scripting if you want to format the output correctly. In this example, I continued to incopt.inc 3 lines of code to first new line space with clear echo, the echo the company line I want to run (title), and then the exact command you would usually run in SSH telnet.

iostat -x 10 5
Linux 2.6.32-220.7.1.el6.x86_64  05/08/2012      _x86_64_        (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
0.14    0.00    0.14    0.03    0.00   99.69

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.08     1.98    0.21    0.37     8.09    18.42    46.01     0.01   12.47   1.67   0.10
scd0              0.00     0.00    0.00    0.00     0.00     0.00     8.00     0.00    0.43   0.43   0.00
dm-0              0.00     0.00    0.28    2.31     8.03    18.42    10.22     0.47  181.06   0.37   0.10
dm-1              0.00     0.00    0.00    0.00     0.02     0.00     8.00     0.00    2.73   1.66   0.00