Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
EPUB - 377.5Kb
HTML Download (TGZ) - 299.6Kb
HTML Download (Zip) - 310.6Kb


MySQL Utilities 1.5 Manual  /  ...  /  How Do I Add New Servers to My Topology and Change Master Role

3.4.2 How Do I Add New Servers to My Topology and Change Master Role

We will examine a scenario similar to the previous one where we want to make one of the two new servers added the master server (perhaps because it has better specs and is faster).

Objectives

Our goal in this example it create replication configuration with 3 servers, two new ones and an existing one, and we want to replicate all the information, but make one of the new servers the master server.

Like the previous example, lets assume that the existing server, Server1, is running on port 13001 on the local machine with IP 192.168.1.1 that the two new machines with mysql server instances are Server2 running on 192.168.1.2:13001 and Server3 running on 192.168.1.3:3306. We want to make Server2 the new master.

Example Execution

shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
          --slave=slave_acc1@192.168.1.2:13001 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.2: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

shell> mysqlreplicate --master=m_account@192.168.1.1:13001 \
          --slave=slave_acc2@192.168.1.3:3306 --rpl-user=repl:slavepass -b
# master on 192.168.1.1: ... connected.
# slave on 192.168.1.3: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.

shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
          --slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 health

# Checking privileges.
#
# Replication Topology Health:
+--------------+--------+---------+--------+------------+------------------------------+
| host         | port   | role    | state  | gtid_mode  | health                       |
+--------------+--------+---------+--------+------------+------------------------------+
| 192.168.1.1  | 13001  | MASTER  | UP     | ON         | OK                           |
| 192.168.1.2  | 13001  | SLAVE   | UP     | ON         | Slave delay is NNN seconds   |
| 192.168.1.3  | 3306   | SLAVE   | UP     | ON         | Slave delay is NNN seconds   |
+--------------+--------+---------+--------+------------+------------------------------+
# ...done.

shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
          --slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 health

# Checking privileges.
#
# Replication Topology Health:
+--------------+--------+---------+--------+------------+---------+
| host         | port   | role    | state  | gtid_mode  | health  |
+--------------+--------+---------+--------+------------+---------+
| 192.168.1.1  | 13001  | MASTER  | UP     | ON         | OK      |
| 192.168.1.2  | 13001  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.3  | 3306   | SLAVE   | UP     | ON         | OK      |
+--------------+--------+---------+--------+------------+---------+
# ...done.

shell> mysqlrpladmin --master=m_account@192.168.1.1:13001 \
          --slaves=slave_acc1@192.168.1.2:13001,slave_acc2@192.168.1.3:3306 \
          --new-master=slave_acc1@localhost:13002 --demote-master switchover
# Checking privileges.
# Performing switchover from master at 192.168.1.1:13001 to slave at 192.168.1.2:13001.
# 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  |
+--------------+--------+---------+--------+------------+---------+
| 192.168.1.2  | 13001  | MASTER  | UP     | ON         | OK      |
| 192.168.1.1  | 13001  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.3  | 3306   | SLAVE   | UP     | ON         | OK      |
+--------------+--------+---------+--------+------------+---------+        

Discussion

As with our previous scenario we used the mysqlreplicate utility to set up a replication topology between the existing server and the two new servers. Notice the use of the -b flag which this replication start from the first event recorded in the master's binary log.

After setting our replication topology we made use of the mysqlrpladmin utility specifying both the master and slave servers and using the health command to check the status of the replication. Since our master server had lots of information, it is normal for the new slaves to take some time to catch up, thus the slave delay message on the health column of the output.

However, if all goes well, after some time the slaves will eventually catch up, and when that happens, the health column will show an OK status.

When this happened, we used the mysqlrpladmin utility yet again, this time with switchover command. Using the --new-master option we specify the server that will become the new master. We can not forget the --demote-master option which turns the old master into a slave, otherwise it would still behave as a master just without any slaves, Server3 will become a slave of Server2.

After the switchover, Server2 becomes the master server for both Server1 and Server3 which are now the slaves.

Permissions Required

The m_account user needs the following privileges for the mysqlreplicate: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. As for the slave_acc users, they need the SUPER privilege. The repl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

To run the mysqlrpladmin utility with the health command, the m_account used on the master needs an extra SUPER privilege.

As for the switchover command all the users need the following privileges: SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE and REPLICATION SLAVE

Tips and Tricks

We can use the --discover-slaves-login option for mysqlrpladmin in order to detect the slaves automatically instead of manually specifying the slaves.

The mysqlrpladmin utility allows users to specify a script to execute before and after the failover and switchover operations using the --exec-before and --exec-after options respectively. Note that the script specified using the exec-after option only runs in case the switchover/failover executes successfully.

We can use the mysqlrpladmin utility to start and stop all the slaves with the start/stop commands. Using the stop command only stops servers that are actually slaves of the specified master thus preventing us from stopping unwanted servers.


User Comments
Sign Up Login You must be logged in to post a comment.