CN105095496A - Method for monitoring MYSQL table space through ZABBIX - Google Patents

Abstract

The invention provides a method for monitoring an MYSQL table space through a ZABBIX. The method includes the following steps of transmitting parameters including an MYSQL user name, an MYSQL password, a database name and a table name to a mysqlsize.sh script by establishing a monitoring item with the key value of mysql.schema.size, and transmitting the data to the ZABBIX and writing the data in a database after the script obtains the data. Compared with the prior art, the method has the advantages that the total size of all MYSQL tables on any server in a cluster, the sizes of all tables of a certain database and the size of a certain table in a certain database can be monitored in real time and displayed on a Web interface in real time, and when the size of the table space exceeds a designated size, warning information is produced and a user is reminded. The method further has the advantages of being reasonable in concept, convenient to use, capable of saving labor, high in intelligent degree and the like, and has quite good use value.

Description

A kind of method being monitored MYSQL table space by ZABBIX

Technical field

The present invention relates to computer automation O&M and monitoring field, specifically a kind of method being monitored MYSQL table space by ZABBIX.

Background technology

Along with the sustainable development of information age, IT O&M has become ingredient important in IT service connotation.In the face of the business that becomes increasingly complex, in the face of more and more diversified user's request, the IT application of constantly expansion need more and more reasonably pattern ensure IT service can flexibly convenient, continue guarantee with security and stability, the security factors in this pattern is exactly IT O&M.Huge data center is developed into from several station servers at initial stage, depend merely on the requirement that manually cannot meet in technology, business, management etc., the factor that so standardization, robotization, framework optimization, process optimization etc. reduce IT cost of serving is more and more valued by the people.Multiple stage mysql database server is often had in a cluster, multiple database is had again in each mysql, multiple are had again to show in each database, cause hydraulic performance decline to prevent overabundance of data or write by hard disk completely causing normally running, carrying out monitoring in real time to mysql table space size is very necessary work.Zabbix is the solution of increasing income providing the enterprise-level of distributed system supervision and network monitoring function at a sing on web interface.The present invention is based on zabbix to expand.Web system due to zabbix has a lot of drawback, so adopt Spring, iBatis, JSP, Highcharts to develop a set of new Web system.

Summary of the invention

The object of the invention is to overcome the deficiencies in the prior art, a kind of method being monitored MYSQL table space by ZABBIX is provided.

Technical scheme of the present invention realizes in the following manner, and its method is as follows:

In zabbix system, find the database server that will monitor, create a new monitored item, type is " Zabbix agency ", and key assignments is mysql.schema.size [mysql user name, mysql password, database name, table name];

Be divided three classes from the different of table name according to database name:

(1), when parameter " database name " is " all ", parameter " table name " is invalid, means the size of all tables of whole mysql, such as: mysql.schema.size [root, root, all];

(2), when parameter " database name " is not " all ", total size of all tables under meaning this database when parameter " table name " is " all ", such as: mysql.schema.size [root, root, cmp, all], i.e. total size of all tables of cmp database;

(3), when parameter " database name " and " table name ", under meaning this database time " all ", certain does not open the size shown, such as: mysql.schema.size [root, root, zabbix, history], i.e. the size of the history table of zabbix database;

Amendment zabbix_agentd.conf configuration file, add UserParameter=mysql.schema.size [*], / usr/local/zabbix/plugins/mysqlsize.sh $ 1 $ 2 $ 3 $ 4, wherein mysql.schema.size [*] is the key assignments of the new monitored item created, mysqlsize.sh is the shell script collecting table size, $ 1 $ 2 $ 3 $ 4 correspond to four parameters of monitored item mysql.schema.size respectively: mysql user name, mysql password, database name, table name;

Timeout value in configuration file is set to 10 or larger, because be defaulted as 3 seconds, and the time that mysqlsize.sh performs once is often greater than 3 seconds, and the execution time that Timeout value must be greater than mysqlsize.sh just can normally perform;

Mysqlsize.sh code is as follows:

#!/bin/bash

user=$1

password=$2

dbname=$3

tablename=$4

Total size of all databases under if [$ dbname==" all "] # inquiry mysql

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES;")

echo$size

else

Total size of all tables under if [$ tablename==" all "] # Query Database $ dbname

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES

WHERETABLE_SCHEMA='$dbname';")

echo$size

The size that $ tablename under else## Query Database $ dbname shows

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLESWHERETABLE_SCHEMA='$dbname'ANDTABLE_NAME='$tablename';")

echo$size

fi

fi

After $ 1 $ 2 $ 3 $ 4 is passed to mysqlsize.sh by Zabbix system, this script can log in mysql according to user name ($ 1) and password ($ 2), then inquire about according to database name ($ 3) and table name ($ 4), finally result is returned to zabbix, then write the database of zabbix.

Advantage of the present invention is:

A kind of method by ZABBIX monitoring MYSQL table space of the present invention compared to the prior art, the size that in cluster, on any server, in total size of all tables of mysql, the size of all tables of certain database and certain database, certain is shown can be monitored in real time, and show in real time on Web interface, produce warning information reminding user when table space exceeds and specifies size.And the present invention also has reasonable idea, easy to use, saving manpower, intelligence degree high, thus has good use value.

Embodiment

Below a kind of method by ZABBIX monitoring MYSQL table space of the present invention is described in detail below.

