Master-Slave Switching Process

Master-Slave Switching Based on Log Position

When configuring node B as a slave of node A’, you need to execute a “change master” command with six parameters:

  • MASTER_HOST, MASTER_PORT, MASTER_USER, and MASTER_PASSWORD represent the IP address, port, username, and password of master A’, respectively.
  • MASTER_LOG_FILE and MASTER_LOG_POS indicate the position in the master’s log file from which synchronization should continue. This position is known as the synchronization point, denoting the filename and log offset on the master.

Originally, node B was a slave of A and recorded A’s log position locally. However, for the same set of logs, the log positions in A and A’ differ. Therefore, when switching slave B, it must first go through a “synchronization point finding” process.

One method to determine the synchronization point is as follows:

  1. Wait for the new master A’ to fully synchronize the relay logs.
  2. Execute the show master status command on A’ to obtain the latest File and Position on A’.
  3. Determine the moment T when the original master A failed.
  4. Utilize the mysqlbinlog tool to parse the File on A’ and obtain the log position at moment T.

GTID (Global Transaction Identifier)

To address the complexity and challenges posed by methods like sql_slave_skip_counter and slave_skip_errors for establishing a master-slave relationship between slave B and the new master A’, MySQL introduced GTID (Global Transaction Identifier) in version 5.6.

GTID is a unique identifier generated when a transaction is committed, comprising two parts: server_uuid:gno.

  • server_uuid is automatically generated when an instance starts for the first time, ensuring a globally unique value.
  • gno is an integer, starting at 1, and incremented with each transaction’s commitment. Unlike transaction_id in MySQL, which can increase even in case of transaction rollbacks, gno is assigned only upon transaction commitment.

In GTID mode, each transaction corresponds to a unique GTID, generated based on the session variable gtid_next.

  1. If gtid_next=automatic, MySQL assigns the default value server_uuid:gno to the transaction.

    • When recording binlog, it first records a line SET @@SESSION.GTID_NEXT='server_uuid:gno'.
    • This GTID is added to the instance’s GTID set.

  2. If gtid_next is a specific GTID value, such as current_gtid, two possibilities exist:
    a. If current_gtid already exists in the instance’s GTID set, the subsequent transaction is directly ignored.
    b. If current_gtid does not exist in the instance’s GTID set, it is assigned to the upcoming transaction, obviating the need to generate a new GTID, and gno remains unchanged.

Master-Slave Switching Based on GTID

In GTID mode, setting slave B as a slave of the new master A’ is done using the following syntax: