MySQL Host Template -- Updated on 2006-08-10

[philovivero]

i clicked on the big link at the top saying CREATE GRAPHS NOW - took me to another page, i highlighted all of them (like 15or 20 of them) clicked the CREATE button bottom right - and nothing, i never got prompted to enter a user/pass combination for anything just went back to the same page from memory

That does sound like a bug in your version of Cacti. Either the template was loaded corrupted, or some other feature got borked.

A known-good version (for me) is 0.8.6g.

[tribune]

yeah im running v0.8.6h via debian package

will try reinstall the mysql module and see how it goes

*edit* well i tried the reinstall of everything including cacti
and now i get the following when i click the 'create graphs'

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Warning: Cannot modify header information - headers already sent by (output started at /usr/share/cacti/site/lib/template.php:732) in /usr/share/cacti/site/graphs_new.php on line 322

[philovivero]

Notice: Undefined index: local_data_id in /usr/share/cacti/site/lib/template.php on line 732

Definitely a Cacti bug. You shouldn't get that.

[tribune]

ok well what i did , was after testing few different things, decided to build cacti up from source

sure enough, finally im presented with the 15 or so user/pass boxes *yay*

now it has other problems with the device being down as its reporting but manually i can connect, so at least its progress which is fantastic =) heh

[tribune]

works really nice!

i installed to 2 pcs and both worked fine

something u can add to the readme:

to make it work you have to remove the snmp community string or it will not work!!!

when i had snmp community string ("public") the host was detected as down and nothing worked.
when i removed it everything worked asap


great work

yup just had to do the same, and now it seems the device is recovering and doing its thing, finally yay

[jp24]

Hi,

Can anyone help me out, I have followed the README steps, but I still haven't got any data appearing on my graphs. I have had a read through the old posts but can not find a solution, any tips?

btw I have turned on graph debug mode and RRDTOOL says OK

Cheers

John

[gleam]

To those who aren't getting prompted for a username/password after creating the graphs: edit the xml file and change

Code: Select all

<allow_nulls>on</allow_nulls>

to

Code: Select all

<allow_nulls>off</allow_nulls>

This works on the 0.8.6h-3 cacti package that debian currently provides.

[jasonb]

Maybe I am just reading over it in the README.txt but the Load Average & Locking and slow are not graphing. I imported the xml files for the template. I did the whole...

GRANT PROCESS ON * TO cactiuser@'IPaddress' IDENTIFIED by 'password';

*Yes, I also did change cactiuser/IPAddress/password to the correct info*

and that worked just fine. My poller runs every 5 mintues and I also ran the convertXmlTo5minutePollingInterval.sh.

I added the Graph Template for Load Average & Locking and Slow and the username and password appeared. I put the username and password that I used in the Grant statement. My question is do I need to edit a file to put the Username/Password/IP address of MySQL for the data to be gathered? Also will I need to add anything to my cron entries for this to run or does it run off the poller.php?

thanks for the help..

-jb

[philovivero]

To those who aren't getting prompted for a username/password after creating the graphs: edit the xml file and change

Code: Select all

<allow_nulls>on</allow_nulls>

to

Code: Select all

<allow_nulls>off</allow_nulls>

This works on the 0.8.6h-3 cacti package that debian currently provides.

Is this something I should fix at the source, or do I need to upgrade to Cacti 0.8.6h-3 to make the template export do this properly?

Also, I've change most graph types back to linear graphs and re-exported the templates and made a new release, here:

http://faemalia.net/mysqlUtils

These are the graphs as we're currently using them at Digg. I'll ask again, if anyone has any useful information about which stats should logically be grouped with which other stats, and/or hints on how to keep the graph scale correct when one stat starts to diverge from the rest, let me know. I'm open to suggestions.

[philovivero]

It seems the username/password support in Cacti is causing others woes. Believe me, it caused me woes, too.

Another work-around is to not enter any username/password at all, make no changes to the XML files (as Gleam suggests), but instead to modify the PHP stats gathering script like this:

Code: Select all

if ($_SERVER["argc"] == 3 || $_SERVER["argc"] == 5 || ($_SERVER["argv"][1] == "status" && $_SERVER["argc"] == 6)) {

    $host     = $_SERVER["argv"][2];
    $username = $_SERVER["argv"][3];
    $password = $_SERVER["argv"][4];

    $username = "cacti";
    $password = "yourcactipassword";

What this'll do is let a 3-argument call succeed, then it'll just hardcode username/password to be cacti/yourcactipassword. This is actually what I'm doing at Digg now because I started using a browser !Firefox, and it wouldn't let me enter the username/password so easily anymore.

It'd be super-cool if some PHP guru could suggest how to elegently make the script take "--username=XXX --password=XXX --host=XXX" sort of calling convention. If I ever have to digg into this too much, I'll just rewrite it in Perl. :)

Hmm. The more I ponder this, the more I think I should just make this the default script behaviour. Anyone have opinions on this matter?

[giovanni]

This "package" is a great piece of work... great graphs and ease installation!

My construtive "opinion"

