Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to monitor innodb status indicators, the article is very detailed, has a certain reference value, interested friends must read it!

Enter the ip of the monitored machine to obtain innodb-related status metrics in real time, including:

-|-MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--

-QPS--- | select insert update delete | read inserted updated deleted | logical physical

# need to compile the script to write the account password

# you can create a monitoring account with a uniform password for all the DB you maintain, so you can write it to death in the script and use it directly.

] $cat innodb_status.sh

#! / bin/bash

# Parameter variables

Host=$1

Port=$2

Username= user

Passwd= password

# echo "sh $0$ host $username $passwd $port"

# fountion script description, help

Help_info () {

Echo "NAME"

Echo "$0"

Echo "SYNOPSIS"

Echo "$0 is a shell script and monitor innodb status,eg:qps,tps."

Echo "DESCRIPTION"

Echo "option like mysql client-h host-u username-p password-P port"

Echo "Usage:"

Echo "sh $0 ipaddr username password port"

Echo "E.G sh $010.111.7.31 3306 yujx yujxpw"

Echo

}

# function print innodb related metrics

Server_id () {

Mysql-h $host-P$port-u$username-p$passwd-e "show variables like 'server_id'" | grep-iv value

}

Innodb_status () {

Mysqladmin-h $host-P$port-u$username-p$passwd-r-I 1 ext |\

Awk-F "|"\

"BEGIN {count=0;}"\

'{if ($2 ~ / Variable_name/ & & ((+ + count)% 20 = = 1)) {\

Print "- |-|-- MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--";\

Print "--Time--- |-- QPS--- | select insert update delete | read inserted updated deleted | logical physical";\

}\

Else if ($2 ~ / Queries/) {queries=$3;}\

Else if ($2 ~ / Com_select /) {com_select=$3;}\

Else if ($2 ~ / Com_insert /) {com_insert=$3;}\

Else if ($2 ~ / Com_update /) {com_update=$3;}\

Else if ($2 ~ / Com_delete /) {com_delete=$3;}\

Else if ($2 ~ / Innodb_rows_read/) {innodb_rows_read=$3;}\

Else if ($2 ~ / Innodb_rows_deleted/) {innodb_rows_deleted=$3;}\

Else if ($2 ~ / Innodb_rows_inserted/) {innodb_rows_inserted=$3;}\

Else if ($2 ~ / Innodb_rows_updated/) {innodb_rows_updated=$3;}\

Else if ($2 ~ / Innodb_buffer_pool_read_requests/) {innodb_lor=$3;}\

Else if ($2 ~ / Innodb_buffer_pool_reads/) {innodb_phr=$3;}\

Else if ($2 ~ / Uptime / & & count > = 2) {\

Printf ("% s |% 9d", strftime ("% H:%M:%S"), queries);\

Printf ("|% 6d% 6d% 6d% 6d", com_select,com_insert,com_update,com_delete);\

Printf ("% 6d% 8d% 7d% 7d", innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\

Printf ("| d\ n", innodb_lor,innodb_phr);\

}'

}

# default port 3306

If [! $port]; then

Port=3306

Fi

# echo $#, $1

# execute script

If [$#-le 0] | | ["$1" x = "- h" x] | | ["$1" x = "--help" x]

Then

Help_info

Else

Echo "."

Server_id

Echo "."

Innodb_status

Fi

# run

] $. / innodb_status.sh ip address

.

Warning: Using a password on the command line interface can be insecure.

Server_id 111111

.

Warning: Using a password on the command line interface can be insecure.

-|-|-- MySQL Command Status-- |-Innodb row operation-|-- Buffer Pool Read--

-Time--- |-QPS--- | select insert update delete | read inserted updated deleted | logical physical

09:37:24 | 10526 | 318 66 5021 0 | 18124 66 10061 0 | 537009 87

09:37:25 | 10596 | 298 96 5048 0 | 21170 96 10118 0 | 535858 79

09:37:26 | 10300 | 238 65 4947 0 | 21968 65 9914 0 | 525109 91

The above is all the contents of the article "how to monitor innodb status indicators". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.