CACTI: Use a template to monitor mysql on a local server or another server

CACTI: Use a template to monitor mysql on a local server or another server

Mysql templates, online are using this address http://mysql-cacti-templates.googlecode.com has not been accessed

File currently downloaded: percona-cacti-templates-1.1.4-1.noarch.rpm

Local server monitoring local mysql:

Go to the cacti directory

cd /var/www/html

Move the compressed file to the current directory (put it there ):

mv percona-cacti-templates-1.1.4-1.noarch.rpm .

Decompress the file:

rpm2cpio percona-cacti-templates-1.1.4-1.noarch.rpm | cpio -div

The following directories and files will be produced in the current directory:

./usr/share/cacti/resource/percona./usr/share/cacti/resource/percona/bin./usr/share/cacti/resource/percona/bin/pmp-cacti-extract-template./usr/share/cacti/resource/percona/bin/pmp-cacti-graph-defs./usr/share/cacti/resource/percona/bin/pmp-cacti-make-hashes./usr/share/cacti/resource/percona/bin/pmp-cacti-template./usr/share/cacti/resource/percona/definitions./usr/share/cacti/resource/percona/definitions/apache.def./usr/share/cacti/resource/percona/definitions/galera.def./usr/share/cacti/resource/percona/definitions/gnu_linux.def./usr/share/cacti/resource/percona/definitions/jmx.def./usr/share/cacti/resource/percona/definitions/memcached.def./usr/share/cacti/resource/percona/definitions/mongodb.def./usr/share/cacti/resource/percona/definitions/mysql.def./usr/share/cacti/resource/percona/definitions/nginx.def./usr/share/cacti/resource/percona/definitions/openvz.def./usr/share/cacti/resource/percona/definitions/rds.def./usr/share/cacti/resource/percona/definitions/redis.def./usr/share/cacti/resource/percona/misc./usr/share/cacti/resource/percona/misc/jmx-monitor.xml./usr/share/cacti/resource/percona/templates./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_apache_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_galera_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_gnu_linux_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_jmx_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_memcached_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_mongodb_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_nginx_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_openvz_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_rds_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/resource/percona/templates/cacti_host_template_percona_redis_server_ht_0.8.6i-sver1.1.4.xml./usr/share/cacti/scripts/ss_get_by_ssh.php./usr/share/cacti/scripts/ss_get_mysql_stats.php./usr/share/cacti/scripts/ss_get_rds_stats.py

Now we only need two mysql files:

ss_get_mysql_stats.phpcacti_host_template_percona_mysql_server_ht_0.8.6i-sver1.1.4.xml

Copy ss_get_mysql_stats.php to the cacti directory:

cp ./usr/share/cacti/scripts/ss_get_mysql_stats.php /var/www/html/cacti/scripts/

Edit the file to confirm that the user name and password are correct:

vi /var/www/html/cacti/scripts/ss_get_mysql_stats.php

$mysql_user = 'cacti';$mysql_pass = 'cacti';

Import template: Open the cactiy page --> Console --> Import Templates --> browse and add

 

Import result:

 

Import ResultsCacti has imported the following items:CDEF[success] Percona Turn Into Bits CDEF [new][success] Percona Negate CDEF [new]GPRINT Preset[success] Percona MySQL Server Checksum 523e4d8178a000df8df3a9825c4e7e1e [new][success] Percona MySQL Server Version t1.1.4:s1.1.4 [new][success] Percona Normal [new]Data Input Method[success] Percona Get MySQL Stats/MyISAM Indexes IM [new][success] Percona Get MySQL Stats/MyISAM Key Cache IM [new][success] Percona Get MySQL Stats/InnoDB Buffer Pool IM [new][success] Percona Get MySQL Stats/InnoDB I/O IM [new][success] Percona Get MySQL Stats/InnoDB Insert Buffer IM [new][success] Percona Get MySQL Stats/InnoDB Insert Buffer Usage IM [new][success] Percona Get MySQL Stats/InnoDB Semaphores IM [new][success] Percona Get MySQL Stats/InnoDB Row Operations IM [new][success] Percona Get MySQL Stats/MySQL Table Locks IM [new][success] Percona Get MySQL Stats/MySQL Connections IM [new][success] Percona Get MySQL Stats/MySQL Replication IM [new][success] Percona Get MySQL Stats/MySQL Query Cache IM [new][success] Percona Get MySQL Stats/MySQL Query Cache Memory IM [new][success] Percona Get MySQL Stats/MySQL Command Counters IM [new][success] Percona Get MySQL Stats/MySQL Select Types IM [new][success] Percona Get MySQL Stats/MySQL Sorts IM [new][success] Percona Get MySQL Stats/MySQL Temporary Objects IM [new][success] Percona Get MySQL Stats/MySQL Network Traffic IM [new][success] Percona Get MySQL Stats/InnoDB Buffer Pool Activity IM [new][success] Percona Get MySQL Stats/InnoDB I/O Pending IM [new][success] Percona Get MySQL Stats/InnoDB Log IM [new][success] Percona Get MySQL Stats/MySQL Binary/Relay Logs IM [new][success] Percona Get MySQL Stats/InnoDB Transactions IM [new][success] Percona Get MySQL Stats/InnoDB Transactions Active/Locked IM [new][success] Percona Get MySQL Stats/MySQL Files and Tables IM [new][success] Percona Get MySQL Stats/MySQL Threads IM [new][success] Percona Get MySQL Stats/InnoDB Memory Allocation IM [new][success] Percona Get MySQL Stats/InnoDB Adaptive Hash Index IM [new][success] Percona Get MySQL Stats/InnoDB Internal Hash Memory Usage IM [new][success] Percona Get MySQL Stats/InnoDB Tables In Use IM [new][success] Percona Get MySQL Stats/InnoDB Current Lock Waits IM [new][success] Percona Get MySQL Stats/InnoDB Lock Structures IM [new][success] Percona Get MySQL Stats/InnoDB Checkpoint Age IM [new][success] Percona Get MySQL Stats/InnoDB Row Lock Time IM [new][success] Percona Get MySQL Stats/InnoDB Row Lock Waits IM [new][success] Percona Get MySQL Stats/InnoDB Semaphore Waits IM [new][success] Percona Get MySQL Stats/InnoDB Semaphore Wait Time IM [new][success] Percona Get MySQL Stats/MySQL Processlist IM [new][success] Percona Get MySQL Stats/MySQL Transaction Handler IM [new][success] Percona Get MySQL Stats/MySQL Handlers IM [new][success] Percona Get MySQL Stats/MySQL Query Time Histogram (Count) IM [new][success] Percona Get MySQL Stats/MySQL Query Response Time (Microseconds) IM [new][success] Percona Get MySQL Stats/InnoDB Buffer Pool Efficiency IM [new]Data Template[success] Percona MyISAM Indexes DT [new][success] Percona MyISAM Key Cache DT [new][success] Percona InnoDB Buffer Pool DT [new][success] Percona InnoDB I/O DT [new][success] Percona InnoDB Insert Buffer DT [new][success] Percona InnoDB Insert Buffer Usage DT [new][success] Percona InnoDB Semaphores DT [new][success] Percona InnoDB Row Operations DT [new][success] Percona MySQL Table Locks DT [new][success] Percona MySQL Connections DT [new][success] Percona MySQL Replication DT [new][success] Percona MySQL Query Cache DT [new][success] Percona MySQL Query Cache Memory DT [new][success] Percona MySQL Command Counters DT [new][success] Percona MySQL Select Types DT [new][success] Percona MySQL Sorts DT [new][success] Percona MySQL Temporary Objects DT [new][success] Percona MySQL Network Traffic DT [new][success] Percona InnoDB Buffer Pool Activity DT [new][success] Percona InnoDB I/O Pending DT [new][success] Percona InnoDB Log DT [new][success] Percona MySQL Binary/Relay Logs DT [new][success] Percona InnoDB Transactions DT [new][success] Percona InnoDB Transactions Active/Locked DT [new][success] Percona MySQL Files and Tables DT [new][success] Percona MySQL Threads DT [new][success] Percona InnoDB Memory Allocation DT [new][success] Percona InnoDB Adaptive Hash Index DT [new][success] Percona InnoDB Internal Hash Memory Usage DT [new][success] Percona InnoDB Tables In Use DT [new][success] Percona InnoDB Current Lock Waits DT [new][success] Percona InnoDB Lock Structures DT [new][success] Percona InnoDB Checkpoint Age DT [new][success] Percona InnoDB Row Lock Time DT [new][success] Percona InnoDB Row Lock Waits DT [new][success] Percona InnoDB Semaphore Waits DT [new][success] Percona InnoDB Semaphore Wait Time DT [new][success] Percona MySQL Processlist DT [new][success] Percona MySQL Transaction Handler DT [new][success] Percona MySQL Handlers DT [new][success] Percona MySQL Query Time Histogram (Count) DT [new][success] Percona MySQL Query Response Time (Microseconds) DT [new][success] Percona InnoDB Buffer Pool Efficiency DT [new]Graph Template[success] Percona MyISAM Indexes GT [new][success] Percona MyISAM Key Cache GT [new][success] Percona InnoDB Buffer Pool GT [new][success] Percona InnoDB I/O GT [new][success] Percona InnoDB Insert Buffer GT [new][success] Percona InnoDB Insert Buffer Usage GT [new][success] Percona InnoDB Semaphores GT [new][success] Percona InnoDB Row Operations GT [new][success] Percona MySQL Table Locks GT [new][success] Percona MySQL Connections GT [new][success] Percona MySQL Replication GT [new][success] Percona MySQL Query Cache GT [new][success] Percona MySQL Query Cache Memory GT [new][success] Percona MySQL Command Counters GT [new][success] Percona MySQL Select Types GT [new][success] Percona MySQL Sorts GT [new][success] Percona MySQL Temporary Objects GT [new][success] Percona MySQL Network Traffic GT [new][success] Percona InnoDB Buffer Pool Activity GT [new][success] Percona InnoDB I/O Pending GT [new][success] Percona InnoDB Log GT [new][success] Percona MySQL Binary/Relay Logs GT [new][success] Percona InnoDB Transactions GT [new][success] Percona InnoDB Transactions Active/Locked GT [new][success] Percona MySQL Files and Tables GT [new][success] Percona MySQL Threads GT [new][success] Percona InnoDB Memory Allocation GT [new][success] Percona InnoDB Adaptive Hash Index GT [new][success] Percona InnoDB Internal Hash Memory Usage GT [new][success] Percona InnoDB Tables In Use GT [new][success] Percona InnoDB Current Lock Waits GT [new][success] Percona InnoDB Lock Structures GT [new][success] Percona InnoDB Checkpoint Age GT [new][success] Percona InnoDB Row Lock Time GT [new][success] Percona InnoDB Row Lock Waits GT [new][success] Percona InnoDB Semaphore Waits GT [new][success] Percona InnoDB Semaphore Wait Time GT [new][success] Percona MySQL Processlist GT [new][success] Percona MySQL Transaction Handler GT [new][success] Percona MySQL Handlers GT [new][success] Percona MySQL Query Time Histogram (Count) GT [new][success] Percona MySQL Query Response Time (Microseconds) GT [new][success] Percona InnoDB Buffer Pool Efficiency GT [new]Host Template[success] Percona MySQL Server HT [new]

 

Open Devices --> select host (create if no host exists) --> drop down to the bottom

Select an image template and add it to the Host:

Three items are added for the current test:

Percona InnoDB Log GT
Percona MySQL Connections GT
Percona MySQL Threads GT

Go to the top of the page, click "Create Graphs for this Host", select the three image templates you just created.

Create a directory tree and add images to the tree:

Click "Graph Trees" and select "Default Tree". --> Add subtree (Mysql_local)

Click "Add" next to the tree Mysql_local, and set the subtree type to "Graph". Put the three monitoring images of mysql in this directory structure and display them:

Several minutes later. Image appears!

Cross-server monitoring of mysql in centos:

Cacti monitoring server: 192.168.1.151

Monitored client: 192.168.1.152

Ping each other to confirm that the network is smooth.

Install snmp in client 152:

 

yum -y install net-snmp net-snmp-utils

 

# Modify the following information in the configuration file: 41st rows, 62nd rows, and 85th rows

 

vi /etc/snmp/snmpd.conf

# Com2sec notConfigUser default publiccom2sec notConfigUser 192.168.1.151 public # access notConfigGroup "" any noauth exact systemview none noneaccess notConfigGroup "" any noauth exact all none # Set # view all supported ded. 1. comment out the comment before '80. 1 80

Finally start or restart the client snmp Service:

 

 

service snmpd restart

 

Add a cacti user to mysql in client 152 to allow 151 access:

 

grant process,super on *.* to 'cacti'@'192.168.1.151' identified by 'cacti';flush privileges;

Run the command in server 151 to test whether snmp 152 is enabled successfully:

 

snmpwalk -c public -v 2c 192.168.1.152

 

For Main firewall problems, add the client snmp service to self-starting: ntsysv

Click: Device --> add (upper right corner)

The other steps and methods are the same as those used on the local machine.

If no image is available, check whether the cacti directory has the following permissions:

 

ll /var/www/html/cacti/rra

Soon the image data margin appeared: