Zabbix monitoring MySQL (Windows and Linux environments)

A Linux environment to monitor MySQL

Zabbix Serverbring your own.MySQLPlugins to monitorMySQLdatabase templates, only need to be configured wellAgentclient, and then inWebadd a template to the host to the end of the line

Monitoring project:

Number of updates performed by Com_update:mysql

Number of queries executed by Com_select:mysql

Com_insert:mysql number of insertions performed

Com_delete: Number of deletions performed

Com_rollback: Number of actions to perform rollback

bytes_received: Number of bytes accepted

Bytes_sent: Number of bytes Sent

Slow_queries: Number of slow query statements

Com_commit: Number of things to confirm

Com_begin: Number of things to start

Uptime: Number of seconds the server has started

Questions: Number of statements sent to the server by the client

1) Create Zabbix link mysql username, password and grant permissions.

Mysql> Grant All on * * to [e-mail protected] ' localhost ' identified by ' 123456 ';mysql> flush privileges;

2) create the. my.cnf connection file under the Zabbix_agent service directory

Cd/usr/local/zabbix/etc/vim. my.cnf[client]user=zabbixpassword=123456

Attention:
If the database grant is authorized for localhost, this. MY.CNF does not have to add the host parameter to "Configure as above"
However, if grant authorization is for a native IP (such as 192.168.1.25), then the host parameter will be specified in the. my.cnf file:host=192.168.1.25

3) Configure the MySQL key file

This can be copied from the unpacking package at the Zabbix installation:

Cp/usr/local/src/zabbix3.0.3/conf/zabbix_agentd/userparameter_mysql.conf/usr/local/zabbix/etc/zabbix_ agentd.conf.d/

4) Replace the Zabbix installation path, note that if MySQL is not configured environment variable may not find the MySQL command, you can use the MySQL full path

See the path settings like Home=/var/lib/zabbix, and replace the path with/usr/local/zabbix/etc/, which is the directory path where the. my.cnf file is located above.

cd /usr/local/zabbix/etc/zabbix_agentd.conf.d/vim 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=/usr/local/zabbix/etc/ mysql -N | awk  ' {print $$2} ' #  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=/usr/local/zabbix/etc/ mysql -n ' userparameter=mysql.ping,home=/usr/local/zabbix/ etc/ mysqladmin ping | grep -c aliveuserparameter=mysql.version,mysql -v%s# /var/lib/zabbix#/usr/local/zabbix/etc/#       #用命令替换

The

can also be monitored with this shell script, including master-slave monitoring:

### mysql db infomationuserparameter=mysql.status[*],echo "show global status  Where variable_name= ' $ '; "| mysql--defaults-file=/usr/local/zabbix/etc/.my.cnf -n|awk  ' {print $$2} ' UserParameter= Mysql.variables[*],echo "Show global variables where variable_name= ';" | 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  \ n} ' |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} '

5) Modify the zabbix_agentd.conf configuration file, open the additional load, is to remove the previous # number

Vim zabbix_agentd.confinclude=/usr/local/zabbix/etc/zabbix_agentd.conf.d/

6) Restart Zabbix_agentd Service

/etc/init.d/zabbix_agentd restart

7) test to see if data can be obtained

# zabbix_get-s 127.0.0.1-p 10050-k mysql.status[com_select]200661

8) Login Zabbix monitoring interface, in the "Configuration" for the host to add templates, complete monitoring.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/92/CD/wKioL1kDCfnzGAvoAAErWK8wki4296.png-wh_500x0-wm_ 3-wmp_4-s_1713505052.png "style=" Float:none; "title=" A.png "alt=" Wkiol1kdcfnzgavoaaerwk8wki4296.png-wh_50 "/>

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M01/92/CE/wKiom1kDCfrxfjaPAAEhZQbl6As617.png-wh_500x0-wm_ 3-wmp_4-s_3101104208.png "style=" Float:none; "title=" B.png "alt=" Wkiom1kdcfrxfjapaaehzqbl6as617.png-wh_50 "/>

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/92/CD/wKioL1kDCfugFXQpAADs2vrL1NI866.png-wh_500x0-wm_ 3-wmp_4-s_3673559191.png "style=" Float:none; "title=" C.png "alt=" Wkiol1kdcfugfxqpaads2vrl1ni866.png-wh_50 "/>

Second, the MySQL monitoring under Windows

To get MySQL status data on Windows, you can run the MySQL command with a VBS script

1) Create a new two script file in the D:\Zabbix\Scripts\ directory with the following contents:

Mysql_ping.vbs

Set OBJFS =createobject ("Scripting.FileSystemObject") Set Objargs = WSCRIPT.ARGUMENTSSTR1 = Getcommandoutput ("D:\SOFT_ Php_package\mysql\bin\mysqladmin-ucactiuser-pcactiuser ping ")//modify the corresponding database path, username and password If Instr (str1," Alive ") > 0ThenWS Cript. Echo 1elsewscript.echo 0End If Function getcommandoutput (thecommand) Dim Objshell, Objcmdexecset Objshell =createobject ( "Wscript.Shell") Set objcmdexec = objshell.exec (thecommand) getcommandoutput =objcmdexec.stdout.readallend Function

Mysql-status.vbs

Set objFS = CreateObject ("Scripting.FileSystemObject") Set Objargs = WSCRIPT.ARGUMENTSSTR1 = Getcommandoutput ("D:\SOFT_ Php_package\mysql\bin\mysqladmin-u cactiuser-pcactiuser extended-status ")//modify the corresponding database path, username and password arg = objargs (0) str2 = S Plit (str1, "|") For i = LBound (str2) to UBound (str2) If Trim (str2 (i)) = Arg then WScript.Echo Trim (STR2 (i+1)) Exit forend IfNext functio n Getcommandoutput (Thecommand) Dim Objshell, Objcmdexecset objshell =createobject ("Wscript.Shell") Set objCmdExec = Objshell.exec (Thecommand) getcommandoutput =objcmdexec.stdout.readall End Function

2) Modify the zabbix_agentd.comf file on Windows to set the key value. Add two sentences below the userparameter;

Userparameter=mysql.status[*], Cscript/nologo d:\Zabbix\Scripts\MySQL_Ext-Status_Script.vbs $
Userparameter=mysql.ping, Cscript/nologo D:\Zabbix\Scripts\MySql_Ping.vbs

3) Restart Zabbix_agentd and add MySQL template to the host to view the items status.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/92/CD/wKioL1kDCfugFXQpAADs2vrL1NI866.png-wh_500x0-wm_ 3-wmp_4-s_3673559191.png "title=" C.png "style=" Float:none; "alt=" Wkiol1kdcfugfxqpaads2vrl1ni866.png-wh_50 "/>

This article is from the "Gen Y Rookie" blog, so be sure to keep this source http://zhangxinqi.blog.51cto.com/9668428/1920584

Zabbix monitoring MySQL (Windows and Linux environments)