Quickly obtain database connection information and some extensions through zabbix

background

As the number of application systems continues to increase, active threads that originally did not give alarms begin to give alarms frequently (about twice a day). Although the number of alarms is small and the threshold of this monitoring item is not high (no more than 50), the threads-running of the database is a point that must be taken seriously for operation and maintenance.

Generally, alarms appear in the middle of the night, and it is impossible to manually record which connections are being processed after threads-running passes the line. There are no good tools on the market that can automatically record this data, so the action function of zabbix is needed at this time.

zabbix configuration

1. Define monitoring items

Here I am lazy and directly use the Threads Running monitoring item in the percona template:

insert image description here

2. Define triggers

The same applies to Percona trigger settings:

insert image description here

3. Create an action

Create actions in the order shown below:

insert image description here

4. Action conditions

insert image description here

The action will be triggered only when conditions A, B, C, and D are met. Here, try to filter as detailed as possible to avoid incorrect calls to Zabbix.

5. Improve the operation content

insert image description here

The ssh method is applicable here. Of course, you can also use the custom script option in the type column, which is to give sudo permissions to the zabbix client.

The command bar is filled with /bin/sh /opt/connect.sh command. This is easy to understand. It directly calls the connect.sh script. The specific script is attached in the following text.

6. Modify zabbix-agent configuration

Enter the monitored server:

vim /etc/zabbix/zabbix_agentd.conf
    
    EnableRemoteCommands=1 #Add this parameter to allow the zabbix server remote command service zabbix-agent restart

At this point, all the configurations related to zabbix have been completed. Next, you only need to put the written processing script into the /opt directory.

Functional script

What we want to achieve this time is to output information such as which account, which IP, which SQL is being executed, etc. when there are more than 50 connections. The script is as follows:

#!/bin/sh

    export PATH=$PATH:/usr/bin
    da=`date +%Y%m%d`
    dc=`date +%Y-%m-%d" "%H:%M:%S`
    
    echo $dc"-------------------------------I am the dividing line------------------------------------" >> /tmp/ok_$da.log
    
    /usr/local/mysql/bin/mysql -uroot -pXXX -e "select * from information_schema.PROCESSLIST where COMMAND != 'Sleep' order by TIME DESC;" >> /tmp/ok_$da.log

Extensions

Since Zabbix can call scripts when an alarm is triggered, is it possible to let Zabbix handle more complex tasks?

Database connection, lock, storage engine and other information

#!/bin/sh
export PATH=$PATH:/usr/bin
da=`date +%Y%m%d`
dc=`date +%Y-%m-%d" "%H:%M:%S`
echo $dc"-------------------------------I am the dividing line------------------------------------" >> /home/zabbix/engine_log/engine_log_$da.log
/usr/bin/mysql -hlocalhost -uroot -pXXX -e "show engine innodb status \G;" >> /home/zabbix/engine_log/engine_log_$da.log
echo -e "\n\n\n" >> /home/zabbix/engine_log/engine_log_$da.log
echo $dc"-------------------------------I am the dividing line------------------------------------" >> /home/zabbix/processlist/processlist_$da.log
/usr/bin/mysql -hlocalhost -uroot -pXXX -e "select * from information_schema.processlist where time>=0 and command !='sleep' order by time desc \G;" >> /home/zabbix/processlist/processlist_$da.log
echo -e "\n\n\n" >> /home/zabbix/processlist/processlist_$da.log
echo $dc"-------------------------------I am the dividing line------------------------------------" >> /home/zabbix/lock/lock_$da.log
/usr/bin/mysql -hlocalhost -uroot -pXXX -e "select 'Blocker' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, tx.trx_tables_in_use, tx.trx_tables_locked, tx.trx_rows_locked from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_trx_id = tx.trx_id union all select 'Blockee' role, p.id, p.user, left(p.host, locate(':', p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, tx.trx_tables_in_use, tx.trx_tables_locked, tx.trx_rows_locked from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_trx_id = tx.trx_id \G;" >> /home/zabbix/lock/lock_$da.log
echo -e "\n\n\n" >> /home/zabbix/lock/lock_$da.log
val=`/usr/bin/mysql -hlocalhost -uroot -pXXX -N -e "show variables like 'general_log'" |awk '{print $2}'`
if [ $val = 'OFF' ];
then
        /usr/bin/mysql -hlocalhost -uroot -pXXX -e "set global general_log=0;"
