Python实现监控mysql相关指标（show global status 与 服务器CPU等）

import multiprocessing
import pymysql
import xlrd
import time
import datetime
import paramiko
import os
import glob


def select_sql_1(cursor,sql):
    #执行查询sql，返回第一行数据
    cursor.execute(sql)
    result = cursor.fetchone()
    return result[1]
def select_sql_all(cursor,sql):
    # 执行查询sql，返回所有数据
    cursor.execute(sql)
    result = cursor.fetchall()
    return result
def count_data(num_1,num_2):
    #进行百分比计算
    result = int(num_1)/int(num_2)*100
    return result
def del_files(path):
    #删除文件夹下的文件
    fileNames = glob.glob(path + r'/*')
    for fileName in fileNames:
        try:
            os.remove(fileName)
            print("remove file:%s" % fileNames)
        except:
            pass
def print_out(i):
    #保存数据库主机连接信息的Excel表
    data = xlrd.open_workbook(r"d:\data.xls")
    table = data.sheets()[0]
    # 打开一个文件用于追加。如果该文件已存在，文件指针将会放在文件的结尾;如果该文件不存在，创建新文件进行写入
    file = open(r"d:\测试文件\mysql\{0}.txt".format(table.cell_value(i, 4)), 'a', encoding='utf-8')
    file_vmstat = open(r"d:\测试文件\mysql\{0}_vmstat.txt".format(table.cell_value(i, 4)), 'a',
                       encoding='utf-8')
    db = pymysql.connect(
        host=table.cell_value(i, 0),
        port=int(table.cell_value(i, 1)),
        user=table.cell_value(i, 2),
        password=table.cell_value(i, 3),
        database=table.cell_value(i, 4),
        charset='utf8'
    )
    cursor = db.cursor()
    #连接到服务器主机，执行Linux命令进行数据监控
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(table.cell_value(i, 0), 22, 'name', 'password', timeout=5)
    VARIABLES = dict(select_sql_all(cursor, "show VARIABLES like \"%%\";"))
    stdin, stdout, stderr = ssh.exec_command("vmstat 1 3")  # 执行命令
    file_vmstat.write(str(datetime.datetime.now()) + "\n")  #写入文件
    for j in range(5):
        cmd_result = stdout.readline(), stderr.read()  # 读取命令结果
        file_vmstat.write(str(cmd_result) + "\n")

    global_status = dict(select_sql_all(cursor, "show global status;"))
    file.write(str(datetime.datetime.now()) + "\n" + str(global_status) + "\n")
    file.write(str(datetime.datetime.now()) + "\n")
    # key_buffer_size
    file.write("读取索引的缓存未命中率为%.2f%%" % count_data(global_status.get("Key_reads"),global_status.get("Key_read_requests")) + "\n")
    # # 单位时间内的Key_reads
    file.write(
        "单位时间从磁盘读取索引的请求次数为%.2f%%" % count_data(global_status.get("Key_reads"), global_status.get("Uptime")) + "\n")
    # # 最大使用的连接数除以设置的最大连接数，理想值 ≈ 85%
    file.write("连接数占用率为%.2f%%" % count_data(global_status.get("Max_used_connections"),VARIABLES.get("max_connections")) + "\n")
    # # blocks数使用率（理想值 ≈ 80%）
    # file.write("blocks数使用率为%.2f%%" % count_data(global_status.get("Key_blocks_used"), global_status.get("Key_blocks_used") + global_status.get("Key_blocks_unused"))+ "\n")
    # # 磁盘上创建临时表占比（理想值<= 25%）
    file.write("磁盘上创建临时表占比为%.2f%%" % count_data(global_status.get("Created_tmp_disk_tables"),global_status.get("Created_tmp_tables")) + "\n")
    # # table_open_cache理想值 (<= 95%)
    file.write("表打开率为%.2f%%" % count_data(global_status.get("Open_tables"), VARIABLES.get("table_open_cache")) + "\n")
    # # 文件打开数(open_files)（理想值<= 75％）（服务器有个配置最大文件打开数：/etc/security/limits.conf）
    file.write("文件打开数为%.2f%%" % count_data(global_status.get("Open_files"), VARIABLES.get("open_files_limit")) + "\n")
    # 表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好
    file.write("表扫描率为%.2f%%" % count_data(global_status.get("Handler_read_rnd_next"),
                                          global_status.get("Com_select")) + "\n")
    # Innodb缓冲池(命中率不低于95%)
    file.write("缓冲池的读命中率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_read_requests"),
                                              global_status.get("Innodb_buffer_pool_reads") + global_status.get(
                                                  "Innodb_buffer_pool_read_requests")) + "\n")
    # 缓冲池的空闲率
    file.write("表扫描率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_pages_free"),
                                          global_status.get("Innodb_buffer_pool_pages_total")) + "\n")
    # 缓冲池的脏块的百分率
    file.write("表扫描率为%.2f%%" % count_data(global_status.get("Innodb_buffer_pool_pages_dirty"),
                                          global_status.get("Innodb_buffer_pool_pages_total")) + "\n")
    cursor.close()
    db.close()
    file.close()
    ssh.close()
    file_vmstat.close()
if __name__ == '__main__':
    del_files(r"d:\测试文件\mysql")
    num = 0
    #执行监控间隔时间，执行总次数，mysql服务器配置Excel行数
    sleep_time,frequency,nrows = 20,2,8
    while True:
        num = num + 1
        #进程池，使用多进程，加快执行速度
        pool = multiprocessing.Pool(processes=3)
        for i in range(1,nrows):
            pool.apply_async(print_out(i))
        time.sleep(sleep_time)
        pool.close()
        pool.join()
        if num >= frequency:
            break