A kind of method being monitored MYSQL table space by ZABBIX of the present invention, method is as follows:

The database server that will monitor is found in zabbix system, create a new monitored item (item), type is " Zabbix acts on behalf of (passive type) ", key assignments (key) is mysql.schema.size [mysql user name, mysql password, database name, table name].

Be divided three classes from the different of table name according to database name:

1. when parameter " database name " is " all ", parameter " table name " is invalid, means the size of all tables of whole mysql, such as: mysql.schema.size [root, root, all];

2. when parameter " database name " is not " all ", total size of all tables under meaning this database when parameter " table name " is " all ", such as: mysql.schema.size [root, root, cmp, all], i.e. total size of all tables of cmp database;

3. when parameter " database name " and " table name ", under meaning this database time " all ", certain does not open the size shown, such as: mysql.schema.size [root, root, zabbix, history], i.e. and the size of the history table of zabbix database.

Amendment zabbix_agentd.conf configuration file, add UserParameter=mysql.schema.size [*], / usr/local/zabbix/plugins/mysqlsize.sh $ 1 $ 2 $ 3 $ 4, wherein mysql.schema.size [*] is the key assignments of the new monitored item created, mysqlsize.sh is the shell script collecting table size, $ 1 $ 2 $ 3 $ 4 correspond to four parameters of monitored item mysql.schema.size respectively: mysql user name, mysql password, database name, table name.Timeout value in configuration file is set to 10 or larger, because be defaulted as 3 seconds, and the time that mysqlsize.sh performs once is often greater than 3 seconds, and the execution time that Timeout value must be greater than mysqlsize.sh just can normally perform.

Mysqlsize.sh code is as follows:

#!/bin/bash

user=$1

password=$2

dbname=$3

tablename=$4

Total size of all databases under if [$ dbname==" all "] # inquiry mysql

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES;")

echo$size

else

Total size of all tables under if [$ tablename==" all "] # Query Database $ dbname

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES

WHERETABLE_SCHEMA='$dbname';")

echo$size

The size that $ tablename under else## Query Database $ dbname shows

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLESWHERETABLE_SCHEMA='$dbname'ANDTABLE_NAME='$tablename';")

echo$size

fi

fi

After $ 1 $ 2 $ 3 $ 4 is passed to mysqlsize.sh by Zabbix system, this script can log in mysql according to user name ($ 1) and password ($ 2), then inquire about according to database name ($ 3) and table name ($ 4), finally result is returned to zabbix, then write the database of zabbix.

A kind of its processing and fabricating of method by ZABBIX monitoring MYSQL table space of the present invention is very simple and convenient, can process to specifications.

Except the technical characteristic described in instructions, be the known technology of those skilled in the art.

Claims (1)

1. monitored a method for MYSQL table space by ZABBIX, it is characterized in that method is as follows:

In zabbix system, find the database server that will monitor, create a new monitored item, type is " Zabbix agency ", and key assignments is mysql.schema.size [mysql user name, mysql password, database name, table name];

Be divided three classes from the different of table name according to database name:

(1), when parameter " database name " is " all ", parameter " table name " is invalid, means the size of all tables of whole mysql, such as: mysql.schema.size [root, root, all];

(2), when parameter " database name " is not " all ", total size of all tables under meaning this database when parameter " table name " is " all ", such as: mysql.schema.size [root, root, cmp, all], i.e. total size of all tables of cmp database;

(3), when parameter " database name " and " table name ", under meaning this database time " all ", certain does not open the size shown, such as: mysql.schema.size [root, root, zabbix, history], i.e. the size of the history table of zabbix database;

Amendment zabbix_agentd.conf configuration file, add UserParameter=mysql.schema.size [*], / usr/local/zabbix/plugins/mysqlsize.sh $ 1 $ 2 $ 3 $ 4, wherein mysql.schema.size [*] is the key assignments of the new monitored item created, mysqlsize.sh is the shell script collecting table size, $ 1 $ 2 $ 3 $ 4 correspond to four parameters of monitored item mysql.schema.size respectively: mysql user name, mysql password, database name, table name;

Timeout value in configuration file is set to 10 or larger, because be defaulted as 3 seconds, and the time that mysqlsize.sh performs once is often greater than 3 seconds, and the execution time that Timeout value must be greater than mysqlsize.sh just can normally perform;

Mysqlsize.sh code is as follows:

#!/bin/bash

user=$1

password=$2

dbname=$3

tablename=$4

Total size of all databases under if [$ dbname==" all "] # inquiry mysql

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES;")

echo$size

else

Total size of all tables under if [$ tablename==" all "] # Query Database $ dbname

then

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLES

WHERETABLE_SCHEMA='$dbname';")

echo$size

The size that $ tablename under else## Query Database $ dbname shows

size=$(mysql-u${user}-p${password}-Dinformation_schema-BN-e

"SELECTSUM(DATA_LENGTH+INDEX_LENGTH)FROMTABLESWHERETABLE_SCHEMA='$dbname'ANDTABLE_NAME='$tablename';")

echo$size

fi

fi

After $ 1 $ 2 $ 3 $ 4 is passed to mysqlsize.sh by Zabbix system, this script can log in mysql according to user name ($ 1) and password ($ 2), then inquire about according to database name ($ 3) and table name ($ 4), finally result is returned to zabbix, then write the database of zabbix.