This template retrieves replication status of MySQL 5.1 (or above) slave server installed on Linux or Unix machine. This template should be assigned on MySQL slave server.

Prerequisites: SSH and Perl installed on the target server.

Credentials:
- To execute script on target machine: Linux/Unix user on target machine;
- To get replication statistic: MySQL user with SUPER or REPLICATION CLIENT privilege (provided in arguments).

Monitored Components

Slave Replication Status

This counter returns MySQL slave replication status.

Note: Before using this monitor, the correct argument should be set in all monitors. All monitors require the following arguments:
perl ${SCRIPT}  mysql_command,MySQL_user,MySQL_password
where
  mysql_command - This is full path to MySQL command.
   MySQL_user - This is MySQL username with SUPER or REPLICATION CLIENT privilege.
MySQL_password - This is password to MySQL user.

Below is an example using the Scripts Arguments field:
perl ${SCRIPT} /usr/bin/mysql repuser password

The returned values are as follows:

Slave IO State – This component returns the state for the slave I/O thread. This tells you what the thread is doing. More information about IO states could be found here: http://dev.mysql.com/doc/refman/5.7/en/slave-io-thread-states.html.

Possible values:
0 - Waiting for master update;
1 - Connecting to master;
2 - Checking master version;
3 - Registering slave on master;
4 - Requesting binlog dump;
5 - Waiting to reconnect after a failed binlog dump request;
6 - Reconnecting after a failed binlog dump request;
7 - Waiting for master to send event;
8 - Queuing master event to the relay log;
9 - Waiting to reconnect after a failed master event read;
10 - Reconnecting after a failed master event read;
11 - Waiting for the slave SQL thread to free enough relay log space;
12 - Waiting for slave mutex on exit;
13 – Unknown.

Slave IO Running – This component returns the state of slave server. Whether the I/O thread is started and has connected successfully to the master. Possible values:
0 - Running;
1 - Not Running;
2 - Connecting;
3 – Unknown.

Last Error – This component returns the error message of the most recent slave error. Empty string mean “no error.”

Seconds Behind Master – This component returns the time difference in seconds between the slave SQL thread and the slave I/O thread. When the slave is actively processing updates, this statistic shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the most event currently being processed on the slave. When no event is currently being processed on the slave, this value is 0.

If the network connection between master and slave is fast, the slave I/O thread is very close to the master, so this field is a good approximation of how late the slave SQL thread is compared to the master. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.

In MySQL 5.7, this field is NULL (undefined or unknown) if the slave SQL thread is not running, or if the SQL thread has consumed all of the relay log and the slave I/O thread is not running. (In older versions of MySQL, this field was NULL if the slave SQL thread or the slave I/O thread was not running or was not connected to the master.) If the I/O thread is running but the relay log is exhausted, Seconds_Behind_Master is set to 0.

Last IO Error – This component returns the error message of the most recent error that caused the I/O thread to stop. Empty string mean “no error.”

Last SQL Error – This component returns the error message of the most recent error that caused the SQL thread to stop. Empty string mean “no error.”

Relay Log Space – This component the total combined size of all existing relay log files.

Configuring Windows Remote Management (WinRM)

1. 1. If not already done so, install PowerShell 2.0 and WinRM on the SAM and target servers. Powershell 2.0 can be found here: http://support.microsoft.com/kb/968930.
2. 2. On the SAM server, open a command prompt as an administrator. To do this, perform the following step:

• Go to the Start menu and right-click the cmd.exe and then select Run as Administrator.

1. 3. Enter the following in the command prompt:
          winrm quickconfig
   winrm set winrm/config/client @{TrustedHosts="*"}
2. 4. On the target server, open a command prompt as an Administrator and enter the following:
          winrm quickconfig
   winrm set winrm/config/client @{TrustedHosts="IP_ADDRESS"}

where IP address is the IP address of your SAM server.

Portions of this template are based on the following articles:
http://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html.

Last updated: 7/2/2014