The HOWTO below will guide you through creating statistics graphs for MySQL usage in Cacti.
We have modified the authentication to use the standard mtop user which was already on our system.
I saved this script as /usr/local/bin/cacti-mysqlstats.php and set it executable with:
chmod a+rx /usr/local/bin/cacti-mysqlstats.php
Here is a local cached copy of the script from: http://www.cacti.net/additional_scripts.php [Gets statistics from MySQL server]#!/usr/bin/php -q
<?
/* sql_stat – Gets statistics from MySQL server
Takes one argument (Variable name) and returns it’s value.
Good replacement for the sql.php script.
some interesting variables:
Questions – queries executed since last restart.
Threads_running – running queries now
Threads_connected – connected clients
.
.
For more info RTFM or mail me:
Martin Spasov (mspasov@techno-link.com)
This script is distributed under GPL.
*/
$var = $argv[1];
mysql_connect(“localhost”, “mysqltop”) or die (“0″);
$res = mysql_query(“SHOW STATUS”) or die(“0″);
while ($a = mysql_fetch_row($res)) {
$stat[$a[0]] = $a[1];
}
printf(“%s”, $stat[$var]);
mysql_close();
?>
Data Input Method
To graph the data we need an input method
- Click – Data Input Methods (left hand menu)
To graph Queries
- Click – Add (top right hand corner)
- Should now show “Data Input Methods [new]“
- Set: Name -> Mysql – “Queries”
- Set: Input Type -> Query/Command
- Set: Input String -> “/usr/local/bin/cacti-mysqlstats.php Queries”
- Click – Create
- Click Output Fields -> Add
- Set: Field [Output] -> “queries”
- Set: Friendly Name -> “Queries”
- Click – Create
- Click – Save
Data Templates
- Click – Data Templates
To graph Queries
- Click – Add (top right hand corner)
- Set: Data Templates [new] -> “Mysql – Queries”
- Tick: Data Source “Use Per-Data Source Value (Ignore this Value)”
- Set: Data Input Method -> Mysql – Queries