Master Zabbix Monitoring: SQL Queries, Binlog Status, Replication & Cleanup Scripts

1. Use Zabbix to monitor database SQL values

Edit the default monitoring script template (applicable to any SQL, adjust for Oracle if needed).

#!/bin/bash
host=127.0.0.1  # monitoring host IP
monitor='password'  # monitoring password
name=$3
login(){
  user=$1
  database=$2
  sql="$(cat /home/appop/script/MYSQL-MONITOR-SQL/${name}.sql)"
  /greatdb/svr/greatdb/bin/greatsql -u$user -h$host -p$monitor -P3306 $database -sN -e "$sql" 2>/dev/null
}
login $1 $2
# $1 = monitor user, $2 = database instance (e.g., information_schema), $3 = SQL file name without .sql

2. Create directory for monitoring SQL files mkdir -p /home/appop/script/MYSQL-MONITOR-SQL/ 3. Edit the SQL file

cat /home/appop/script/MYSQL-MONITOR-SQL/test.sql
select count(*) from information_schema.greatdb_binlog_server_status

4. Test the script

sh /home/appop/script/mysql-monitor.sh greatdb information_schema test.sql
0  # output shows the result of the SQL query

5. Zabbix key and command format

system.run[sh /home/appop/script/mysql-monitor.sh greatdb information_schema test.sql]

6. Monitor database binlog status

If the instance cannot be connected, the binlog server has an issue.

Zero result indicates the binlog replication service is not started.

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e "select count(*) from information_schema.greatdb_binlog_server_status"

Non‑zero result with errors:

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e "select count(*) from information_schema.greatdb_binlog_server_status where LAST_IO_ERROR!='' or LAST_SQL_ERROR!=''"

Non‑zero result with delay > 900 seconds:

/greatdb/svr/greatdb/bin/greatsql -ugreatdb -p -h127.0.0.1 -P3310 -e "select count(*) from information_schema.greatdb_binlog_server_status where SECONDS_BEHIND_SOURCE>900"

7. ClickHouse connection monitoring (ck.sql contains the SQL)

clickhouse-client -h 127.0.0.1 --port 9000 --user "default" --password "password" -m < ~/ck.sql

8. Database master‑slave replication monitoring script

#!/bin/bash
hostONE=127.0.0.1  # A host
hostTWO=127.0.0.2  # B host
monitor=   # password
user=      # user
database=   # database
ONE=$(greatdb -u$user -h$hostONE -p$monitor -P6310 $database -sN -e "show master status\G" | grep -o "seconds behind master:")
TWO=$(greatdb -u$user -h$hostTWO -p$monitor -P6310 $database -sN -e "show master status\G" | grep -o "seconds behind master:")
if [ "$ONE" = "$TWO" ] && echo 1 || echo 0; then
  : # 1 = replication normal, 0 = abnormal
fi

9. Zabbix historical data cleanup script (removes data older than 90 days)

#!/bin/bash
source /rdata1/encrypt/decrypt.sh  # load encrypted password
Date=$(date -d $(date -d "-90 day" +%Y%m%d) +%s)
mysql -uzabbix -p$db_pwd_mysql -h127.0.0.1 -e "
USE zabbix;
DELETE FROM history WHERE clock < $Date; OPTIMIZE TABLE history;
DELETE FROM history_str WHERE clock < $Date; OPTIMIZE TABLE history_str;
DELETE FROM history_uint WHERE clock < $Date; OPTIMIZE TABLE history_uint;
DELETE FROM trends WHERE clock < $Date; OPTIMIZE TABLE trends;
DELETE FROM trends_uint WHERE clock < $Date; OPTIMIZE TABLE trends_uint;
DELETE FROM events WHERE clock < $Date; OPTIMIZE TABLE events;
DELETE FROM history_text WHERE clock < $Date; OPTIMIZE TABLE history_text;
DELETE FROM history_log WHERE clock < $Date; OPTIMIZE TABLE history_log;
"

10. Database status monitoring via Zabbix UserParameter definitions

UserParameter=zxw.mysql.uptime_3306,/usr/bin/mysql_zabbix_3306.sh Uptime
UserParameter=zxw.mysql.Slave_readonly_3306,/usr/bin/mysql_zabbix_3306.sh Slave_readonly
UserParameter=zxw.mysql.Slave_running_3306,/usr/bin/mysql_zabbix_3306.sh Slave_running
UserParameter=zxw.mysql.User_connections_3306,/usr/bin/mysql_zabbix_3306.sh User_connections
UserParameter=zxw.mysql.Master_behind_3306,/usr/bin/mysql_zabbix_3306.sh Master_behind
UserParameter=zxw.mysql.Threads_running_3306,/usr/bin/mysql_zabbix_3306.sh Threads_running
UserParameter=zxw.mysql.Master_logfile_3306,/usr/bin/mysql_zabbix_3306.sh Master_logfile
UserParameter=zxw.mysql.Master_logpos_3306,/usr/bin/mysql_zabbix_3306.sh Master_logpos

#!/bin/bash
MYSQL_USER='user'
MYSQL_PWD='password'
MYSQL_HOST='127.0.0.1'
MYSQL_PORT='3306'
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"
MYSQL_CMD="/usr/bin/mysql -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

if [ $# -ne 1 ]; then echo "arg error!"; exit 1; fi
case $1 in
  Uptime)
    result=$( $MYSQL_CONN status | cut -f2 -d":" | cut -f1 -d"T" )
    echo $result
    ;;
  Slave_readonly)
    result=$( $MYSQL_CMD -e 'show variables like "read_only"' | grep read_only | grep ON | wc -l )
    echo $result
    ;;
  Slave_running)
    result=$( $MYSQL_CMD -e "show slave status\G" | grep -E "Slave_IO_Running|Slave_SQL_Running" | awk '{print $2}' | grep -c Yes )
    echo $result
    ;;
  User_connections)
    result=$( $MYSQL_CMD -e "show variables like 'max_user_connections'" | grep connections | awk '{print $2}' )
    echo $result
    ;;
  Master_behind)
    result=$( $MYSQL_CMD -e "show slave status\G" | grep -E "Seconds_Behind_Master" | awk '{print $2}' )
    echo $result
    ;;
  Threads_running)
    result=$( $MYSQL_CMD -e "show global status like '%threads_running%'" | grep running | awk '{print $2}' )
    echo $result
    ;;
  Master_logfile)
    result=$( $MYSQL_CMD -e "show slave status\G" | grep Master_Log_File | awk -F"bin." '{print $2}' | uniq | wc -l )
    echo $result
    ;;
  Master_logpos)
    result=$( $MYSQL_CMD -e "show slave status\G" | grep Master_Log_Pos | awk '{print $2}' | uniq | wc -l )
    echo $result
    ;;
  *)
    echo "Usage: $0 (Uptime|Slave_readonly|Slave_running|User_connections|Master_behind|Threads_running)"
    ;;
esac

These scripts can be integrated into Zabbix as system.run items or as custom UserParameter entries to provide real‑time monitoring of MySQL/GreatDB health, replication lag, binlog status, and automatic cleanup of old monitoring data.