When Your Primary Server Sees Itself as a Replica

The Mystery

During replica setup, a DBA executed SHOW SLAVE HOSTS on the ​primary​ server and saw its ​own IP​ listed as a replica:

Primary Server Output:​

mysql> SHOW SLAVE HOSTS;
+-----------+--------------+------+-----------+
| Server_id | Host         | Port | Master_id |
+-----------+--------------+------+-----------+
| 200       | 10.186.65.33 | 6607 | 100       | -- Primary's own IP!
+-----------+--------------+------+-----------+

‍

Investigation

1. Verify Replica Operation

-- On replica server:
SHOW REPLICA STATUS\G  
Slave_IO_State: Waiting for master to send event
Master_Host: 10.186.65.33  -- Correct primary IP
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

‍

2. Critical Discovery

-- On replica server:
SHOW VARIABLES LIKE '%report%';
+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| report_host     | 10.186.65.33| -- Primary's IP!
| report_port     | 6607         |
+-----------------+--------------+

‍

Root Cause: The Copy-Paste Error

The replica used the ​primary's configuration file​ without updating:

# In replica's my.cnf:
[mysqld]
report_host=10.186.65.33  # Primary's IP (never changed
server_id=200             # Only server_id was updated

‍

Why This Happens

• report_host is ​non-dynamic: Requires restart to change
• When replicas connect, they send this value to the primary
• Primary stores it in SHOW SLAVE HOSTS output
• ​Default behavior since MySQL 8.0.22: SHOW REPLICAS replaces SHOW SLAVE HOSTS

The Fix

1. Update the replica's configuration:

# Corrected replica config:
report_host=10.186.65.34  # Replica's actual IP
report_port=6607

‍

2. Restart the replica instance

Key Takeaway

Always audit these parameters when cloning MySQL configurations:

# Critical clone checklist:
server_id
report_host
report_port
auto_increment_offset

‍

Ignoring report_* parameters won't break replication, but causes misleading management data!​