[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------------------------------------------------------------------------------------------------------------------------------------ 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.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