Use zabbix to comprehensively monitor MySQL

In the previous article, using the zabbix MySQL monitoring template to monitor MySQL is not detailed enough. This article continues to explore the detailed monitoring of MySQL, including MySQL instances, MySQL master-slave replication, and MySQL storage engine. The MySQL version used in this article is 5.5. The template used in this article is mainly changed by the template provided by FROMDUAL.

In the previous article, using the zabbix MySQL monitoring template to monitor MySQL is not detailed enough. This article continues to explore the detailed monitoring of MySQL, including MySQL instances, MySQL master-slave replication, and MySQL storage engine. The MySQL version used in this article is 5.5. The template used in this article is mainly changed by the template provided by FROMDUAL.

In the previous article, we used zabbix's MySQL monitoring template to monitor MySQL.

The monitoring of MySQL is not detailed enough. This article continues to explore the detailed monitoring of MySQL, including MySQL instances, MySQL master-slave replication, and MySQL storage engine.

The MySQL version used in this article is 5.5

The template used in this article is changed by using the template provided by FROMDUAL. FROMDUAL officially uses the Perl language to write collection scripts and then pushes data to zabbix server through zabbix trapper. I think FROMDUAL's official configuration method is cumbersome and I am not familiar with the Perl language, so after reading the official Perl script, I came up with the idea of using the Shell language again. The item name in the template has changed. Other items are generally the same as those in the FROMDUAL official template.

1. Monitoring Principles

Show global status; view the global status

Show global variables; view global variable settings

Mysqladmin MySQL management tool

Show master status; view Master status

Show slave status; view Slave status

Show binary logs; view binary log files

Show engine innodb status \ G view InnoDB Storage engine status

Show engine myisam status \ G view the status of the MyISAM storage engine

You can also obtain information about the InnoDB Storage engine by viewing the information_schema database.

2. Add a MySQL Monitoring account

Grant usage, PROCESS, SUPER, replication client, replication slave on *. * TO 'zabbixagent' @ 'localhost' identified by 'zabbixagent ';

Flush privileges;

Create a. my. cnf file in the/usr/local/zabbix/etc/directory.

[mysql]user=zabbixagentpassword=zabbixagent[mysqladmin]user=zabbixagentpassword=zabbixagent

3. Add the zabbix sub-configuration file mysql_status.conf

### MySQL DB InfomationUserParameter=mysql.status[*],echo "show global status where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'UserParameter=mysql.variables[*],echo "show global variables where Variable_name='$1';"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $$2}'UserParameter=mysql.ping,mysqladmin --defaults-file=/usr/local/zabbix/etc/.my.cnf ping|grep -c  aliveUserParameter=mysql.version,echo "select version();"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N#### MySQL Master InformationUserParameter=mysql.master.Slave_count,echo "show slave hosts;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -lUserParameter=mysql.master.Binlog_file,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $1}'UserParameter=mysql.master.Binlog_number,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}'|awk -F. '{print $2}'UserParameter=mysql.master.Binlog_position,echo "show master status;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}'UserParameter=mysql.master.Binlog_count,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|wc -lUserParameter=mysql.master.Binlog_total_size,echo "show binary logs;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{sum+=$NF}END{print  sum}'#### MySQL Slave InformationUserParameter=mysql.slave.Seconds_Behind_Master,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Seconds_Behind_Master"|awk '{print $2}'UserParameter=mysql.slave.Slave_IO_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_IO_Running"|awk '{print $2}'UserParameter=mysql.slave.Slave_SQL_Running,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Slave_SQL_Running"|awk '{print $2}'UserParameter=mysql.slave.Relay_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Relay_Log_Pos"|awk '{print $2}'UserParameter=mysql.slave.Exec_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Exec_Master_Log_Pos"|awk '{print $2}'UserParameter=mysql.slave.Read_Master_Log_Pos,echo "show slave status\G"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf|grep "Read_Master_Log_Pos"|awk '{print $2}'#### MySQL InnoDB Information#UserParameter=mysql.innodb[*],/usr/local/zabbix/bin/mysql_innodb_status.sh $1####MySQL MyISAM Information#

Some monitoring items related to InnoDB need to be obtained by using a separate script

Mysql_innodb_status.sh

