What problem does this PR solve?

Issue Number: close #11475

What is changed and how it works?

Description:

This PR introduces a new feature in TiCDC to enhance the fault tolerance of Changefeeds that target MySQL-compatible downstream databases. Previously, TiCDC could only connect to one MySQL-compatible server in a downstream cluster, and if that server became unavailable, it required manual intervention (i.e., recreating the Changefeed) to restore functionality.

New Feature:

Automatic Failover for Multiple Downstream Addresses:

• Background: In many deployment scenarios, a load balancer is often used to manage high availability for database clusters. However, this can introduce a single point of failure and additional complexity. To simplify deployment and enhance robustness, TiCDC now natively supports specifying multiple MySQL-compatible downstream addresses in the --sink-uri option during Changefeed creation or update.
• Functionality: When a downstream database server becomes unavailable, TiCDC will automatically attempt to switch to another available server from the list of provided addresses, ensuring the continuity of the Changefeed without needing user intervention. This adds a layer of redundancy, making TiCDC more resilient in environments where load balancers may not be feasible.

Key Additions and Changes:

1. DBConnector Implementation:

   • Purpose: Manages the connection to MySQL-compatible databases and handles automatic reconnection and failover in case of a server failure. It works by rotating through a list of DSNs (Data Source Names) in a round-robin fashion to find an available database server.
   • Code Location: The DBConnector struct and methods have been added in the pkg/sink/mysql package. The core methods include:
     • SwitchToAnAvailableDB: Automatically tries to switch to another available database in the event of a failure.
     • ConfigureDBWhenSwitch: Allows custom configuration logic to be applied when switching to a new connection.

2. Integration into Existing Components:

   • Replaced all instances of direct MySQL connection logic with the DBConnector. This includes:
     • DDL Sink
     • DML Sink
     • Observer
     • Syncpoint Store
   • By using DBConnector, these components now benefit from automatic failover, making TiCDC more resilient in the face of database outages.

3. Unit Testing:

   • Thorough unit tests have been added for the DBConnector to ensure it handles reconnection and failover logic correctly. These tests can be found in pkg/sink/mysql/mysql_connector_test.go.

4. Integration Testing:

   • Updated the TiCDC integration tests to verify that Changefeeds can handle multiple downstream addresses. The integration tests cover scenarios where downstream database servers become unavailable, and TiCDC successfully switches to another available server.
   • New Script: Added a script start_downstream_tidb_instances to start multiple TiDB instances for testing the failover functionality. The test_prepare file has been updated to register three downstream TiDB instance ports for these tests. While the integration tests now support up to three TiDB instances by default, more instances can be added if needed by modifying the registered ports.

5. Minor Changes to Existing Scripts:

   • The ports for downstream TiDB instances in the integration test scripts (run.sh) have been modified to accommodate the new multi-instance setup. These changes are purely related to port assignments and do not alter the test logic.

Testing and Validation:

• Unit Tests: Added detailed unit tests for DBConnector.
• Integration Tests: Tested cdc cli changefeed create and cdc cli changefeed update with multiple downstream addresses. Verified that TiCDC correctly switches between downstream instances during failures with multiple TiDB instances to confirm the automatic failover functionality works as expected.

This enhancement greatly improves TiCDC’s reliability and ease of use, especially in complex deployment environments, by reducing dependency on external load balancers and ensuring smooth failover between multiple downstream MySQL-compatible databases.

Check List

Tests

• Unit test
• Integration test

Questions

Will it cause performance regression or break compatibility?

No

Do you need to update user documentation, design documentation or monitoring documentation?

Yes, the user documentation should be updated to reflect the new support for specifying multiple downstream addresses in the --sink-uri option, along with instructions on how to configure and use this feature. Additionally, any design documentation that explains the architecture of TiCDC's database connection management should be updated to include details on the new DBConnector and its failover capabilities. Monitoring documentation should also be updated to account for the behavior and health of multiple downstream connections, including potential alerts when failover occurs.

Release note

Support automatic failover across multiple MySQL-compatible downstream addresses in the `--sink-uri`, ensuring high availability and improved fault tolerance.