Shulou(Shulou.com)06/01 Report--

The following content mainly brings you how zabbix monitors MySQL. Unlike books, the knowledge mentioned is summed up by professional and technical personnel in the process of contact with users. It has a certain value of experience sharing and hopes to bring help to the majority of readers.

1. Monitoring MySQL in linux environment

Zabbix Server comes with a MySQL plug-in to monitor the template of the mysql database. You only need to configure the agent client, and then add the template to the host on the web side.

Monitoring projects:

Com_update: the number of updates performed by mysql

Com_select: the number of queries executed by mysql

Com_insert: the number of inserts performed by mysql

Com_delete: the number of deletions performed

Com_rollback: the number of operations that performed the rollback

Bytes_received: number of bytes accepted

Bytes_sent: number of bytes sent

Slow_queries: the number of slow query statements

Com_commit: the number of confirmed things

Com_begin: the number of things that start

Uptime: the number of seconds that the CVM has been started

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

Download address of monitoring template: http://www.zabbix.org/wiki/Zabbix_Templates#External_template_resources

Script download address: https://github.com/itnihao/zabbix-book

1) create a user name and password for the zabbix link MySQL and grant permissions.

Mysql > grant all on *. * to zabbix@'localhost'identified by "123456"; mysql > flush privileges

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

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

Note:

If the database grant authorization is aimed at localhost, there is no need to add the host parameter in this .my.cnf [as configured above]

However, if grant is authorized for a native ip (such as 192.168.1.25), the host parameter should be added to the .my.cnf file to specify: host=192.168.1.25.

3) configure the key file of MySQL

This can be copied from the unzipped package when zabbix is installed:

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 with environment variables, the MySQL command may not be found. You can use MySQL full path.

When you see the path settings like HOME=/var/lib/zabbix, replace all the paths with / usr/local/zabbix/etc/, the same directory path as the .my.cnf file 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='$1';" | 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 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" $3 "in both |") echo "data_length+index_length";; data | index) echo "$3_length";; free) echo "data_free" Esac) from information_schema.tables$ ([["$1" = "all" |! "$1]] | | echo" where table_schema=\ "$1\") $([["$2" = "all" | |! "$2"]] | | echo "and table_name=\" $2\ ") | | HOME=/usr/local/zabbix/etc/ mysql-named UserParameterials mysql.pingMagol Homechape _ UserParameterless | grep-c aliveUserParameter=mysql.version,mysql-V%s#/var/lib/zabbix#/usr/local/zabbix/etc/# # replace with the command

You can also use this shell script to monitor, including master-slave monitoring:

# 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}'

5) modify the zabbix_agentd.conf configuration file and enable additional loading, that is, remove the previous #

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

6) restart the zabbix_agentd service

/ etc/init.d/zabbix_agentd restart

7) Test to see if the data can be obtained

# zabbix_get-s 127.0.0.1-p 10050-k mysql.status [Com _ select] 200661

8) Log in to the zabbix monitoring interface, add a template for the host in "configuration", and complete the monitoring.

2. MySQL monitoring under windows

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

1) create two new script files under the d:\ Zabbix\ Scripts\ directory as follows:

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") > 0ThenWScript.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 = Split (str1, "|") For I = LBound (str2) to UBound (str2) If Trim (str2 (I)) = Arg Then WScript.Echo TRIM (str2 (iTun1) Exit ForEnd Ifnext Function 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 and set the key value. Add two sentences under UserParameter

UserParameter=mysql.status [*], cscript/nologo d:\ Zabbix\ Scripts\ MySQL_Ext-Status_Script.vbs $1

UserParameter=mysql.ping, cscript / nologo d:\ Zabbix\ Scripts\ MySql_Ping.vbs

3) restart zabbix_agentd, add a MySQL template to the host, and check the items status.

For the above about how zabbix is used to monitor MySQL, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.