MySQL master library failover mainly includes four steps. 1. Fault detection: Regularly check the main library process, connection status and simple query to determine whether it is downtime, set a retry mechanism to avoid misjudgment, and can use tools such as MHA, Orchestrator or Keepalived to assist in detection; 2. Select the new main library: Select the most suitable slave library to replace it according to the data synchronization progress (Seconds_Behind_Master), binlog data integrity, network delay and load conditions, and perform data compensation or manual intervention if necessary; 3. Switch topology: Point other slave libraries to the new master library, execute RESET MASTER or enable GTID, update VIP, DNS or proxy configurations to achieve transparent switching of applications; 4. Application layer cooperation: Clients must support automatic reconnection, exception handling, and DNS Mechanisms such as cache refresh, combined with connection pool middleware such as HikariCP to improve fault tolerance and ensure seamless service transition.

MySQL master library failover is a key link in a high-availability architecture. The purpose is to quickly transfer traffic to a slave library when an exception occurs in the main library to ensure that the service is continuously available. This process usually includes several steps such as detecting failures, selecting a new master library, and switching client connections.

1. Fault detection: How to determine whether there is a problem with the main library?

Automatically or manually confirming whether the main library is really downtime is the first step in the entire process. A common practice is to regularly detect the health status of the main library through a monitoring system, such as:

• Check if the MySQL process is running
• Try to establish a database connection
• Execute simple SQL queries (such as SELECT 1 )

Once it fails several times in a row, the main library is considered unavailable. It should be noted that network jitter may also lead to misjudgment, so a retry mechanism and timeout time are usually set to avoid unnecessary switching.

Some tools such as MHA, Orchestrator or Keepalived can be used for fault detection.

2. Choose a new main library: Who will take over?

After confirming that the master library fails, the next step is to select a suitable node from the existing slave library as the new master library.

Selection criteria generally include:

• Data synchronization progress (see Seconds_Behind_Master )
• Is there the latest binlog data
• Network latency and stability
• Instance load situation

Ideally, you should choose the slave library that is closest to the original master library, which can reduce the risk of data loss. If multiple slave libraries are lagging behind a lot, data compensation or manual intervention may be required.

MHA automatically tries to fill the logs, while Orchestrator can view the topology and make decisions through the API.

3. Switch to topology: Let other nodes know who the new master is

After determining the new master library, the next thing to do is to point the other slave library to the new master library and update the application layer configuration to ensure that the write requests arrive correctly.

Specific operations include:

• Execute RESET MASTER or enable GTID in the new main library
• Other slave libraries execute CHANGE MASTER TO to point to the new master
• Update VIP or DNS configuration to allow applications to switch transparently
• If you use a proxy (such as ProxySQL, MaxScale), you must also update its configuration

The key to this step is to try to be "seamless" and avoid business interruptions. Some solutions will also simplify the switching logic in combination with virtual IP or service discovery mechanism.

4. Application layer cooperation: Don't forget the client

Even if the switching is completed at the database level, if the application does not perceive changes in time, a connection error will still occur.

To deal with this problem, the following methods can be considered:

• Use connection pool middleware to support automatic reconnection and read-write separation
• Add retry logic and exception handling to the client code
• In conjunction with the DNS cache refresh strategy, ensure that the application gets the latest address

For example, using HikariCP or MyBatis from Spring Boot, you can set the connection timeout and maximum number of retries to improve fault tolerance.

Basically these are the steps. Although the overall process does not seem complicated, there are many details in the actual deployment that are easy to ignore, such as replication delay, GTID consistency, permission configuration and other issues. It is recommended to do more drills before it is officially launched to ensure that the failover can be executed smoothly.

The above is the detailed content of What is a typical process for MySQL master failover?. For more information, please follow other related articles on the PHP Chinese website!