Monitor synchronization between master and slave mysqlreplication using nagios

# Cdetcnagioscommand # wgethttp: www. james. rcpt. tosvntrunknagioscheck_mysql_replicationcheck_mysql_replication.pl # chmod755check_mysql_replication.pl look at usage: #. check_mysql_replication.pl-hcheck_mysql_replicati

#Cd/Etc/nagios/command

# Wget http://www.james.rCpT. to/svn/TrUnk/nagios/check_mysql_repliCatIon/check_mysql_replication.pl

#Chmod755 check_mysql_replication.pl

See Usage:

#./Check_mysql_replication.pl-h
Check_mysql_replication.pl: check replication between MySQL database instaNcEs

Check_replication.pl [-- slave ] [-- Slave-pass ]
[-- Slave-port ] [-- Slave-user ] [-- Master ]
[-- Master-pass ] [-- Master-port ] [-- Master-user ]
[-- Crit ] [-- Warn ] [-- Check-random-database]
[-- Table-rows- Diff-Absolute-crit ]
[-- Table-rows-diff-absolute-warn ]
[-- Schema ]

-- Slave -MySQL instance running as a slave server
-- Slave-port -Port for the slave
-- Slave-user -Username with File/Process/Super privs
-- Slave-pass -Password for above user
-- Master -MySQL instance running as server (overr IdE)
-- Master-port -Port for the master (override)
-- Master-user -Username for master (override)
-- Master-pass -Password for master
-- Crit -Number of complete master binlogs for critical StatE
-- Warn -Number of complete master binlog for warning state
-- Check-random-database-Select a random DB from the slave's list
Databases and compare to the master's info RmAtion
These (ne EdSELECT priv)
-- Table-rows-diff-absolute-crit -If we do the check-random-database,
Then en SuRe that the change in row count between master and
Slave is below this threshold, and go critical if not
-- Table-rows-diff-absolute-warn -If we do the check-random-database,
Then ensure that the change in row count between master and
Slave is below this threshold, and go warning if not
-- Schema -The database schema to use
-- Help-This help page
-- Version-Script version information

By default, you shoshould use your configured replication user, as you will
Then only need to specify the user and password once, and this script will
FindThe master from the slave's running configuration.

Critical and warning values are now measured as amount of a complete master
Sized binlog. If your master has the default 1 GB binlog size, then specifying
A warning value of 0.1 means that your will let the slave get 100 MB out
SyncBefore warning; you may want SetWarning to 0.01, and critical at 0.1.

MySQL 3: GRANT File, Process on *. * TO repl@192.168.0. % IDENTIFIED
MySQL 4: GRANT Super, Replication client on *. * TO repl@192.168.0. % IDE...

If you want to use the check-random-database option, then the user needs
SELECT privileges on all replicated tables on the master and the slave.

Note: Any mysql DuMp tables (for backups) may lock large tables for a long
Time. If you DumpFrom your slave for this, then your master will gallop
Away from your slave, and the difference will become large. The trick is
Set 'crit' above this differnce and 'warn' below.

If you are using the host name "localhost" to connect to port forwards, you'll
Probably hit the issue where MySQL uses the named pipe (socket) on File 
System instead of there TCP loopback DdRess. Use host name "127.0.0.1 ".

(C) 2010 James Bromberger, james @ RcpT. to, www. james. rcpt.

First inCommandLine test:

#/Etc/nagios/command/check_mysql_replication.pl -- slave 192.168.1.132 -- slave-user -- slave-passPasswd-- Master 192.168.1.131 -- master-user -- master-pass passwd
OK: 0.000 diff, 0 secs, 192.168.1.131: 3306 (5.0.77-log)-> 192.168.1.132: 3306 (5.0.77-log)

Run successfully. We configure it to nagios.

# Vim objects/commands. cfg

Add:

Define command {
Command_name check_mysql_replication
Command_line/etc/nagios/command/check_mysql_replication.pl -- slave $ HOSTADDRESS $ -- slave-user $ ARG1 $ -- slave-pass $ ARG2 $ --
Master $ ARG3 $ -- master-user $ ARG4 $ -- master-pass $ ARG5 $
}

# Vim objects/hosts. cfg

Add:

Define service {
Use generic-service; Name of service template to use
Host_name mysql_slave
Service_description mysql replication
Check_command check_mysql_replication! User! Passwd! 192.168.1.131! User! Passwd