I monitor remote servers, its not so good open MySQL ports for each, even for my own IP, i think that this kinds of information should be retrieved just using SNMP... less bandwidth and overhead for all.

But your work will help-me too, i will use this script to retrieve at least some information using snmp.

Cheers

[giovanni]

Hi again everyone,

I made a shell script to retrieve some information over SNMP, i will later add some templates to that.

On snmpd.conf:

Code: Select all

pass .1.3.6.1.4.1.2021.256 /usr/local/bin/mysql_stats.sh .1.3.6.1.4.1.2021.256

On /usr/local/bin/mysql_stats.sh

Code: Select all

#!/bin/bash
# Retrieve any MySQL information and passes
# to net-snmp.
# Useful when you are monitoring remote servers and dont
# want to open MySQL ports.
# You need to Add to snmpd.conf
## pass .1.3.6.1.4.1.2021.256 YOUR_PATH/bin/mysql_stats.sh .1.3.6.1.4.1.2021.256
# You can test running:
## snmpwalk -On -c public -v1 SNMP_SERVER .1.3.6.1.4.1.2021.256
# Needs MySQL >= 5
# Contact: <giovanniATredix.com.br>

BASE_OID="$1"
TYPE="$2"
REQUEST_OID="$3"

MYSQL_PATH="/usr/bin/mysql"
MYSQL_USER="cacti?"
MYSQL_PASS="cacti?"
MYSQL_CONN_TYPE="socket"
# Query MySQL database
query() {
        if [ "${MYSQL_CONN_TYPE}" == "socket" ]; then
                echo "$1" | ${MYSQL_PATH} -s --user=${MYSQL_USER} \
                        --password=${MYSQL_PASS} | awk '{ print $2 }'
        else
                echo "$1" | ${MYSQL_PATH} -s -h ${MYSQL_HOST} --user=${MYSQL_USER} \
                        --password=${MYSQL_PASS} | awk '{ print $2 }'
        fi
}

# Grab all the information on SHOW GLOBAL STATUS
# Using just 1 conection to MySQL
THE_GREAT_SELECT="
show global status where
Variable_name = 'Bytes_received' or
Variable_name = 'Bytes_sent' or
Variable_name = 'Innodb_data_read' or
Variable_name = 'Innodb_data_written' or
Variable_name = 'Innodb_data_reads' or
Variable_name = 'Innodb_data_writes' or
Variable_name = 'threads_connected' or
Variable_name = 'open_files' or
Variable_name = 'open_tables' or
Variable_name = 'com_commit' or
Variable_name = 'com_insert' or
Variable_name = 'com_update' or
Variable_name = 'com_select' or
Variable_name = 'Com_change_db' or
Variable_name = 'Questions';
"

i=0
for RESULT in $(query "${THE_GREAT_SELECT}"); do
        MYSQL_RESULT[$i]="${RESULT}"
        i=$[ $i + 1 ]
done

# You can add new selects and commands
##MYSQL_RESULT[ $i + 1 ]="$(query "any select you want")

if   [ "${TYPE}" == "-n" ]; then
        if   [ "${BASE_OID}" == "${REQUEST_OID}" ]; then
                echo -e "${BASE_OID}.0\ninteger\n${MYSQL_RESULT[0]}"
        else
                for (( i=0; i < ${#MYSQL_RESULT[@]}; ++i )); do
                        if [ "${REQUEST_OID}" == "${BASE_OID}.$i" ] && [ "${MYSQL_RESULT[ $i + 1 ]}" != "" ]; then
                                echo -e "${BASE_OID}.$[ $i + 1 ]\nINTEGER\n${MYSQL_RESULT[$i + 1]}"
                        fi
                done
        fi
elif [ "${TYPE}" == "-g" ]; then
        if   [ "${BASE_OID}" == "${REQUEST_OID}" ] || [ "${REQUEST_OID}" == "${BASE_OID}.0"  ]; then
                echo -e "${BASE_OID}.0\nINTEGER\n${MYSQL_RESULT[0]}"
        else
                for (( i=1; i < ${#MYSQL_RESULT[@]}; ++i )); do
                        if [ "${REQUEST_OID}" == "${BASE_OID}.$i" ]; then
                                echo -e "${BASE_OID}.$i\nINTEGER\n${MYSQL_RESULT[$i]}"
                        fi
                done
        fi
fi

[rpingar]

I installed latest version of the script.

I use mem ndbcluster, so I am going to get only memchaced graph.

The probles is that I don't get any graph constructed. So executing from CLI the dump...... i get the follwing error:
PHP Fatal error: Cannot instantiate non-existent class: memcache in /var/www/html/cacti-0.8.6h/scripts/dumpMemcachedStats.php on line 8

How to fix it?
regards
Rosario

[koniczynek]

Everything works fine, but I have a question of a different matter
Is there a way do graph myqsl CPU usage, like one for apache web server? e.g that mysql processes use 35% of CPU time.

[philovivero]

I've put another update on the page:

http://faemalia.net/mysqlUtils

I don't honestly remember what I've changed, It's been a few months. Just minor incremental colour or linesize changes, regroupings, added some CDEFs so that the non-logarithmic graphs actually look right.