监控MySQL主从状态是否正常Python3脚本

最新推荐文章于 2024-07-25 14:24:03 发布

の正在缓存99%

最新推荐文章于 2024-07-25 14:24:03 发布

阅读量77

点赞数 1

分类专栏： # MySQL 文章标签： mysql 数据库 python

本文链接：https://blog.csdn.net/qq_24948581/article/details/139144576

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

监控MySQL从库同步状态，写入监控数据库[PostgreSQL]

#!/bin/python3
# -*- coding: UTF-8 -*-
import pymysql  
import psycopg2  
import time 

def insert_pg_monitordb(ip,app_name,status):
    ip=ip
    app_name=app_name
    status=status
    # 连接到PostgreSQL数据库  
    conn = psycopg2.connect(  
        database="jkuser_db",  
        user="jkuser",  
        password="Jkuser.jkuser_db.2024199",  
        host="10.10.10.99",  
        port="5432"  
    )  
    
    # 创建一个新的数据库会话  
    cur = conn.cursor()  
    
    # 执行插入语句  
    
    pg_sql_txt1="insert into public.mdb_slave_status (ip,app_name,status) values (%s, %s, %s)"
    cur.execute(pg_sql_txt1,(ip,app_name,status))
    
    # cur.execute("INSERT INTO your_table_name (column1, column2, column3) VALUES (%s, %s, %s)",(ip, app_name, status))  
    
    # 提交更改到数据库  
    conn.commit()  
    
    # 关闭会话和连接  
    cur.close()  
    conn.close() 
  
def check_slave_status(host, app_name, port): 
    ip = host
    app_name = app_name
    # 连接到MySQL服务器  
    try:  
        connection = pymysql.connect(host=host,  
                                     user='xxxxxxxxx',  
                                     password='xxxxxxxxxxxxxxxxxxxx',  
                                     port=port,  
                                     charset='utf8',  
                                     cursorclass=pymysql.cursors.DictCursor)  
    except pymysql.MySQLError as e:  
        print(f"Error while connecting to MySQL: {e}")  
        insert_pg_monitordb(ip,app_name,status=0)
        return False  
  
    try:  
        with connection.cursor() as cursor:  
            # 执行SHOW SLAVE STATUS命令  
            sql = "SHOW SLAVE STATUS"  
            cursor.execute(sql)  
            slave_status = cursor.fetchone()  
  
            # 检查Slave_IO_Running和Slave_SQL_Running是否都是'Yes'  
            if slave_status and slave_status['Slave_IO_Running'] == 'Yes' and slave_status['Slave_SQL_Running'] == 'Yes':  
                print("Slave is running normally.")  
                insert_pg_monitordb(ip,app_name,status=99)
                return True  
            else:  
                print("Slave is not running normally.") 
                insert_pg_monitordb(ip,app_name,status=1)
                return False  
    finally:  
        connection.close()  

def connect_pg_monitordb_mdb():  
    # 数据库连接参数  
    conn = psycopg2.connect(  
        database="jkuser_db",  
        user="jkuser",  
        password="Jkuser.jkuser_db.20240505",  
        host="10.10.10.166",  
        port="5432"  
    )  
    
    # 创建一个新的数据库会话  
    cur = conn.cursor()  
    
    # 执行查询语句  
    query = "SELECT app_name, ip,port FROM public.mdb_name where mrole='s'"  
    cur.execute(query)  
    
    # 获取查询结果  
    results = cur.fetchall()    
    
    # 处理查询结果，将每个结果转换为元组  
    results = [(row[0], row[1], row[2]) for row in results]  
    ##print(results)
    return results

        
    # 关闭会话和连接  
    cur.close()  
    conn.close()


# 使用示例 connect_pg_monitordb_mdb

result = connect_pg_monitordb_mdb()
print(result)
print("==================================================================================================")
for row in result:  
    # 提取每个字段的值  
    app_name = row[0]  
    host = row[1]  
    port = row[2]
    print(app_name + ':' + host + ':' + str(port))
    time.sleep(1) 
    check_slave_status(host,app_name, port)