程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【mysql】使用腳本對mysql狀態進行監控,腳本mysql狀態監控

【mysql】使用腳本對mysql狀態進行監控,腳本mysql狀態監控

編輯:MySQL綜合教程

【mysql】使用腳本對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 [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  

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

  

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved