Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 variables_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 |
+--------------------------+
1 row in set (0.00 sec)

mysql>

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
share|improve this answer
    
I had considered the slave_running=on option, but it seemed that would also return a negative if slaving was just temporarily disabled. Will system user in the PL show up if an only if the server is config'd to be a slave? If so can you point to docs to support the claim? (Not that I don't trust you, just need to be able to back up the claims if I assert them) –  atxdba Dec 30 '11 at 4:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.