【mysql】使用脚本对mysql状态进行监控
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 -*-
"""
Copyright (c) Shoma Suzuki
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'
__copyright__ = 'Copyright 2012 Shoma Suzuki'
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",
"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 Opened_tables : 70
- 作者：踏雪无痕
- 出处：http://www.cnblogs.com/chenpingzhao/
- 本文版权归作者和博客园共有，如需转载，请联系 pingzhao1990#163.com
