Monitoring MySQL Replication

[cbidwell]

Hi all,

I'm trying to monitor mysql replication on four of my servers but I keep winding up with "No Data"

This is my setup:

UserParameter=mysql.slave_running,mysql -u username -ppassword -e "show slave status" -E | grep Slave_SQL_Running | cut -d: -f2 | sed "s/ \s//g" | sed "s/Yes/1/" | sed "s/No/0/"

When I run zabbix_agentd -t mysql.slave_running I get a [t|1]:

However in my item configuration, I input:
{Template_App_MySQL:mysql.slave_running.last(0)}=1

Should I use this instead:
{Template_App_MySQL:mysql[slave_running].last(0)}=1

This is my Item::Configuration:

Type: Zabbix agent(active)
Key: mysql.slave_running
Type of information: Numeric (integer 64 bit)
Units: N/A
Use Multiplier: Do Not User
Update Interval: 60
no flexible intervals.

Any help is greatly appreciated. I know I'm doing something obviously stupid here and am just overlooking it.

Thanks,
Chris

[OneLoveAmaru]

Did you get this working?/ I'm curious about this as well.

[cbidwell]

Nope, and haven't gotten any replies from anyone here. I would hope that a noob such as myself would get the community support needed to make this successful, but to no avail.

Let me know if you have any success as well.

The only thing I can ask you is that you've verified that you can talk to the server on tcp/10051 and that you have #DisableActive=1 STILL commented out in your zabbix_agentd.conf on your client.

I've done these and still I get 'No data'

[rolandsym]

Double check...

Just a thought. Do you have proper name set in zabbix_agentd.conf
Hostname = yourserver
and this name is the same on the Zabbix server host record
These are required to be the same for active checks to work.
If you change it remember to restart the agent.

Otherwise, I got your piece to work on Ubuntu 7.04.

Rolandsym

[rolandsym]

hostname

The hostname in zabbix client and zabbix server records has no correlation to a fqdn. It's whatever you want it to be. However, for active agent checks that name has to be "exact" so if your entry in zabbix_agentd.conf looks like

hostname = bob.local.man


Zabbix server

Name = bob.local.man

Rolandsym

[azilber]

Here's how I do it...

zabbix_agentd.conf:

UserParameter=mysql.slave,/home/zabbix/slave-check.pl

slave-check.pl

#!/usr/bin/perl

my $returnstr="";

open(INPIPE,"/usr/bin/mysqladmin --host=127.0.0.1 --port=3306 extended-status|/bin/grep -i 'Slave_running'|/bin/cut -f3 -d'|'|") or die "AAAahhhhh!";
        chomp($returnstr=<INPIPE>);
        $returnstr=~ s/ *//g;
close INPIPE;

#print "\'$returnstr\'";
if($returnstr eq "ON"){
        print('1');
        exit;
}

print('0');
exit;

This works for me for zabbix 1.3.

-Alex

[stritec]

MySQL Replication Monitoring (Seconds Behind Master)

Here is my solution for monitoring replication (seconds behind master)

I have a php file called mysql_monitor_seconds_behind_master.php which contains:

<?
$seconds_behind_master = exec("mysql -u username -ppassword -Bse \"show slave status\\G\" | grep Seconds_Behind_Master | awk '{ print $2 }'");
if($seconds_behind_master == "NULL"){
   echo "-1\n";
}
else
{
   echo "$seconds_behind_master\n";
}
?>

I then call it from zabbix conf file like so:

UserParameter=mysql.seconds_behind_master, php /opt/monitor/mysql_monitor_seconds_behind_master.php

[marcelein]

UserParameter=mysql.srt4,mysqladmin --host=localhost -P 4004 -u user -pfoobar extended-status|/bin/grep -i 'Slave_retried_transactions' | cut -f3 -d'|'
UserParameter=mysql.sott4,mysqladmin --host=localhost  -P 4004 -u user -pfoobar extended-status|/bin/grep -i 'Slave_open_temp_tables' | cut -f3 -d'|'
UserParameter=mysql.sr4,mysqladmin --host=localhost  -P4004 -u user -pfoobar extended-status|/bin/grep -i 'Slave_running' | cut -f3 -d'|' | sed "s/OFF/0/; s/ON/1/"

