星空网 > 软件开发 > 数据库

【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=rootpassword=123456

简单的命令

mysqladmin -r -i 1extended-status 

监控脚本

#!/bin/bash#author pingzhao1990@163.commysqladmin 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------------------------------------------------------------------------------------------------------------------------------------ 23:52:06 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:07 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:08 | 14  | 0   0    0  | 8    1    0   0   | 0    0    0    0   | 123   0    | 5  3 23:52:09 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 61   0    | 5  3  :52:10 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:10 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:12 | 21  | 0   0    0  | 2    1    0   0   | 1    0    0    1   | 9    0    | 5  3 23:52:13 | 21  | 0   0    0  | 15   1    0   0   | 0    0    0    0   | 2    0    | 5  3 23:52:14 | 1  | 0   0    0  | 0    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:15 | 4  | 0   0    0  | 1    0    0   0   | 0    0    0    0   | 0    0    | 5  3 23:52:16 | 140 | 0   0    0  | 123   1    0   0   | 115659 0    0    0   | 17473  0    | 5  3 23:52:17 | 54  | 0   0    0  | 30   3    0   0   | 71   0    0    0   | 53   0    | 5  3 23:52:18 | 17  | 0   0    0  | 6    2    1   0   | 76148  0    1    1   | 12262  0    | 6  3 23:52:19 | 25  | 0   0    0  | 8    4    4   0   | 192580 0    4    4   | 26304  0    | 6  3 23:52:20 | 35  | 0   0    0  | 11   6    6   0   | 264798 0    6    6   | 36177  0    | 5  3 23:52:21 | 47  | 0   0    0  | 16   7    7   0   | 382750 0    8    7   | 52543  0    | 7  4 

2、show命令

附上:python2.7的安装

wget http://www.python.org/ftp/python/2.7.8/Python-2.7.8.tar.xzxz -d Python-2.7.8.tar.xztar -xvf Python-2.7.8.tarcd Python-2.7.8./configure --prefix=/usr/localmake && make altinstall# 检查 Python 版本:python2.7 -Vexport PATH="/usr/local/bin:$PATH"#安装 setuptoolswget --no-check-certificate https://pypi.python.org/packages/source/s/setuptools/setuptools-1.4.2.tar.gztar -xvf setuptools-1.4.2.tar.gzcd setuptools-1.4.2# 使用 Python 2.7.8 安装 setuptoolspython2.7 setup.py install#安装 PIPcurl https://raw.githubusercontent.com/pypa/pip/master/contrib/get-pip.py | python2.7 -修复 yum 工具which yum #修改 yum中的python 将第一行 #!/usr/bin/python 改为 #!/usr/bin/python2.6pip install mysql-python

脚本如下

#!/usr/bin/env python# -*- coding: utf-8 -*-"""Copyright (c) Shoma SuzukiPermission is hereby granted, free of charge, to any person obtaininga copy of this software and associated documentation files (the"Software"), to deal in the Software without restriction, includingwithout limitation the rights to use, copy, modify, merge, publish,distribute, sublicense, and/or sell copies of the Software, and topermit persons to whom the Software is furnished to do so, subject tothe following conditions:The above copyright notice and this permission notice shall beincluded 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 OFMERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE ANDNONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BELIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTIONOF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTIONWITH 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 SyntaxMySQLhttp://dev.mysql.com/doc/refman/5.7/en/show-status.html"""import argparseimport cursesimport getpassimport loggingimport osimport sysimport threadingimport timefrom datetime import datetimeimport 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 parserclass 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():      passif __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.00Aborted_connects     :      1Binlog_cache_disk_use  :      0Bytes_received      :    21450Bytes_sent        :   4321757Connections       :      40Created_tmp_disk_tables :      0Created_tmp_files    :      5Created_tmp_tables    :     464Handler_delete      :      0Handler_read_first    :      6Handler_read_rnd     :      0Handler_read_rnd_next  :    159270Handler_update      :      0Handler_write      :    158786Key_read_requests    :      0Key_reads        :      0Max_used_connections   :      1Open_files        :      24Opened_table_definitions :      70Opened_tables      :      70Opened_tables      :      70

  




原标题:【mysql】使用脚本对mysql状态进行监控

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

空运行李托运:https://www.goluckyvip.com/tag/102833.html
义乌空运:https://www.goluckyvip.com/tag/102834.html
飞机托运包:https://www.goluckyvip.com/tag/102835.html
空运出口物流:https://www.goluckyvip.com/tag/102836.html
泰州空运:https://www.goluckyvip.com/tag/102837.html
出口空运:https://www.goluckyvip.com/tag/102838.html
出境旅游预订须知:https://www.vstour.cn/a/365175.html
九寨沟景区地图(详细指南和攻略):https://www.vstour.cn/a/365176.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流