MySQL Asynchronous Connection Failover Prerequisites

Setting up asynchronous connection failover in MySQL ensures high availability by automatically switching replica connections to a new source if the primary source fails. This article outlines the essential system requirements, configuration settings, and user privileges needed to successfully implement this failover mechanism in your MySQL replication environment.

MySQL Version Requirements

• Minimum Version: You must be running MySQL 8.0.22 or higher. The asynchronous connection failover mechanism was introduced in this version.
• Group Replication Support: If you plan to use this feature with Group Replication (to automatically track membership changes), MySQL 8.0.23 or higher is highly recommended to support member weight and status tracking.

Replication and GTID Configuration

• GTID-Based Replication: Global Transaction Identifiers (GTIDs) must be enabled on both the source and replica servers. Configure the following settings in your my.cnf or my.ini files:

  gtid_mode = ON
  enforce_gtid_consistency = ON

• Active Replica: The replica must have an active replication channel configured.

Performance Schema Requirements

The failover mechanism relies on the MySQL Performance Schema to monitor connection states and manage the list of potential source servers. * Performance Schema Enabled: Ensure that the Performance Schema is enabled on the replica: ini performance_schema = ON

Administrative Privileges

To configure and manage the source sender list for failover, the database user requires specific privileges: * Replica Administration: The user executing the configuration functions must have the SUPER privilege, or the REPLICATION_SLAVE_ADMIN privilege (available in MySQL 8.0 and above). * UDF Execution: Privileges to execute User-Defined Functions (UDFs) such as asynchronous_connection_failover_add_source and asynchronous_connection_failover_delete_source.

Network and Security Settings

• Network Connectivity: The replica must have unrestricted network access to all potential source servers listed in the failover configuration.
• Consistent Credentials: The replication user account used by the replica must exist on all potential sources with the exact same username, password, and replication privileges.