When working with MySQL replication, administrators often need to programmatically identify all slave servers connected to a master. While configuration files contain this information, querying the database directly provides a more dynamic approach.

MySQL maintains several system tables and commands that expose replication topology:

SHOW SLAVE HOSTS;
SELECT * FROM performance_schema.replication_group_members;
SHOW PROCESSLIST;

The most reliable method is using the SHOW SLAVE HOSTS command:

-- Returns slave connection information
SHOW SLAVE HOSTS;

-- Sample output format:
-- +-----------+-----------+------+-----------+
-- | Server_id | Host      | Port | Master_id |
-- +-----------+-----------+------+-----------+
-- | 2         | 192.168.1.5 | 3306 | 1         |
-- +-----------+-----------+------+-----------+

For username information, examine the process list:

SELECT user, host FROM information_schema.processlist 
WHERE command = 'Binlog Dump';

-- Alternative method using performance_schema:
SELECT user_host FROM performance_schema.threads 
WHERE processlist_command = 'Binlog Dump';

Here's a Python script that combines both approaches:

import mysql.connector

def get_replication_slaves(connection_params):
    slaves = []
    try:
        conn = mysql.connector.connect(**connection_params)
        cursor = conn.cursor(dictionary=True)
        
        # Get slave hosts
        cursor.execute("SHOW SLAVE HOSTS")
        slave_hosts = cursor.fetchall()
        
        # Get replication users
        cursor.execute("""
            SELECT user, host 
            FROM information_schema.processlist 
            WHERE command = 'Binlog Dump'
        """)
        users = cursor.fetchall()
        
        # Combine information
        for host in slave_hosts:
            for user in users:
                if user['host'].split(':')[0] == host['Host']:
                    slaves.append({
                        'server_id': host['Server_id'],
                        'ip': host['Host'],
                        'port': host['Port'],
                        'username': user['user']
                    })
        
        return slaves
    finally:
        if conn.is_connected():
            conn.close()

# Example usage
slaves = get_replication_slaves({
    'host': 'master-server',
    'user': 'admin',
    'password': 'securepassword'
})
print(slaves)

For more complex environments:

• For MySQL Group Replication, use SELECT * FROM performance_schema.replication_group_members
• For older MySQL versions, check SHOW PROCESSLIST for 'Binlog Dump' entries
• Always ensure your monitoring user has sufficient privileges (REPLICATION CLIENT, PROCESS)

Remember that exposing server IPs and usernames might present security risks. Consider:

• Implementing proper access controls on your monitoring tool
• Using encrypted connections for all replication traffic
• Regularly rotating replication user credentials

In MySQL replication environments, the master server maintains crucial information about connected slaves in several system tables and variables. While my.cnf contains configuration details, we can programmatically fetch slave information through SQL queries and SHOW commands.

The most direct approach is executing:

SHOW SLAVE HOSTS;

This returns columns including Server_id, Host (IP address), and sometimes User if slaves register with report-host/report-port parameters. However, this method has limitations:

• Only shows slaves that registered with --report-host
• Username might not be available

For MySQL 5.6+ with Performance Schema enabled:

SELECT * FROM performance_schema.replication_connection_configuration;

This provides detailed connection information including HOST, PORT, and USER for each slave.

Slaves appear in the process list with specific patterns:

SHOW PROCESSLIST;

Look for processes where the Command column shows "Binlog Dump" - these are slave connections. The Host column shows the IP, but usernames might be generic replication accounts.

Here's a complete Python script using MySQL Connector:

import mysql.connector

def get_slaves_info(host, user, password):
    conn = mysql.connector.connect(
        host=host,
        user=user,
        password=password
    )
    cursor = conn.cursor(dictionary=True)
    
    # Method 1: SHOW SLAVE HOSTS
    cursor.execute("SHOW SLAVE HOSTS")
    slave_hosts = cursor.fetchall()
    
    # Method 2: Performance Schema
    cursor.execute("""
        SELECT HOST, PORT, USER 
        FROM performance_schema.replication_connection_configuration
    """)
    perf_schema_slaves = cursor.fetchall()
    
    # Method 3: Process List
    cursor.execute("SHOW PROCESSLIST")
    processes = [
        p for p in cursor.fetchall() 
        if p['Command'] == 'Binlog Dump'
    ]
    
    return {
        'slave_hosts': slave_hosts,
        'perf_schema_slaves': perf_schema_slaves,
        'binlog_dump_processes': processes
    }

For advanced cases where slaves don't register properly:

SHOW RELAYLOG EVENTS LIMIT 100;

This can reveal slave connection patterns in certain configurations.

• Ensure your monitoring user has sufficient privileges (REPLICATION CLIENT at minimum)
• Results may vary based on MySQL version and replication topology
• Some methods work better in traditional replication vs GTID replication
• For security, consider using encrypted connections when retrieving this data