mysql_monitorsh_0">1、 mysql_monitor.sh
主要关注mysql的这些状态：
uptime| version| questions| slowQueries| select| bytesSent| bytesReceived| ping| activeThread| connectThread| maxConnect| bigSql| bigTransaction | innodbLock| primaryKey| dbRole| readonly| superReadonly| ioRunning| sqlRunning| slaveBehind
#!/bin/bash
MYSQL_USER='zabbix_mysql_monitor'
MYSQL_PWD='zabbix_mysql_monitor_passwd'
MYSQL_PATH='/vdb/mysql/bin'
MYSQL_CONN="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PWD}"
MYSQL_ADMIN="${MYSQL_PATH}/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD}"main(){case $1 inuptime) result=`${MYSQL_CONN} -e "\s" 2>/dev/null|grep Uptime|awk -F ':' '{print $2}'`echo $result;;version) result=`${MYSQL_CONN} -e "\s" 2>/dev/null|grep 'Server version'|awk -F ':' '{print $2}'`echo $result;;questions)# qps 两次差集result=`${MYSQL_ADMIN} status 2>/dev/null |cut -f4 -d":"|cut -f1 -d"S"`echo $result;;slowQueries)# 两次差集result=`${MYSQL_ADMIN} status 2>/dev/null |cut -f5 -d":"|cut -f1 -d"O"`echo $result;;select)# 两次差集 selectresult=`${MYSQL_ADMIN} extended-status 2>/dev/null |grep -w "Com_select"|cut -d"|" -f3`echo $result;;bytesSent)# 两次差集result=`${MYSQL_ADMIN} extended-status 2>/dev/null |grep -w "Bytes_sent" |cut -d"|" -f3`echo $result;;bytesReceived)# 两次差集result=`${MYSQL_ADMIN} extended-status 2>/dev/null |grep -w "Bytes_received" |cut -d"|" -f3`echo $result;;ping)result=`${MYSQL_ADMIN} ping 2>/dev/null| grep -c alive` if [ ${result} -eq 1 ] ; thenecho 0elseecho 1fi;;activeThread) result=`${MYSQL_CONN} -e "show global status like 'threads_running'\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result ;; connectThread) result=`${MYSQL_CONN} -e "show global status like 'threads_connected'\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result ;;maxConnect) result=`${MYSQL_CONN} -e "select @@max_connections value\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result ;; bigSql)# 超过30秒的sqlresult=`${MYSQL_CONN} -e "select count(*) value from information_schema.processlist where user not in ('event_scheduler','system user') and user not like 'mysql_router\%' and command<>'sleep' and command not like 'Binlog Dump%' and time>=30\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result if [ ${result} -gt 0 ] ; thenfilename=/tmp/bigsql`date +"%Y%m%d%H%M%S"`res=`${MYSQL_CONN} -e "tee $filename;select * from information_schema.processlist where user not in ('event_scheduler','system user') and user not like 'mysql_router\%' and command<>'sleep' and command not like 'Binlog Dump%' and time>=30\G" 2>/dev/null`fi;;bigTransaction)# 超过5分钟的事务result=`${MYSQL_CONN} -e "select count(*) value from information_schema.innodb_trx trx join information_schema.processlist pcl on trx.trx_mysql_thread_id=pcl.id where trx.trx_mysql_thread_id !=connection_id() and to_seconds(now())-to_seconds(trx_started)>300\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result if [ ${result} -gt 0 ] ; thenfilename=/tmp/bigtrx`date +"%Y%m%d%H%M%S"`res=`${MYSQL_CONN} -e "tee $filename;SELECT trx_mysql_thread_id AS PROCESSLIST_ID, NOW(), TRX_STARTED, TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME , USER, HOST, DB, TRX_QUERY FROM INFORMATION_SCHEMA.INNODB_TRX trx JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id WHERE trx.trx_mysql_thread_id !=connection_id() and to_seconds(now())-to_seconds(trx_started)>300\G" 2>/dev/null`fi;;innodbLock)result=`${MYSQL_CONN} -e "select ifnull(sum(waiting_age),0) value from (SELECT distinct a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, \"Metadata Lock\" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID) e\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $resultif [ ${result} -gt 60 ] ; thenfilename=/tmp/innodbLock`date +"%Y%m%d%H%M%S"`res=`${MYSQL_CONN} -e "tee $filename;SELECT distinct a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, \"Metadata Lock\" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G" 2>/dev/null`fi;;primaryKey)result=`${MYSQL_CONN} -e "select ifnull(count(distinct table_schema,table_name),0) value from information_schema.columns where table_schema not in ('sys','information_schema','performance_schema','mysql','sysaux','mysql_innodb_cluster_metadata') and (table_name not in (select distinct table_name from information_schema.columns where column_key='PRI') and (table_schema,table_name) not in (select table_schema,table_name from information_schema.views where table_schema not in ('sys','information_schema','performance_schema','mysql','sysaux','mysql_innodb_cluster_metadata')))\G" 2>/dev/null|grep -i Value|awk -F ':' '{print $2}'`echo $result;;dbRole) # 0是主库result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`if [ ${result} -eq 0 ] ; thenecho 0elseecho 1fi;;readonly)# 0表示只读状态正常（主库不检查readonly，从库readonly=1）result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`readonly=`${MYSQL_CONN} -e "select @@read_only value\G" 2>/dev/null|awk -F ':' '{print $2}'`readonly=`eval echo $readonly`if [ ${result} -eq 0 ] ; thenecho 0elif [ ${result} -gt 0 ]&[ ${readonly} -eq 1 ] ; thenecho 0elseecho 1fi;;superReadonly)# 0表示只读状态正常（主库不检查readonly，从库readonly=1）result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`readonly=`${MYSQL_CONN} -e "select @@super_read_only value\G" 2>/dev/null|awk -F ':' '{print $2}'`readonly=`eval echo $readonly`if [ ${result} -eq 0 ] ; thenecho 0elif [ ${result} -gt 0 ]&[ ${readonly} -eq 1 ] ; thenecho 0elseecho 1fi;;ioRunning)# 0表示IO进程状态正常result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`ioRunning=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|grep -i -w Slave_IO_Running|awk -F ':' '{print $2}'`ioRunning=`eval echo $ioRunning| tr [a-z] [A-Z]`if [ ${result} -eq 0 ] ; thenecho 0elif [ ${result} -gt 0 ]&[ ${ioRunning} == 'YES' ] ; thenecho 0elseecho 1fi;;sqlRunning)result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`sqlRunning=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|grep -i -w Slave_SQL_Running|awk -F ':' '{print $2}'`sqlRunning=`eval echo $sqlRunning| tr [a-z] [A-Z]`if [ ${result} -eq 0 ] ; thenecho 0elif [ ${result} -gt 0 ]&[ ${sqlRunning} == 'YES' ] ; thenecho 0elseecho 1fi;;slaveBehind)result=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|wc -l`slaveBehind=`${MYSQL_CONN} -e "show slave status\G" 2>/dev/null|grep Seconds_Behind_Master|awk -F ':' '{print $2}'`if [ ${result} -eq 0 ] ; thenecho 0slaveBehind=0elseecho ${slaveBehind}fiif [ ${slaveBehind} -gt 60 ] ; thenfilename=/tmp/slave`date +"%Y%m%d%H%M%S"`res=`${MYSQL_CONN} -e "tee $filename;show slave status\G" 2>/dev/null`fi;;*)echo "Usage:$0 (uptime| version| questions| slowQueries| select| bytesSent| bytesReceived| ping| activeThread| connectThread| maxConnect| bigSql| bigTransaction | innodbLock| primaryKey| dbRole| readonly| superReadonly| ioRunning| sqlRunning| slaveBehind)" ;; esac
}main $1
mysql_monitorshetczabbixsharezabbixexternalscripts_183">2、把mysql_monitor.sh放在/etc/zabbix/share/zabbix/externalscripts/，并赋予它执行权限
mysqlMYSQL_USERzabbix_mysql_monitor_sqlmysql_184">3、另外你需要在mysql数据库中创建一个用来监控用的账户MYSQL_USER=‘zabbix_mysql_monitor’ ，以下这些事sql语句，是需要进去mysql交互界面输入的。
create user 'zabbix_mysql_monitor'@'localhost' identified with mysql_native_password by 'zabbix_mysql_monitor_passwd';grant select,process,replication client on *.* to 'zabbix_mysql_monitor'@'localhost' ;flush privileges;
zabbixzabbix_agent2dmysql_confmysqlconf_193">4、在/etc/zabbix/zabbix_agent2.d/mysql_conf写入一个文件mysql.conf
UserParameter=mysql.status[*],/etc/zabbix/share/zabbix/externalscripts/mysql_monitor.sh $1
