How to find mysql DB is slave?

Question

How to find mysql DB is slave with out using "show slave status" by query?

Solution 1

Here are 3 options you have to detect if Replication is running

OPTION #1 : Check Status Variable 'Slave_running'

Using MySQL 5.1/5.5

select variable_value from information_schema.global_status
where variable_name = 'Slave_running';

Using MySQL 5.0 and back

SHOW VARIABLES LIKE 'Slave_running';

OPTION #2 : Check the Process List

Using MySQL 5.1+/5.5

select COUNT(1) SlaveThreads
from information_schema.processlist
where user = 'system user';

• If SlaveThreads = 2, Replication is Running
• If SlaveThreads = 1, Replication is Broken
• If SlaveThreads = 0, Replication is Stopped or Disabled

Using MySQL 5.0 and back

SHOW PROCESSLIST;

Look for 2 DB Conenctions thaty have 'system user' in the user column.

OPTION #3 : Check for presence of master.info

If replication is setup on a DB Server, look for master.info. By default, master.info is usually in /var/lib/mysql or wherever datadir is defined.

Simply run 'cat master.info' multiple times (For Windows community, type master.info). If the log position is moving, replication is on. If the log position is not moving, it could mean that replication is either broken (SQL Error in SQL Thread), stopped (due to STOP SLAVE;), or disabled (by running CHANGE MASTER TO MASTER_HOST='';).

Solution 2

According to MySQL doc - Checking Replication Status:

Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.

Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.