Leader election using MySQL

Being a stateful, centralized datastore, MySQL can serve in negotiating leadership: a mechanism to elect a single service out of multiple services; moreover, a mechanism to promote a new leader should the existing leader cease to function.

What of Zookeeper?

Zookeeper makes for an excellent leader election mechanism. This is one of the most recognized uses for Zookeeper. It has HA via multiple nodes & quorum,  ephemeral nodes, all you need. To achieve similar benefits with MySQL you’d need to use Galera or NDB Cluster; so why not use Zk? The use case at hand is

orchestrator, a multi-node, mostly stateless service that happens to use MySQL as backend datastore. Ir relies on MySQL to exist in backend. It already expects it to be there. If the MySQL server is down, so is the service, effectively. In such case it doesn’t hurt adding another dependency on MySQL; this does not reduce HA. You need to take care of MySQL HA anyhow so there’s no additional cost. In fact, going to Zookeeper makes the additional cost as you introduce a new component to the system that can be avoided.

Terms of the solution

Our proposed solution offers:

• Single leader election out of multiple nodes
• Leader actively reaffirms its leadership periodically
• Timeout based re-election: decision to re-elect new leader based on the fact current leader has not reaffirmed its leadership over X seconds
• A way to forcibly assume leadership for a specific node
• A way to forcibly call for re-elections by demoting existing leader
• A node/service can easily tell whether it’s the leader or not
• Anyone can tell who the leader is

SQL solution

The solution is composed of a single table and a set of queries which implement the above offers. We assume a service can uniquely identify itself; this is easy to achieve:

• If services are running from different hosts (as should be the case, this is service HA), use hostname for ID
  • But what if the service restarts? Are you good with calling this “the same service” or is this now a new service running on the same host?
• In such case use combination of hostname & OS process ID
  • Or generate a random token upon startup
  • Or use startup timestamp Whichever solution you pick, make sure it is human readable, such that it is easy to tell

which service is the leader. This helps operations. We note this as service_id

Table

The following table will have a single row; the

service_id in that row is the active leader.

CREATE TABLE service_election ( 
  anchor tinyint(3) unsigned NOT NULL, 
  service_id varchar(128) NOT NULL, 
  last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (anchor) 
) ENGINE=InnoDB 

Attempt election

All services issue the following, periodically. Say every service issues this once per second:

insert ignore into service_election ( anchor, service_id, last_seen_active ) values ( 1, 'my_service_number_7', now() ) on duplicate key update service_id = if(last_seen_active < now() - interval 20 second, values(service_id), service_id), last_seen_active = if(service_id = values(service_id), values(last_seen_active), last_seen_active) ;

• Replace ‘my_service_number_7’ with specific service_id per service.* The above is the most complex query in our solution; breakdown:
• Assume timeout is set for 20 seconds
• The first ever service (my_service_number_7) succeeds in registering (inserting its own service_id into the table)
• The next time this service issues the query, it finds that it is already the owner, hence updates last_seen_active.
  • And while it is alive and keeps on polling, it remains the leader.
• Immediately following, another service (my_service_number_12) does not update anything: since last_seen_active < now() – interval 20 second is false, service_id is unchanged, nor is last_seen_active.
• Assuming said service (my_service_number_7) has died and hasn’t injected anything in the last 20 seconds, a different service will inject its own service_id as well as update last_seen_active. It will become the leader.

Forcibly assume leadership

Make a certain service the leader:

replace into service_election ( anchor, service_id, last_seen_active ) values ( 1, 'my_service_number_12', now() ) 

The next time my_service_number_12 attempts to register its own leadership, it finds out it’s already the leader and updates last_seen_active.

Force re-elections

The next service to attempt election will succeed after this:

delete from service_election;

Am I the leader?

A service can query as follows:

select count(*) as is_leader from service_election where anchor=1 and service_id='my_service_number_7';

Who is the leader?

This is of course very simple:

select max(service_id) as leader from service_election where anchor=1;

What of GET_LOCK()?

GET_LOCK() allows one to acquire a helper-lock of arbitrary name; this lock is reserved as long as the connection that acquired it lives. No other connection can acquire lock of same name.

SELECT GET_LOCK("my_service_leadership", 0)

Returns 1 on success, 0 on failure to obtain lock. This supposedly makes a much simpler leader election mechanism. However I don’t like it for the following reasons: * You must allocate a dedicated connection that lives for the duration of your service * This doesn’t play too well with ORMs or typical connection pools * There is no visibility (< MySQL 5.7) into who is holding the lock. You only know that it’s being held. * If your service hangs (but does not die), the lock is still being held. There is no “keepalive” requirement on the lock’s side.