Replication - High Performance MySQL - page 493

Figure 10-15. When server1 crashed, server2 was caught up, but server3 was behind in replication
the last positions on server1 . Therefore, we can promote server2 to be the new master
and make server3 a replica of it.
But what parameters should we use in the CHANGE MASTER TO command on server3 ?
This is where we need to do a little math and investigation. server3 stopped at offset
1493, which is 89 bytes behind offset 1582, the last command server2 executed.
server2 is currently writing to position 8167 in its binary log. 8167 - 89 = 8078, so in
theory we need to point server3 at that offset in server2 's logs. It's a good idea to
investigate the log events around this position and verify that server2 really has the
right events at that offset in its logs, though. It might have something else there because
of a data update that happened only on server2 , for example.
Assuming that the events are the same upon inspection, the following command will
switch server3 to be a replica of server2 :
server2> CHANGE MASTER TO MASTER_HOST="server2", MASTER_LOG_FILE="mysql-bin.000009",
MASTER_LOG_POS=8078;
What if server1 had actually finished executing and logging one more event, beyond
offset 1582, when it crashed? Because server2 had read and executed only up to offset
1582, you might have lost one event forever. However, if the old master's disk isn't
damaged, you can still recover the missing event from its binary log with mysqlbinlog
or with a log server.