4.6 使用Python监控MySQL

在本机安装mysql和MySQLdb库

sudo apt-get install mysql-server
sudo apt install mysql-client
sudo apt install libmysqlclient-dev

pip3 install mysqlclient

通过Python的MySQLdb库连接MySQL，从MySQL数据库中获取一系列运行数据，并输出。

#!/usr/bin/python
import sys
import MySQLdb

host = '127.0.0.1'
user = 'root'
password = 'internet'
db = 'mysql'


def getConn(host, user, passwd, db):
    try:
        conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
        return conn
    except:
        print ("conn error")
        sys.exit(1)


def closeConn(conn):
    conn.close()


def getValue(conn, query):
    cursor = conn.cursor()
    getNum=cursor.execute(query)
    if getNum>0:
        result = cursor.fetchone()
    else:
        result=['0']
    return int(result[1])


Questions = "show global status like 'Questions'"
Uptime = "show global status like 'Uptime'"


if __name__ == "__main__":
    conn = getConn(host, user, password, db)
    Questions = getValue(conn, Questions)
    Uptime = getValue(conn, Uptime)

    print ("----QPS----")
    # QPS = Questions / Seconds
    QPS = str(round(Questions / Uptime, 5))
    print('QPS:'+QPS)
    closeConn(conn)

运行结果为：（每秒查询次数的结果根据Mysql运行情况而定）

root@liu-ubuntu:~# ./18-mysql.py 
----QPS----
QPS:1.0