MySQL multi-server tasks fall-back mechanism

Question

I have two web servers. Server 'A' and server 'B'. Both are exact duplicate of the other and are connected to a load-balancer for serving clients.

I have a tasks (cronjob) that is executed at every hour, every day. This task is currently running only on server 'A'. The problem is that this tasks is really important and if server 'A' crash for some reason the task will no longer run.

I would like server 'B' to execute the task but only if server 'A' didn't. Both tasks are PHP scripts interacting with a MySQL database.

I'm thinking about using some rows in a table (using MySQL locks?) as a flag for the server 'B' to start his task if server 'A' didn't do it and vice versa.

Please note that the task file on both servers must be exactly the same.

Thank you.

Answer 1

Your proposed solution is one I often employ for simpler tasks, usually with something like:

• cronjob is started on both servers alike
• both get different tokens (something based on server-id & process-id usually)
• both try to claim a job in a table with UPDATE jobs SET process = '$token' WHERE process IS NULL (+ some other clauses not to the point right now).
• both check for processes won/claimed: SELECT * FROM jobs WHERE process = '$token'
• only the 'winner' performs the job, loser / jobless process just exits.

Something to keep in mind though that this only works for jobs as long as the MySQL instance is up: no processing can be done without it. Then again, a cronjob which checks whether the MySQL server is alive can be performed on both servers, I don't mind to get 2 alerts for such a major catastrophe :)