python脚本监控mysql链接

本脚本适合监控mysql会话，并通过会话取到相关服务
1.需要先建立好监控机器和服务机器的互信

ssh-keygen -t rsa

在~/.ssh目录下会生成秘钥文件

authorized_keys  id_rsa  id_rsa.pub  known_hosts

其中id_rsa为私钥文件，id_rsa.pub为公钥文件，将id_rsa.pub文件中内容拷贝到需要免密登录的目标机器的authorized_keys文件中即可

2.编写脚本

import pymysql
import paramiko
import datetime
from dbutils.pooled_db import PooledDB
import time


def log_w(msg):
    f = open('/root/script/pate.log', 'a')
    f.write(msg+'\n')
    f.close()

def get_data():
    connect = pymysql.connect(host='', user='',password='', port=3306,connect_timeout=25)
    cursor = connect.cursor()
    sql = "show  processlist"
    cursor.execute(sql)
    data = cursor.fetchall()
    return data

def get_conn():
    pool = PooledDB(pymysql,
                    maxcached=4,  # 连接池中最大空闲连接数
                    maxshared=8,  # 允许的最大共享连接数
                    maxconnections=4,
                    host='',
                    user='',
                    port=3306,
                    passwd='',
                    db='mysql',
                    charset='utf8mb4',
                    use_unicode=True)
    return pool

if __name__ == "__main__":
    pool = get_conn()
    while True:
        t = str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
        log_w(t)
        print(t)
        conn = pool.connection()
        cur = conn.cursor()
        sql = "show  processlist"
        cur.execute(sql)
        data = cur.fetchall()
        log_w('data is get')
        for i in data:
            if i[1] not in ['dba_root']:
                log_w(i[2])
                hostname = i[2].split(':')[0]
                username = 'root'
                pid = i[2].split(':')[1]
                private_key = paramiko.RSAKey.from_private_key_file('/root/.ssh/id_rsa')
                ssh = paramiko.SSHClient()
                ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
                ssh.connect(hostname=hostname, port=22, username="root", pkey=private_key)
                stdin, stdout, stderr = ssh.exec_command("lsof -i:%s | awk '{print $2}' | grep -v PID" % (pid))
                result = stdout.read().decode()
                if result:
                    log_w(hostname)
                    for r in result.split('\n'):
                        stdin_in, stdout_in, stderr_in = ssh.exec_command("ps -ef | grep %s" % (r,))
                        re_in =stdout_in.read().decode()
                        log_w(re_in)
                        #print(re_in)
                ssh.close()
        cur.close()
        conn.close()
        log_w('end-------------------')
        time.sleep(180)

/root/script/pate.log 为监控日志路径