mysql性能状态监控脚本_【mysql】使用脚本对mysql状态进行监控

最新推荐文章于 2023-03-08 14:07:34 发布

黑忠

最新推荐文章于 2023-03-08 14:07:34 发布

阅读量186

点赞数

文章标签： mysql性能状态监控脚本

本文链接：https://blog.csdn.net/weixin_34659856/article/details/113221044

版权

1、mysqladmin

使用mysqladmin extended-status命令可以获得所有MySQL性能指标，即show global status的输出，不过，因为多数这些指标都是累计值，如果想了解当前的状态，则需要进行一次差值计算，这就是mysqladmin extended-status的一个额外功能，非常实用。

默认的，使用extended-status，看到也是累计值，但是，加上参数-r(--relative)，就可以看到各个指标的差值，配合参数-i(--sleep)就可以指定刷新的频率

如果是5.7可以对mysqladmin进行配置

[mysqldump]

user=root

password=123456

简单的命令

mysqladmin -r -i 1extended-status

监控脚本

#!/bin/bash

#author pingzhao1990@163.com

mysqladmin extended-status -i1|awk 'BEGIN{local_switch=0}

$2 ~ /Queries$/ {q=$4-lq;lq=$4;}

$2 ~ /com_commit$/ {c=$4-lc;lc=$4;}

$2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;}

$2 ~ /Com_select$/ {s=$4-ls;ls=$4;}

$2 ~ /Com_update$/ {u=$4-lu;lu=$4;}

$2 ~ /Com_insert$/ {i=$4-li;li=$4;}

$2 ~ /Com_delete$/ {d=$4-ld;ld=$4;}

$2 ~ /Innodb_rows_read$/ {irr=$4-lirr;lirr=$4;}

$2 ~ /Innodb_rows_deleted$/ {ird=$4-lird;lird=$4;}

$2 ~ /Innodb_rows_inserted$/ {iri=$4-liri;liri=$4;}

$2 ~ /Innodb_rows_updated$/ {iru=$4-liru;liru=$4;}

$2 ~ /Innodb_buffer_pool_read_requests$/ {ibprr=$4-libprr;libprr=$4;}

$2 ~ /Innodb_buffer_pool_reads$/ {ibpr=$4-libpr;libpr=$4;}

$2 ~ /Threads_connected$/ {tc=$4;}

