"Get statistics from mysql server"

[claquetteman]

I have change the Martin spasov script about getting statistics from mysql server, this is not great but you can graph the records from a particular table

Code: Select all

#!/usr/local/bin/php -q
<?

/* sql_stat - Gets statistics from MySQL server

takes 5 or 6 arguments

1/Host IP address
2/database user 
3/Database password
4/Database name to connect --> cacti for example or mysql
5/Table

$host = argv[1];
$user = argv[2];
$pass = argv[3];
$base = argv[4];
$table = argv[5];
$var = argv[6];

mysql_pconnect($host, $user, $pass) or die(mysql_error());
mysql_select_db($base) or die (mysql_error());

if($table = status)
{
        $query = mysql_query("SHOW STATUS") or die (mysql_error));
        while($fetch = mysql_fetch_row($query))
        {
                $stat[$fetch[0]] = $fetch[1];
        }
        printf("%s", $stat[$var]);
}
else
{
       $query = mysql_query("SELECT COUNT(*) FROM $table") or die(mysql_error());
       $fetch = mysql_fetch_row($query) or die(mysql_error());
       printf("%s", $fetch[0]);
}
mysql_close();
?>
*/

this is not finished because you have to create a host and a user on mysql server on the remote computer;

Code: Select all

INSERT INTO host (Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv) VALUES("IP address of cacti server", "cacti", "Y" , "N", "N", "N", "N", "N", "N", "N", "N", "N");

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv) VALUES("IP address of cacti server", "cactiuser", PASSWORD('cacti'), "Y" , "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N");

flush privileges;

In fact i am not sure you have to create an account user nonetheless it was for my job and i have no much time to test.

a word for paranoid cacti user :
I advice you to be careful about the password, if you use it on local it is not a problem but if you connect from a remote machine to cacti server the mysql password is not crypted. So i think you have to write it in the script :

comment /*$pass = argv[3];*/

mysql_pconnect($host, $user, your password to connect mysql) or die(mysql_error());

Now test it !

php -q mysql_stat_database 192.168.32.70 cactiuser cacti cacti snmp_query_graph

I think you should connect mysql server
connect cacti;
show tables; to see tables interesting to graph.

php -q mysql_stat_database 192.168.32.70 cactiuser cacti cacti status Questions

then you can create a data input method from cacti
Name : mysql stat
Input type : script/command
Input String : php4 /var/html/cacti/script/mysql_stat_database.php <ip> <db_user> <db_pass> <db> <table> <var>
create six input fields <ip> <db_user> <db_pass> <db> <table> <var>
and one output field : value for example.

--------------------------------------------------------------------------------------
you have to create next a data template :

Data Templates [edit: MySQL - Stat]

Name : MySQL - Stat

Data Source

Name : |host_description| - Mysql Stat

Data Input Source : Mysql Stat

Associated RRA's : all

Step 300

Data Source Active selected

Data Source Item [mysql_stat]

Internal Data Source Name : mysql_stat

Minimum Value 0

Maximum Value 100000000000

Data Source Type : GAUGE

Heartbeat

Output Field : value - output "you have created from data input method"


Custom Data [data input: MySQL - Stat]

database selected

database_pass selected

database_table selected

database_user selected

ip_address selected

variable_name selected

--------------------------------------------------------------------------------------
Next create a data source :

Data Template Selection [edit: Quark - Mysql Stat]

Selected Data Template : The name given to this data template MySQL - Stat

Host : ip addresse of mysql server

-------------------------------------------------------------------------------------
Data Source

Custom Data [data input: MySQL - Stat]

for example :
database cacti
database_pass cacti
database_table status
database_user cactiuser
ip_address 192.168.32.70 #mysql_server#
variable_name Questions

or

for example :
database cacti
database_pass cacti
database_table snmp_query_graph
database_user cactiuser
ip_address 192.168.32.70 #mysql_server#
variable_name : / ---> you can write anything but you can not leave this field blank

You have to wait a moment for cacti to generate the rrd file ie the next cmd.php from crontab.

--------------------------------------------------------------------------------------
Next a graph template it is not difficult, you have to add an mysql item and a graph item input.

then from graph management add a graph select the graph template mysql - stat and the mysql server host and create
select the graph template and save.
add the graph from graph tree.

ok i thinks that is all !

claq