#!/bin/bash#Get InnoDB Row Lock Details and InnoDB Transcation Lock Memory#mysql> SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;#+-------------+---------------+-------------+#| rows_locked | rows_modified | lock_memory |#+-------------+---------------+-------------+#|        NULL |          NULL |        NULL |#+-------------+---------------+-------------+#1 row in set (0.00 sec)#+-------------+---------------+-------------+#| rows_locked | rows_modified | lock_memory |#+-------------+---------------+-------------+#|           0 |             0 |         376 |#+-------------+---------------+-------------+#Get InnoDB Compression Time#mysql> SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;#+---------------+-----------------+#| compress_time | uncompress_time |#+---------------+-----------------+#|             0 |               0 |#+---------------+-----------------+#1 row in set (0.00 sec)#Get InnoDB Transaction states#TRX_STATETransaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.#mysql> SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;#+---------+-----+#| state   | cnt |#+---------+-----+#| running |   1 |#+---------+-----+#1 row in set (0.00 sec)innodb_metric=$1case $innodb_metric in   Innodb_rows_locked)                      value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $1}')                      if [ "$value" == "NULL" ];then                         echo 0                      else                         echo $value                      fi                    ;;   Innodb_rows_modified)                      value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $2}')                      if [ "$value" == "NULL" ];then                         echo 0                      else                         echo $value                      fi                    ;;   Innodb_trx_lock_memory)                      value=$(echo "SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N| awk '{print $3}')                      if [ "$value" == "NULL" ];then                         echo 0                      else                         echo $value                      fi                    ;;      Innodb_compress_time)                      value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $1}')                      echo $value                      ;;            Innodb_uncompress_time)                      value=$(echo "SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP;"|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|awk '{print $2}')                      echo $value                      ;;            Innodb_trx_running)                         value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep running|awk '{print $2}')                         if [ "$value" == "" ];then                            echo 0                         else                            echo $value                         fi                        ;;       Innodb_trx_lock_wait)                         value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep lock_wait|awk '{print $2}')                         if [ "$value" == "" ];then                            echo 0                         else                            echo $value                         fi                        ;;    Innodb_trx_rolling_back)                         value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep rolling_back|awk '{print $2}')                         if [ "$value" == "" ];then                            echo 0                         else                            echo $value                         fi                        ;;    Innodb_trx_committing)                         value=$(echo 'SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state;'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep committing|awk '{print $2}')                         if [ "$value" == "" ];then                            echo 0                         else                            echo $value                         fi                        ;; Innodb_trx_history_list_length)                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "History list length"|awk '{print $4}'                        ;;    Innodb_last_checkpoint_at)                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Last checkpoint at"|awk '{print $4}'                        ;;   Innodb_log_sequence_number)                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log sequence number"|awk '{print $4}'                        ;;    Innodb_log_flushed_up_to)                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Log flushed up to"|awk '{print $5}'                        ;;   Innodb_open_read_views_inside_innodb)                         echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "read views open inside InnoDB"|awk '{print $1}'                        ;;        Innodb_queries_inside_innodb)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries inside InnoDB"|awk '{print $1}'                        ;;        Innodb_queries_in_queue)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "queries in queue"|awk '{print $5}'                        ;;        Innodb_hash_seaches)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "hash searches"|awk '{print $1}'                        ;;       Innodb_non_hash_searches)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "non-hash searches/s"|awk '{print $4}'                        ;;       Innodb_node_heap_buffers)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "node heap"|awk '{print $8}'                       ;;       Innodb_mutex_os_waits)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $9}'                       ;;       Innodb_mutex_spin_rounds)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $6}'|tr -d ','                       ;;       Innodb_mutex_spin_waits)                        echo 'show engine innodb status\G'|mysql --defaults-file=/usr/local/zabbix/etc/.my.cnf -N|grep "Mutex spin waits"|awk '{print $4}'|tr -d ','                       ;;                    *)                    echo "wrong parameter"                    ;;esac

4. Add a monitoring template

The attachment includes monitoring of MySQL instances, MySQL Master, MySQL Slave, and MySQL InnoDB.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1216191311-0.jpg "style =" float: none; "title =" innodb.png "alt =" wKioL1Sen5fzYycrAAVCrBygdz0116.jpg "/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1216192557-1.jpg "style =" float: none; "title =" mysql.jpg "alt =" wKiom1SenuqBCiXZAAUgZS7BGIY685.jpg "/>

References:

Http://www.percona.com/doc/percona-monitoring-plugins/1.0/cacti/mysql-templates.html

Http://hi.baidu.com/ytjwt/item/2bbd69a0869d1ef314329b6a

Http://www.fromdual.com/mpm-installation-guide

Http://www.fromdual.com/mysql-performance-monitor

Http://dev.mysql.com/doc/refman/5.5/en/information-schema.html

FROMDUAL official templates and scripts are as follows. For more information, see

Http://support.fromdual.com/admin/public/download.php? Operation1_download&file_name=mysql_performance_monitor-0.9.3.tar.gz & id = 1996