Zabbix's MySQL Monitor

1.LINUX-NODE2 Node Installation database

[[email protected] ~]# yum install-y mariadb-server[[email protected] ~]# systemctl start mariadb[[email protected] ~]# N ETSTAT-TULNP |grep 3306tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 41299/mysqld

2.Zabbix Adding database Host monitoring

Passive mode is not required for hostname, but the active mode must be consistent with the host hostname. "Configuration"-"host" (fill in Host information)-"Templates" (link mysql template)

After the successful creation, you can view the corresponding monitoring information to MySQL, the database of the increase and delete,

command line to view the information obtained:[[email protected] ~]# zabbix_get -s linux-node2 -k  Mysql.status[com_begin]0[[email protected] ~]# zabbix_get -s linux-node2 -k  mysql.status[slow_queries]0 Database template Monitoring configuration is mainly from the Linux-node2 node:/etc/zabbix/zabbix_agentd.d/userparameter_ Configuration of the mysql.conf  [[Email protected] ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_ mysql.conf # for all the following commands home should be  set to the directory that has .my.cnf file with password  Information.# flexible parameter to grab global variables. on the  frontend side, use keys like mysql.status[com_insert].# key syntax  is mysql.status[variable]. userparameter=mysql.status[*],echo  "Show global status where variable_name= ';"  | home=/var/lib/zabbix mysql -n | awk  ' {print $$2} '     # MySQL's status gets # flexible parameter to determine database or table size.  on the frontend side, use keys like mysql.size[zabbix,history,data].#  key syntax is mysql.size[<database>,<table>,<type>].# database  may be a database name or  ' All ' . default is  ' all ' .#  table may be a table name or  "All" . default is  "all" .#  type may be  "Data",  "index",  "free"  or  "both".  both is a  sum of data and index. Default is  "both". # database is  mandatory if a table is specified. type may be specified  always.# returns value in bytes.#  ' Sum '  on data_length or index_length alone needed  when we are getting this information for whole database  instead of a single tableuserparameter=mysql.size[*],bash -c  ' echo  ' Select  sum ($ (case  "$"  in both| "")  echo  "Data_length+index_length";;  data|index)  echo  "$3_length";;  free)  echo  "Data_free";; &NBSP;ESAC)  from information_schema.tables$ ([[  "$"  =  "All"  | |  !  "$  ]] |" |  echo  " where table_schema=\" $1\ "") $ ([[  "$"  =  "All"  | |  !  "$  ]] |" |  echo  "and table_name=\" $2\ "");  | home=/var/lib/zabbix mysql -n ' userparameter=mysql.ping,home=/var/lib/zabbix  mysqladmin ping | grep -c alive     #mysql的存活获取Userparameter=mysql.version,mysql -v     #mysql的版本获取 

3. Password-to-MySQL monitoring

The above to the database monitoring, is not the password directly get the value, this is unreasonable, then need to add a password to get monitoring data?

(1) First to the database authorization and password, through Zabbix users to obtain data, the authorization here because of the test, authorized the full authority, the formal production can not be set up. [[email protected] ~]# mysqlwelcome to the mariadb monitor.   commands end with ; or \g.your mariadb connection id is  711server version: 5.5.56-mariadb mariadb servercopyright  (c)  2000, 2017,  oracle, mariadb corporation ab and others. type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement. mariadb [(None)]> grant all on *.* to [email protected]  identified by  "Zabbix"; query ok, 0 rows affected  (0.00 sec) mariadb [(none)]> flush  privileges; query ok, 0 rows affected  (0.00 sec) mariadb [(none)]> quit; Bye (2) Modify the monitoring configuration and add the user name password [[email protected] ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf userparameter=mysql.status[ *],echo  "Show global status where variable_name= ';"  | HOME=/var/lib/zabbix mysql -uzabbix -pzabbix -N | awk  ' {print  $$2} ' userparameter=mysql.size[*],bash -c  ' echo  "Select sum ($ (case " $ " in  both| "")  echo  "Data_length+index_length";;  data|index)  echo  "$3_length";;  free)  echo  "Data_free";;  ESAC)  from information_schema.tables$ ([[  "$"  =  "All"  | |  !  "$  ]] |" |  echo  " where table_schema=\" $1\ "") $ ([[  "$"  =  "All"  | |  !  "$  ]] |" |  echo  "and table_name=\" $2\ "");  | home=/var/lib/zabbix mysql -n ' userparameter=mysql.ping,home=/var/lib/zabbix  Mysqladmin ping -uzabbix -pzabbix | grep -c aliveuserparameter=mysql.version,mysql -v[[email protected]  ~]# systemctl restart zabbix-agent

After the modifications are complete, we can see that the item item shows not supported,

At this time, modify the Zabbix to the invalid monitoring item refresh time, the default is 600s, we changed to 30s. When the modifications are complete, they become enabled.

"Administration"--"general"--upper right corner Select "Other"--"Refresh Unsupported items (in sec)" to 30

4. Flexible use of macro variables (Macrros) for parameter configuration user name password monitoring

Configuring variables in the host

(1) Modify the configuration file: [[email protected] ~]# vim /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf  UserParameter=mysql.status[*],echo  ' Show global status where variable_name= ' ;"  | HOME=/var/lib/zabbix mysql -u$1 -p$2 -N | awk  ' {print $$2 } ' Userparameter=mysql.ping[*],home=/var/lib/zabbix mysqladmin -u$1 -p$2 ping| grep  -c alive[[email protected] ~]# systemctl restart zabbix-agent at this time, If the user name is not passed in the password access is denied:[[email protected] ~]# zabbix_get -s linux-node2 -k  mysql.status[slow_queries]enter password: error 1045  (28000):  Access denied  for user  ' slow_queries ' @ ' localhost '   (using password: yes) [[email protected ] ~]# zabbix_get -s linux-node2 -k mysql.status[zabbix,zabbix,slow_queries]0

(2) Modify the template, the variable value in the template can not be set, because when the template is connected to modify the variable, the variable value set in the template is automatically overwritten:

(3) Modify the item in the template: variable {$USER},{passwd} with additional arguments

Zabbix's MySQL Monitor