ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

Switchover

Sometimes it is necessary to switch the role of master from the original master to one of the slaves. You may
need to do this to perform maintenance on the master, recover from data loss, and perform other events that
require stopping replication for a period of time. Rather than force your solution to stop altogether, you can
simply switch the master role temporarily and then switch it back when the master is back online.
What follows is a demonstration of performing switchover using MySQL Utilities. Recall MySQL Utilities
consists of a set of Python scripts and libraries designed to help automate server maintenance. Many of the
utilities are designed to assist in setting up and managing replication. Listing 8-2 shows an execution of the
mysqlrpladmin utility to perform switchover from the master to one of the slaves. The output shows you all
the steps taken. Had you done this manually, you would have had to do each yourself on each slave and then
the master.

Listing 8-2. Switchover Using mysqlrpladmin

$ mysqlrpladmin switchover --demote-master --master=root:secret@localhost:13001 --new-
master=root:secret@localhost:13002 --discover-slaves=root:secret

Discovering slaves for master at localhost:13001

Discovering slave at localhost:13002

Found slave: localhost:13002

Discovering slave at localhost:13003

Found slave: localhost:13003

Discovering slave at localhost:13004

Found slave: localhost:13004

Checking privileges.

Performing switchover from master at localhost:13001 to slave at localhost:13002.

Checking candidate slave prerequisites.

Checking slaves configuration to master.

Waiting for slaves to catch up to old master.

Stopping slaves.

Performing STOP on all slaves.

Demoting old master to be a slave to the new master.

Switching slaves to new master.

Starting all slaves.

Performing START on all slaves.

Checking slaves for errors.

Switchover complete.

Replication Topology Health:

+------------+--------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------+--------+---------+--------+------------+---------+
| localhost | 13002 | MASTER | UP | ON | OK |
| localhost | 13001 | SLAVE | UP | ON | OK |
| localhost | 13003 | SLAVE | UP | ON | OK |
| localhost | 13004 | SLAVE | UP | ON | OK |
+------------+--------+---------+--------+------------+---------+

...done.

To perform switchover, I specify the original master (recall switchover requires a healthy, running
master), the new master, and the login and password for each slave (called discovery). The options I specified
to retain the master as a slave are what are interesting here. The demote-master option returns the master as