Mysql – Taking load off the Master server by using a slave

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

• set the server_id as a different number from M1, M2, M3
• set log-slave-updates in my.cnf like you did for M1, M2, M3
• enable binary logging on S1 the same way you enabled it on M1, M2, M3
• restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.

When you shifted the IP within your application, any DB Connections that were open at the moment were totally unaware of the move. A quick netstat | grep -i mysql would reveal that on Master1.

You would be better off doing the cutover of the IP as follows:

• On Master1
  • FLUSH HOSTS;
  • service mysql stop
• Change the IP in the App
• On all web servers, service httpd restart

In order to safeguard the app from having to edit it for the sake of assigning a new IP, try using a DB VIP instead.

For example:

• Master1 is 10.1.2.30
• Master2 is 10.1.2.40
• Use 10.1.2.70 as the DBVIP

Here is what you can setup

• On Master1, run ip addr add 10.1.2.70/24 dev eth1
• Use 10.1.2.70 in your app

Then, when it is time to cutover, do the following

• On Master1
  • FLUSH HOSTS;
  • service mysql stop
  • ip addr del 10.1.2.70/24 dev eth1
• On Master2
  • ip addr add 10.1.2.70/24 dev eth1
  • SHOW SLAVE STATUS\G and make sure all final SQL statements from Master1 executed
• On all web servers, service httpd restart

That way, a cutover would not involve editing any part of the app. Please notice that I did not mention running STOP SLAVE anywhere because this would allow any final SQL statements to flow over from Master1 to Master2 once mysql is stopped on Master1.