This will work if the Master has a Slave Connected
SELECT COUNT(1) FROM
(SELECT user FROM information_schema.processlist) A
INNER JOIN
(SELECT user FROM mysql.user B WHERE repl_slave_priv = 'Y')
USING (user);
If the answer is...
- > 0, Replication is running (This indicates this is a Master)
- = 0, Replication is not running
You can also check the global status variable 'Slave_running'
SELECT COUNT(1) FROM information_schema.global_status
WHERE variable_name = 'Slave_running'
AND variable_value = 'ON';
If the answer is...
- > 0, Replication is running (This indicates this is a Slave)
- = 0, Replication is not running
You can also check the processlist for 'system user'
SELECT COUNT(1) FROM information_schema.processlist
WHERE user = 'system user';
If the answer is...
- = 2, Replication is running (This indicates this is a Slave)
- = 1, Replication is broken (This indicates this is a Slave)
- = 0, Replication is not running
I hope these queries give you some direction.
CAVEAT
SHOW MASTER STATUS;
just tells you what the current binary log is. If your Slaves have binary logs disabled, then SHOW MASTER STATUS;
return nothing. There is no way to capture this in the information_schema database.
The only obvious way is to either
SHOW SLAVE STATUS\G
- Look for the master.info file in the OS
UPDATE 2011-12-30 12:50 EDT
Here is simply way to identify your master from your slaves: Create a Table whose sole purpose to is to hold the server names that make up your Master. Run these commands on all your DB Servers, (Masters and Slaves)
CREATE TABLE mysql.MasterList
(
hostname VARCHAR(64),
PRIMARY KEY (hostname)
) ENGINE=MyISAM;
INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Now just run this query to determine if it is a Master:
SELECT COUNT(1) INTO @IsThisMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);
Here is a sample run on my PC using MySQL 5.5.12
mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| hostname | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE mysql.MasterList
-> (
-> hostname VARCHAR(64),
-> PRIMARY KEY (hostname)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
-> (SELECT variable_value hostname FROM information_schema.global_variables
-> WHERE variable_name='hostname') A
-> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
-> (SELECT variable_value hostname FROM information_schema.global_variables
-> WHERE variable_name='hostname') A
-> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
Give it a Try !!!
UPDATE 2011-12-30 13:06 EDT
Here is the Stored Function you will need:
DELIMITER $$
DROP FUNCTION IF EXISTS `mysql`.`Is_This_A_Master` $$
CREATE FUNCTION `mysql`.`Is_This_A_Master` () RETURNS INT
DETERMINISTIC
BEGIN
SELECT COUNT(1) INTO @IsThisReplicationMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);
RETURN @IsThisReplicationMaster;
END $$
DELIMITER ;
Here is a Sample Call:
mysql> select mysql.Is_This_A_Master();
+--------------------------+
| mysql.Is_This_A_Master() |
+--------------------------+
| 1 |
+--------------------------+
UPDATE 2011-12-30 13:12 EDT
Just remember
- Once a Server Becomes a Slave, delete that hostname from mysql.MasterList
- Once a Server Becomes a Master, insert that hostname into mysql.MasterList