else
        exit 0;
fi

When the database is under great pressure, you can record the lock information, connection information and storage engine information. The so-called high pressure can be defined as the following trigger items:

threads-running: Too many connections running
Innodb Row Lock Waits: Lock wait time is too long
Com Select\Update\Insert\Delete: Too many changes in production
Incoming\Outgoing network traffic: When the inbound and outbound traffic is abnormal

These can all serve as trigger conditions.

Kill long sql

#!/bin/sh
export PATH=$PATH:/usr/bin
da=`date +%Y%m%d`
dc=`date +%Y-%m-%d" "%H:%M:%S`
user="root"
password="XXX"
val=`mysql -u$user -p$password -N -e "select count(*) from information_schema.processlist where time>=180 and command ='Query' and user in ('job_name','report')" | awk '{print $1}'`
if [ $val -gt 0 ];
then
        echo $dc"-------------------------------I am the dividing line------------------------------------" >> /home/zabbix/kill_log/long_query_$da.log
        mysql -u$user -p$password -e "select * from information_schema.processlist where time>=180 and command ='Query' and user in ('job_name','report') order by time desc \G;" >> /home/zabbix/kill_log/long_query_$da.log
        echo -e "\n\n\n" >> /home/zabbix/kill_log/long_query_$da.log
        count=`mysql -u$user -p$password -N -e "select id from information_schema.processlist where time>=180 and command ='Query' and user in ('job_name','report')"`
        for id in $count;
        do
           mysql -u$user -p$password -e "kill $id"
        done
else
        exit 0;
fi

Sometimes some jobs or report SQL will be executed there for a long time, causing impact on other businesses. We can simply make a judgment that when the database connection surges, it is mostly due to a large SQL being rammed there. At this time, Zabbix can call this script to kill SQL initiated by special accounts for more than 180 seconds. The conditions for killing sql can be customized in the script.

Of course, SQL statements that are known to take a long time to process should be executed from the database.

Delete useless logs

#!/bin/sh
logdir='/mysql/logs'
binlog='/mysql/binlog'
var_percent=`df -h |grep var|grep dev/sda|awk '{print $5}'|awk -F% '{print $1}'`
if [ -z $var_percent ] || [ $var_percent -lt 90 ];then
        echo "never mind"
        exit 0
fi
date >>$binlog/del_list.txt
list=`ls -l --time-style='+%Y-%m-%d %H:%M:%S' $binlog/mysql-bin.0?????|awk '{print $6","$7","$8}'`
for i in $list
do
        filetime=`echo $i|awk -F "," '{print $1,$2}'`
        filetimestamp=`date -d "$filetime" +%s`
        cur_time = `date + %s`
        if [ $(($cur_time - $filetimestamp)) -gt $((3*24*3600)) ];then
                filename=`echo $i|awk -F, '{print $3}'`
                echo "$filename will delete">>$binlog/del_list.txt
                /bin/rm $filename
        fi
done
if [ -f $logdir/mysql-slow.log ];then
        slow_log_size=`stat $logdir/mysql-slow.log|grep 'Size:'|awk -F ':' '{print $2}'|awk '{print $1}'`
        if [ $slow_log_size -gt $((2*1024*1024*1024)) ];then
                echo "$logdir/mysql-slow.log">>$logdir/del_list.txt
                /bin/rm $logdir/mysql-slow.log
        fi
fi

Sometimes binlog and slowlog are not set to be deleted automatically, and the disk space will be full over time. At this time, we can associate the Free disk space on /mysql monitoring item, check the threshold, and call the above script to clean up useless binlog and slowlog.

In some situations that rely on binlog, such as when the master and slave are interrupted and need to be restored later, it needs to be used with caution.

Reference Links:

Get database connection information and some extensions through zabbix: https://www.jb51.net/article/207412.htm

This is the end of this article about obtaining database connection information and some extensions through zabbix. For more relevant zabbix database connection content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: