What is the Best Way to detect if server is a Master inside a Stored Procedure? I'm want to create a stored procedure/event to do some maintenance tasks.  My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be promoted in the event of primary master failure).

I'd like to have this stored procedure run in the event scheduler on both.  During the normal course of operation I'd like it to do it's thing and let those operations flow through the normal chain of replication.  In the event of failover I'd like this to just detect it's not the master and continue the work on what was previously the slave.  (Less things to worry about getting in place during such an event).

I was thinking wrapping the initial call in a block of pseudo code that looked like

if I_AM_MASTER then

call real_work_sp;

end if;

What's the best way to test for I_AM_MASTER?  I know I can do show master status from the commandline, but I'm not sure how to capture that for purposes of this test.  How would I do that, or is there a better way?

P.S. The operations aren't just deletes I could just let "fall through" if they happened to get run twice.
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&gt; show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql&gt; CREATE TABLE mysql.MasterList
    -&gt; (
    -&gt;     hostname VARCHAR(64),
    -&gt;     PRIMARY KEY (hostname)
    -&gt; ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql&gt; INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql&gt; SELECT COUNT(1) INTO @IsThisMaster FROM
    -&gt; (SELECT variable_value hostname FROM information_schema.global_variables
    -&gt; WHERE variable_name='hostname') A
    -&gt; INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)

mysql&gt; SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql&gt; INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT COUNT(1) INTO @IsThisMaster FROM
    -&gt; (SELECT variable_value hostname FROM information_schema.global_variables
    -&gt; WHERE variable_name='hostname') A
    -&gt; INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)

mysql&gt; SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql&gt;


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&gt; 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