# MySQL复制监控脚本
import pymysql
import time
import logging

class MySQLReplicationMonitor:
    def __init__(self, master_config, slave_configs):
        self.master_config = master_config
        self.slave_configs = slave_configs
        self.logger = logging.getLogger(__name__)
    
    def check_replication_status(self):
        """检查复制状态"""
        for slave_config in self.slave_configs:
            try:
                conn = pymysql.connect(**slave_config)
                cursor = conn.cursor()
                
                # 检查复制状态
                cursor.execute("SHOW SLAVE STATUS")
                result = cursor.fetchone()
                
                if result:
                    slave_io_running = result[10]  # Slave_IO_Running
                    slave_sql_running = result[11]  # Slave_SQL_Running
                    seconds_behind_master = result[32]  # Seconds_Behind_Master
                    
                    if slave_io_running != 'Yes' or slave_sql_running != 'Yes':
                        self.logger.error(f"复制异常: {slave_config['host']}")
                    
                    if seconds_behind_master > 60:
                        self.logger.warning(f"复制延迟: {slave_config['host']} 延迟{seconds_behind_master}秒")
                
                conn.close()
                
            except Exception as e:
                self.logger.error(f"监控异常: {slave_config['host']} - {str(e)}")
    
    def start_monitoring(self):
        """启动监控"""
        while True:
            self.check_replication_status()
            time.sleep(30)

# 使用示例
if __name__ == "__main__":
    master_config = {
        'host': '192.168.1.10',
        'user': 'monitor',
        'password': 'monitor_password',
        'database': 'mysql'
    }
    
    slave_configs = [
        {
            'host': '192.168.1.11',
            'user': 'monitor',
            'password': 'monitor_password',
            'database': 'mysql'
        },
        {
            'host': '192.168.1.12',
            'user': 'monitor',
            'password': 'monitor_password',
            'database': 'mysql'
        }
    ]
    
    monitor = MySQLReplicationMonitor(master_config, slave_configs)
    monitor.start_monitoring()

# 数据一致性检查脚本
import pymysql
import hashlib
import json

class DataConsistencyChecker:
    def __init__(self, master_config, slave_configs):
        self.master_config = master_config
        self.slave_configs = slave_configs
    
    def get_table_checksum(self, host_config, table_name):
        """获取表校验和"""
        conn = pymysql.connect(**host_config)
        cursor = conn.cursor()
        
        # 计算表数据校验和
        cursor.execute(f"""
            SELECT 
                COUNT(*) as row_count,
                MD5(GROUP_CONCAT(CONCAT_WS('|', *))) as data_hash
            FROM {table_name}
        """)
        
        result = cursor.fetchone()
        conn.close()
        
        return {
            'row_count': result[0],
            'data_hash': result[1]
        }
    
    def check_consistency(self, table_name):
        """检查数据一致性"""
        master_checksum = self.get_table_checksum(self.master_config, table_name)
        
        inconsistencies = []
        
        for slave_config in self.slave_configs:
            slave_checksum = self.get_table_checksum(slave_config, table_name)
            
            if (master_checksum['row_count'] != slave_checksum['row_count'] or
                master_checksum['data_hash'] != slave_checksum['data_hash']):
                
                inconsistencies.append({
                    'slave_host': slave_config['host'],
                    'master_row_count': master_checksum['row_count'],
                    'slave_row_count': slave_checksum['row_count'],
                    'master_hash': master_checksum['data_hash'],
                    'slave_hash': slave_checksum['data_hash']
                })
        
        return inconsistencies
    
    def check_all_tables(self):
        """检查所有表的一致性"""
        conn = pymysql.connect(**self.master_config)
        cursor = conn.cursor()
        
        # 获取所有表名
        cursor.execute("SHOW TABLES")
        tables = [row[0] for row in cursor.fetchall()]
        conn.close()
        
        all_inconsistencies = {}
        
        for table in tables:
            inconsistencies = self.check_consistency(table)
            if inconsistencies:
                all_inconsistencies[table] = inconsistencies
        
        return all_inconsistencies

# 使用示例
if __name__ == "__main__":
    master_config = {
        'host': '192.168.1.10',
        'user': 'root',
        'password': 'password',
        'database': 'test'
    }
    
    slave_configs = [
        {
            'host': '192.168.1.11',
            'user': 'root',
            'password': 'password',
            'database': 'test'
        },
        {
            'host': '192.168.1.12',
            'user': 'root',
            'password': 'password',
            'database': 'test'
        }
    ]
    
    checker = DataConsistencyChecker(master_config, slave_configs)
    inconsistencies = checker.check_all_tables()
    
    if inconsistencies:
        print("发现数据不一致:")
        print(json.dumps(inconsistencies, indent=2))
    else:
        print("所有表数据一致")

# 复制性能监控脚本
import pymysql
import time
import psutil

class ReplicationPerformanceMonitor:
    def __init__(self, master_config, slave_configs):
        self.master_config = master_config
        self.slave_configs = slave_configs
    
    def get_replication_metrics(self, slave_config):
        """获取复制指标"""
        conn = pymysql.connect(**slave_config)
        cursor = conn.cursor()
        
        # 获取复制状态
        cursor.execute("SHOW SLAVE STATUS")
        result = cursor.fetchone()
        
        if result:
            metrics = {
                'slave_io_running': result[10],
                'slave_sql_running': result[11],
                'seconds_behind_master': result[32],
                'master_log_file': result[5],
                'master_log_pos': result[6],
                'relay_log_file': result[7],
                'relay_log_pos': result[8]
            }
        else:
            metrics = None
        
        conn.close()
        return metrics
    
    def get_system_metrics(self, host):
        """获取系统指标"""
        # 这里需要SSH连接到目标主机获取系统指标
        # 或者使用监控代理
        return {
            'cpu_usage': psutil.cpu_percent(),
            'memory_usage': psutil.virtual_memory().percent,
            'disk_usage': psutil.disk_usage('/').percent
        }
    
    def monitor_performance(self):
        """监控性能"""
        while True:
            for slave_config in self.slave_configs:
                replication_metrics = self.get_replication_metrics(slave_config)
                system_metrics = self.get_system_metrics(slave_config['host'])
                
                print(f"从库 {slave_config['host']} 性能指标:")
                print(f"  复制延迟: {replication_metrics['seconds_behind_master']}秒")
                print(f"  IO线程状态: {replication_metrics['slave_io_running']}")
                print(f"  SQL线程状态: {replication_metrics['slave_sql_running']}")
                print(f"  CPU使用率: {system_metrics['cpu_usage']}%")
                print(f"  内存使用率: {system_metrics['memory_usage']}%")
                print(f"  磁盘使用率: {system_metrics['disk_usage']}%")
                print("-" * 50)
            
            time.sleep(60)

# 使用示例
if __name__ == "__main__":
    master_config = {
        'host': '192.168.1.10',
        'user': 'monitor',
        'password': 'monitor_password',
        'database': 'mysql'
    }
    
    slave_configs = [
        {
            'host': '192.168.1.11',
            'user': 'monitor',
            'password': 'monitor_password',
            'database': 'mysql'
        }
    ]
    
    monitor = ReplicationPerformanceMonitor(master_config, slave_configs)
    monitor.monitor_performance()