MySQL Primary or Replica

Posted on | Edited on | In

The question is, how can I know the mysql server is a primary, replica or dual?

Is It a Replica

1
SHOW REPLICA STATUS\G

What to look for in the output:

  • Replica_IO_Running: Yes
  • Replica_SQL_Running: Yes

If both of these are Yes, the server is running as a replica and is actively trying to pull and apply changes from a primary. If either is No or Connecting, it’s configured as a replica but replication might be stopped or encountering issues.

  • Source_Host (or Master_Host in older versions): This field will show the hostname or IP address of the server it’s replicating from. If this is populated, it’s a strong indicator that this server is a replica. If it’s empty, it’s not configured as a replica.

If SHOW REPLICA STATUS returns an empty set, it means the server is NOT configured as a replica.

Is It a Master

The master must have binlog enabled if it is used as a source for replica:

1
2
3
mysql> show variables where variable_name in ('log_bin', 'binlog_format');

SHOW MASTER STATUS\G

If it has replica and the replication is running:

1
2
3
SHOW REPLICAS\G

SHOW PROCESSLIST\G

Dual

Have both properties above, for example, the MySQL server who is used as a cascading replica.

0%