$2 ~ /Threads_running$/ {tr=$4;

if(local_switch==0)

{local_switch=1; count=16}

else {

if(count>15) {

count=0;

print "------------------------------------------------------------------------------------------------------------------------------------ ";

print "Time-----| QPS | Commit Rollback TPS | select insert update delete | read inserted updated deleted | logical physical | Tcon Trun";

print "------------------------------------------------------------------------------------------------------------------------------------ ";

}else{

count+=1;

printf "%s | %-5d| %-6d %-7d %-5d| %-7d %-7d %-5d %-6d| %-7d %-7d %-7d %-7d| %-6d %-9d| %-4d %-2d \n", strftime("%H:%M:%S"),q,c,r,c+r,s,u,i,d,irr,ird,iri,iru,ibprr,ibpr,tc,tr;

}

输出结果如下

------------------------------------------------------------------------------------------------------------------------------------

Time-----| QPS | Commit Rollback TPS | select insert update delete | read inserted updated deleted | logical physical | Tcon Trun

------------------------------------------------------------------------------------------------------------------------------------

11:07:39 | 792 | 0 0 0 | 680 12 8 1 | 337862 0 2 2 | 52243 0 | 20 3

11:07:40 | 792 | 0 0 0 | 665 6 1 1 | 1338 0 1 3 | 2548 0 | 18 3

11:07:41 | 755 | 0 0 0 | 680 6 4 0 | 254448 0 4 6 | 88879 0 | 16 3

11:07:42 | 712 | 0 0 0 | 650 2 1 0 | 62496 0 1 1 | 9750 0 | 15 3

11:07:43 | 780 | 0 0 0 | 700 9 4 0 | 328057 0 4 8 | 151307 0 | 15 4

11:07:44 | 748 | 0 0 0 | 662 3 1 0 | 145816 0 2 2 | 24644 0 | 17 3

11:07:45 | 750 | 0 0 0 | 666 6 3 0 | 271397 0 3 5 | 90546 0 | 19 4

11:07:46 | 772 | 0 0 0 | 694 4 2 0 | 99784 0 2 2 | 16763 0 | 18 4

11:07:47 | 820 | 0 0 0 | 731 10 4 0 | 366336 0 4 8 | 159560 0 | 17 4

11:07:48 | 730 | 0 0 0 | 658 4 3 2 | 108957 0 4 3 | 16179 0 | 15 3

11:07:49 | 816 | 0 0 0 | 698 13 3 0 | 309084 0 4 8 | 149888 0 | 16 3

11:07:50 | 838 | 0 0 0 | 736 5 3 0 | 274541 0 3 3 | 42506 0 | 16 3

11:07:51 | 789 | 0 0 0 | 659 4 2 0 | 207564 0 3 4 | 32753 0 | 16 3

11:07:52 | 798 | 0 0 0 | 705 6 3 0 | 260395 0 5 5 | 91289 0 | 17 3

11:07:52 | 783 | 0 0 0 | 683 5 3 0 | 203953 0 3 5 | 81455 0 | 16 3

11:07:54 | 773 | 0 0 0 | 684 5 2 0 | 202198 0 2 5 | 81554 0 | 17 3

11:07:55 | 782 | 0 0 0 | 668 6 3 0 | 231811 0 3 5 | 87368 0 | 17 5

11:07:56 | 774 | 0 0 0 | 682 6 4 0 | 383932 0 5 6 | 107561 0 | 16 3

11:07:57 | 835 | 0 0 0 | 699 14 7 0 | 468329 0 7 9 | 121511 0 | 11 3

11:07:58 | 878 | 0 0 0 | 722 20 12 0 | 1098071 0 12 21 | 365044 0 | 13 4

11:08:00 | 832 | 0 0 0 | 711 22 8 0 | 719002 0 8 19 | 320272 0 | 11 3

11:08:01 | 768 | 0 0 0 | 690 8 4 0 | 419460 0 4 6 | 116009 0 | 14 5

2、show命令

附上：python2.7的安装

wget http://www.python.org/ftp/python/2.7.8/Python-2.7.8.tar.xz

xz -d Python-2.7.8.tar.xz

tar -xvf Python-2.7.8.tar

cd Python-2.7.8

./configure --prefix=/usr/local

make && make altinstall

# 检查 Python 版本:

python2.7 -V

export PATH="/usr/local/bin:$PATH"

#安装 setuptools

wget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-1.4.2.tar.gz

tar -xvf setuptools-1.4.2.tar.gz

cd setuptools-1.4.2

# 使用 Python 2.7.8 安装 setuptools

python2.7 setup.py install

#安装 PIP

curl https://raw.githubusercontent.com/pypa/pip/master/contrib/get-pip.py | python2.7 -

修复 yum 工具

which yum

#修改 yum中的python 将第一行 #!/usr/bin/python 改为 #!/usr/bin/python2.6

pip install mysql-python

脚本如下

#!/usr/bin/env python

# -*- coding: utf-8 -*-

"""

Permission is hereby granted, free of charge, to any person obtaining

a copy of this software and associated documentation files (the

"Software"), to deal in the Software without restriction, including

without limitation the rights to use, copy, modify, merge, publish,

distribute, sublicense, and/or sell copies of the Software, and to

permit persons to whom the Software is furnished to do so, subject to

the following conditions:

The above copyright notice and this permission notice shall be

included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,

EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF

MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND

NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE

LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION

OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION

WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

MySQL Monitor is a console-based (non-gui) tool for monitoring MySQL server.

MySQL Monitor is inspired by innotop_ and mytop_ .

.. `innotop: http://code.google.com/p/innotop/

.. `mytop: http://jeremy.zawodny.com/mysql/mytop/

mysqlstaus.py shows status by *SHOW GLOBAL STATUS;* statement.

see MySQL :: MySQL 5.7 Reference Manual :: 12.7.5.37 SHOW STATUS Syntax

MySQLhttp://dev.mysql.com/doc/refman/5.7/en/show-status.html

"""

import argparse

import curses

import getpass

import logging

import os

import sys

import threading

import time

from datetime import datetime

import MySQLdb as Database

__title__ = 'mysqlstatus'

__version__ = '0.2.0-DEV'

__author__ = 'Shoma Suzuki'

__license__ = 'MIT'

def get_args_parser():

parser = argparse.ArgumentParser(add_help=False)

parser.add_argument("-h", "--host",

default="localhost",

nargs='?',

type=str,

help="Connect to host.")

parser.add_argument("-p", "--port",

default=3306,

nargs='?',

type=int,

help="Port number to use for connection.")

parser.add_argument("-u", "--user",

default=getpass.getuser(),

nargs='?',

type=str,

help="User for login if not current user.")

parser.add_argument("-P", "--password",

default='',

nargs='?',

type=str,

help="Password to use when connecting to server.")

parser.add_argument("-i", "--interval",

default=1,

nargs='?',

type=int,

help="Interval second of monitoring.")

parser.add_argument("-o", "--outfile",

default=sys.stdout,

nargs='?',

type=argparse.FileType('w'),

help="Output result file. avairable for non-interactive.")

parser.add_argument("-n", "--nonint",

default=False,

action='store_true',

help="Non-interactive.")

parser.add_argument("-m", "--mode",

default='status',

nargs='?',

choices=['status', 'process'],

help="monitoring Mode")

parser.add_argument("--debug",

default=False,

action='store_true',

help="Debug log enable.")

parser.add_argument("--help",

default=False,

action='store_true',

help="show this help message and exit.")

return parser

class QueryThread(threading.Thread):

_stop = False

_update = False

_mysql_variables = None

_mysql_status = None

_mysql_procesesslist = None

def __init__(self, **kwargs):

self.mysql_last_status = None

self._db = kwargs.get('db')

self._cursor = self._db.cursor(Database.cursors.DictCursor)

self._interval = kwargs.get('interval', 1)

self._mode = 'status'

self.lock = threading.Lock()

threading.Thread.__init__(self, name="QueryThread")

self.setDaemon(True)

@property

def mysql_variables(self):

"""SHOW VARIABLES"""

if self._mysql_variables is None:

result = self.query("SHOW VARIABLES")

self._mysql_variables = self.to_dict(result)

logging.debug(self._mysql_variables)

return self._mysql_variables

@property

def mysql_status(self):

return self._mysql_status

@property

def mode(self):

return self._mode

@property

def update(self):

return self._update

@update.setter

def update(self, value):

self._update = value

@mode.setter

def mode(self, value):

if value == 'process':

self._mode = 'process'

else:

self._mode = 'status'

@property

def stop(self):

return self._stop

@stop.setter

def stop(self, value):

self._stop = value

@property

def mysql_procesesslist(self):

return self._mysql_procesesslist

def run(self):

while self._stop == False:

if self._mode == 'process':

self.get_procesesslist()

else:

self.get_status()

time.sleep(self._interval)

self.cleanup_mysql()

def cleanup_mysql(self):

self._cursor.close()

self._db.close()

def query(self, sql):

result = ()

try:

self.lock.acquire()

self._cursor.execute(sql)

result = self._cursor.fetchall()

self.lock.release()

except Exception, err:

logging.exception(err)

return result

def get_status(self):

""" SHOW GLOBAL STATUS """

if self._mysql_status is not None:

self.mysql_last_status = self._mysql_status

result = self.query("SHOW GLOBAL STATUS")

self._mysql_status = self.to_dict(result)

logging.debug(self._mysql_status)

self.get_query_per_second()

self._update = True

return self._mysql_status

def get_procesesslist(self):

"""SHOW FULL PROCESSLIST"""

result = self.query("SHOW FULL PROCESSLIST")

self._mysql_procesesslist = result

self._update = True

logging.debug(result)

return self.mysql_procesesslist()

def get_query_per_second(self):

if self._mysql_status is None:

return 0.0

if self.mysql_last_status is not None:

[current, last] = map(lambda x: float(x),

(self._mysql_status.get('Uptime'),

self.mysql_last_status.get('Uptime')))

elapsed_time = last - current

[current, last] = map(lambda x: float(x),

(self._mysql_status.get('Questions', 0),

self.mysql_last_status.get('Questions', 0)))

inc_query = last - current

else:

[elapsed_time, inc_query] = map(lambda x: float(x),

(self._mysql_status.get('Uptime', 0),

self._mysql_status.get('Questions', 0)))

try:

qps = inc_query / elapsed_time

except:

qps = 0.0

self._mysql_status.update({'QPS': "%0.2f" % qps})

return qps

def to_dict(self, dictset):

return dict(

map(

lambda x: (x.get('Variable_name'), x.get('Value')),

dictset))

class MySQLStatus:

keywords = (

"QPS",

"Aborted_connects",

"Binlog_cache_disk_use",

"Bytes_received",

"Bytes_sent",

"Connections",

"Created_tmp_disk_tables",

"Created_tmp_files",

"Created_tmp_tables",

"Handler_delete",

"Handler_read_first",

"Handler_read_rnd",

"Handler_read_rnd_next",

"Handler_update",

"Handler_write",

"Key_read_requests",

"Key_reads",

"Max_used_connections",

"Open_files",

"Opened_table_definitions",

"Opened_tables",

"Qcache_free_memory",

"Qcache_hits",

"Qcache_queries_in_cache",

"Questions",

"Select_full_join",

"Select_full_range_join",

"Select_range",

"Select_range_check",

"Select_scan",

"Slave_running",

"Slow_queries",

"Sort_merge_passes",

"Sort_scan",

"Table_locks_immediate",

"Table_locks_waited",

"Threads_connected",

"Threads_created",

"Threads_running",

"Uptime",

)

def __init__(self, options):

self.options = options

try:

db = Database.connect(

host=self.options.host,

user=self.options.user,

port=self.options.port,

passwd=self.options.password)

except Exception, err:

logging.exception(err)

print err

sys.exit()

self.qthread = QueryThread(

db=db,

interval=options.interval,

)

self.qthread.mode = options.mode

self.qthread.start()

class IntractiveMode(MySQLStatus):

def run(self):

logging.debug('starting IntractiveMode')

self.window = curses.initscr()

self.window.nodelay(1)

self.set_window_size()

curses.nl()

curses.noecho()

curses.cbreak()

try:

self.mainloop()

except (KeyboardInterrupt, SystemExit):

self.cleanup()

except Exception, err:

logging.exception(err)

self.cleanup()

print err

finally:

self.cleanup()

def mainloop(self):

self.show_header()

while True:

c = self.window.getch()

if c == ord('q'):

break

elif c == ord('p'):

self.qthread.mode = 'process'

elif c == ord('s'):

self.qthread.mode = 'status'

elif c == ord('h') or c == ord('?'):

self.show_help()

elif c == curses.KEY_RESIZE:

self.set_window_size()

if self.qthread.update == True:

self.show_update()

time.sleep(0.1)

def set_window_size(self):

(self.window_max_y, self.window_max_x) = self.window.getmaxyx()

def show_header(self):

variables = self.qthread.mysql_variables

data = {

'hostname': variables.get('hostname'),

'currenttime': datetime.now().strftime("%Y-%m-%d %H:%m:%S"),

'mysql_version': variables.get('version'),

}

data = "%(hostname)s, %(currenttime)s, %(mysql_version)s" % data

self.window.addstr(0, 0, data)

self.window.addstr(1, 0, "-" * 70)

def show_update(self):

self.qthread.update = False

self.window.erase()

self.show_header()

if self.qthread.mode == 'process':

self.show_update_process()

else:

self.show_update_status()

def show_update_status(self):

status = self.qthread.mysql_status

y = 2

for k in self.keywords:

data = "%-25s: %12s" % (k, status.get(k))

if y + 1 < self.window_max_y:

self.window.addstr(y, 0, data)

y = y + 1

if len(self.keywords) + 1 > self.window_max_y:

omits = len(self.keywords) + 1 - self.window_max_y

self.window.addstr(self.window_max_y - 1, 0,

"[%d items were truncated.]" % omits)

def show_update_process(self):

"""

Id, Host, db, User, Time, State, Type(Command), Query(Info)

"""

process = self.qthread.mysql_procesesslist

y = 3

header_format = '%7s, %8s, %8s,%7s,%6s,%6s,%12s,'

header_item = ('Id', 'Host', 'db', 'Time', 'State', 'Type', 'Query')

header = header_format % header_item

data_format = '%(Id)7s, %(Host)8s, %(db)8s,%(Time)7s,%(State)6s,%(Command)6s,%(Info)12s,'

self.window.addstr(2, 0, header)

for item in process:

data = data_format % item

# TODO truncate if variables to display is too long.

if y +1 < self.window_max_y:

self.window.addstr(y, 0, data)

y = y + 1

def cleanup(self):

self.window.erase()

curses.nocbreak()

self.window.keypad(0)

curses.echo()

curses.endwin()

self.qthread.stop = True

while self.qthread.isAlive():

# wait for stop QueryThread

pass

def show_help(self):

"""Help:

s : switch to status mode

p : switch to process mode

h : show this help message

? : alias of help

q : quit

[Press any key to continue]"""

self.window.erase()

self.window.addstr(1, 0, IntractiveMode.show_help.__doc__)

self.window.nodelay(0)

self.window.getch()

self.window.erase()

self.window.nodelay(1)

self.show_header()

class CliMode(MySQLStatus):

def run(self):

logging.debug('starting CliMode')

self.output = self.options.outfile

try:

self.mainloop()

except (KeyboardInterrupt, SystemExit), event:

logging.exception(event)

self.cleanup()

except Exception, err:

logging.exception(err)

self.cleanup()

print err

finally:

self.cleanup()

def mainloop(self):

while True:

if self.qthread.update == True:

self.output_action()

time.sleep(0.1)

def output_action(self):

self.qthread.update = False

if self.qthread.mode == 'process':

self.show_update_process()

else:

self.show_update_status()

self.output.write("\n")

def show_update_status(self):

status = self.qthread.mysql_status

self.output.write(str(status))

def show_update_process(self):

process = self.qthread.mysql_procesesslist

self.output.write(str(process))

def cleanup(self):

self.qthread.stop = True

while self.qthread.isAlive():

pass

if __name__ == '__main__':

parser = get_args_parser()

options = parser.parse_args()

if options.help:

parser.print_help()

parser.exit()

if options.debug:

if not os.path.isdir("logs"):

os.mkdir("logs")

logging.basicConfig(

format='%(asctime)s - (%(threadName)s) - %(message)s in %(funcName)s() at %(filename)s : %(lineno)s',

level=logging.DEBUG,

filename="logs/debug.log",

filemode='w',

)

logging.debug(options)

else:

logging.basicConfig(handler=logging.NullHandler)

if(options.nonint):

monitor = CliMode(options)

else:

monitor = IntractiveMode(options)

monitor.run()

# vim: fenc=utf8 et sw=4 ts=4

监控结果

localhost.localdomain, 2015-12-29 00:12:24, 5.6.25-log

----------------------------------------------------------------------

QPS : 1.00

Aborted_connects : 1

Binlog_cache_disk_use : 0

Bytes_received : 21450

Bytes_sent : 4321757

Connections : 40

Created_tmp_disk_tables : 0

Created_tmp_files : 5

Created_tmp_tables : 464

Handler_delete : 0

Handler_read_first : 6

Handler_read_rnd : 0

Handler_read_rnd_next : 159270

Handler_update : 0

Handler_write : 158786

Key_read_requests : 0

Key_reads : 0

Max_used_connections : 1

Open_files : 24

Opened_table_definitions : 70

Opened_tables : 70

黑忠

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql性能状态监控脚本_【mysql】使用脚本对mysql状态进行监控

1、mysqladmin使用mysqladmin extended-status命令可以获得所有MySQL性能指标，即show global status的输出，不过，因为多数这些指标都是累计值，如果想了解当前的状态，则需要进行一次差值计算，这就是mysqladmin extended-status的一个额外功能，非常实用。默认的，使用extended-status，看到也是累计值，但是，加上参数...
复制链接

扫一扫