1

I am using innnodb cluster. It is good that every time a R/W node failed the R/O node will being promoted to R/W.

However I have to restart scheduler and alter event enabled (originally slave side disabled) manually for the cluster again. It pretty error prone and troublesome. I always have to run the script to manually fill the losing data for the time event is not running.

Is there anyway make it automized so I can be hands off?

0

As much I understand your question to Automatically restart event scheduler and alter event when slave being promoted to master. You have always have to run the script to manually fill the losing data for the time event is not running. AS much MySQL documents states my Answer is No

You have to follow these steps manually:

To check event scheduler status on promoted master, use SHOW EVENTS, like this:

 SHOW EVENTS
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you might wish to query the INFORMATION_SCHEMA.EVENTS table as shown here:

  SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, use the following query to enable the events:

 UPDATE mysql.event
 SET STATUS = 'ENABLED'
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Before executing either of the previous two UPDATE statements, you should disable the Event Scheduler on the slave

  using SET GLOBAL event_scheduler = OFF;

run the UPDATE, restart the server, then re-enable the Event Scheduler afterwards

  using SET GLOBAL event_scheduler = ON;

If you later demote the new master back to being a replication slave, you must disable manually all events enabled by the UPDATE statement. You can do this by storing in a separate table the event names from the SELECT statement shown previously, or using an UPDATE statement to rename the events with a common prefix to identify them, as shown in this example:

 UPDATE mysql.event
 SET NAME = CONCAT('replicated_', NAME)
 WHERE STATUS = 'SLAVESIDE_DISABLED';

When demoting this server back to being a replication slave, you can then rename and disable the events like this:

 UPDATE mysql.event
 SET NAME = REPLACE(NAME, 'replicated_', ''),
 STATUS = 'SLAVESIDE_DISABLED'
 WHERE INSTR(NAME, 'replicated_') = 1;
  • thank you for answering. So it means everytime I found a R/W node down I have to rerun all the script manually... That's pretty sad. – Yen May 15 '18 at 23:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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