this works well for me.

[stritec]

Excellent solution.


The use of 'sed' saves from having to call an external script just to see if it was on or off.


I had to call the external script because zabbix couldn't figure out what to do with a 'null' response and therefore it didn't match the datatype that that is was using for seconds behind the mater.

I can probably use sed to do the same thing and if it's null set it to 0 in a single line instead of an if check in an external script.

Thanks!
Curtis

[marcelein]

so you need a simple
pipe with
sed "s/OFF/0/; s/ON/1/; s/NULL/-1/"
i implemented that for me to. but there was no NULL before.

[stritec]

Maybe there is a way to configure mysql to not return NULL but I always get a null response when replication is stopped or not running.

[bek99]

Here is my solution for monitoring replication (seconds behind master)

I have a php file called mysql_monitor_seconds_behind_master.php which contains:

<?
$seconds_behind_master = exec("mysql -u username -ppassword -Bse \"show slave status\\G\" | grep Seconds_Behind_Master | awk '{ print $2 }'");
if($seconds_behind_master == "NULL"){
   echo "-1\n";
}
else
{
   echo "$seconds_behind_master\n";
}
?>

I then call it from zabbix conf file like so:

UserParameter=mysql.seconds_behind_master, php /opt/monitor/mysql_monitor_seconds_behind_master.php

I realize this is over a year old, but this can be accomplished without PHP or perl..

You can also save yourself on password exposure in your zabbix conf files, by using a my.cnf file instead and a local user. just setup the my.cnf file on whatever servers and then you can globalize your zabbix_agentd.conf (we use an include file in the main zabbix_agentd.conf and all of our custom commands in it.)

Here's an example of how I look at slave lag:

## Mysql Slave Check
## Requires user with 'replication client' granted. (grant replication client on *.* to 'user'@'host/wildcard/whatever' identified by 'password'; flush privileges; )
## Returns back # of seconds slave is behind master. If it's null, we'll return -1.
## Make sure /path/to/zabbix/my.cnf is owned by zabbix user.
#
#
UserParameter=db.mysql.slave.lag,mysql --defaults-file=/path/to/zabbix/my.cnf -u zabbix -Bse "show slave status \G" | grep Seconds_Behind_Master | awk '{ print $2 }' | sed -e 's/^NULL$/-1/'


Your my.cnf can look like this:

[client]
host = mysql.database.hostname.com
user = zabbix
password = <zabbix mysql user pass in plain text here.>
#socket = /var/run/mysqld/mysqld.sock


then just make sure your zabbix_agentd process user owns the my.cnf and chmod go-rwx your my.cnf so nobody else can read it except root and zabbix_agentd and you're set.

If you don't want to use a my.cnf file, then remove --defaults-file and add a -p<password> string instead..

Hope this helps.

[LPby]

UserParameter=mysql.ping,mysqladmin -uroot ping|grep alive|wc -l
UserParameter=mysql.seconds-behind-master,SEC=$(mysql -uroot -e 'show slave status\G' | grep Seconds | awk '{print $2}'); if [ "$SEC" = "NULL" ]; then SEC="-1"; fi; echo $SEC
UserParameter=mysql.slave-running,mysql -uroot -e "show slave status" -E | grep "Slave_SQL_Running:" | awk '{print $2}' | grep Yes >/dev/null && echo 1 || echo 0

[jvillain]

Slightly different

mysql -u zabbix --password=<some_password> -Bse "show slave status \G" |grep Slave_IO_Running |grep Yes >/dev/null; echo $?

It's inefficient as we start a mysql process and two grep processes. But I only run it every 5 mins so I am not that worried. If you want to reverse the retun codes change Yes to No.

Hope that helps some one.

[srinivasan]

Mysql Replication

Hi

Please use the following

UserParameter=mysql.slaverunning,mysql -u"username" -p"password" -e "show slave status" -E | grep Slave_SQL_Running | grep Yes| wc -l