【mysql】使用脚本对mysql状态开展监控_mysql

[mysqldump] user=root password=123456 

mysqladmin -r -i 1extended-status  

#!/bin/bash #author [email protected]  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  

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