Laravel应用适配MySQL Group Replication高可用架构的问题咨询

Nice question—let’s walk through how to ensure your Laravel app stays highly available when using MySQL Group Replication (MGR) in Single-Primary mode, even if the primary node goes down. Here’s a practical, step-by-step breakdown of the key pieces you need:

First off, MySQL Group Replication in Single-Primary mode has native automatic failover capabilities. When the primary node fails (e.g., crashes, loses network connectivity), the cluster will automatically elect a new primary from the remaining healthy secondary nodes.

To make sure this works reliably:

• Double-check that group_replication_single_primary_mode is set to ON on all nodes.
• Configure group_replication_autorejoin_tries (e.g., group_replication_autorejoin_tries=3) so a failed node will attempt to rejoin the cluster automatically once it’s back online.
• Keep your base MySQL config solid: unique server_id per node, gtid_mode=ON, and proper replication user permissions.

Once failover completes, the new primary will set read_only=OFF (to accept writes), and the other secondaries stay in read_only=ON.

Your load balancer is the gateway between Laravel and MySQL, so it needs to know which node is the current primary to route write traffic correctly.

Here’s how to set this up:

• Implement a health check that runs SELECT @@global.read_only on each node. The primary returns 0 (not read-only), while secondaries return 1.
• Configure the load balancer to send all write traffic to the node with read_only=0. For read traffic, split it across secondaries (and the primary, if needed) to spread load.
• Set short health check intervals (2-5 seconds) so the load balancer detects a primary failure quickly. Also, configure connection timeouts and retries to avoid hanging requests from Laravel.

Laravel needs to handle brief connectivity blips during failover without crashing. Adjust your database config to boost resilience:

• In your .env file, set DB_HOST to your load balancer’s address (not a single MySQL node). This way, Laravel doesn’t need to know which node is primary—it just talks to the load balancer.
• Add resilience options to config/database.php under the MySQL connection:

  'mysql' => [
      // ... other existing config
      'options' => [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_TIMEOUT => 3, // Short timeout to detect failures fast
          PDO::ATTR_PERSISTENT => false, // Avoid stale connections that don't reflect failover
      ],
  ]
  

• Use Laravel’s retry logic for critical database operations. For example, wrap write operations in a retry block to handle temporary connection issues during failover:

  use Illuminate\Support\Facades\DB;
  use Illuminate\Database\QueryException;
  
  $result = retry(3, function () {
      return DB::table('your_table')->insert($data);
  }, 100); // Wait 100ms between retries
  

• If you’re on Laravel 8 or newer, enable database connection pooling to reduce the overhead of re-establishing connections after failover.

Don’t wait for users to report issues—keep an eye on your MGR cluster:

• Regularly check the cluster status with SELECT * FROM performance_schema.replication_group_members; to verify all nodes are online and in the right state (PRIMARY/SECONDARY).
• Set up alerts for when a node goes into UNREACHABLE or OFFLINE state, or when a primary failover occurs. Tools like Prometheus + Grafana can pull metrics from MySQL and trigger alerts via email/Slack.
• Monitor replication lag on secondary nodes to ensure they’re keeping up with the primary—lag can cause read inconsistencies if you’re routing reads to secondaries.

When the original primary node comes back online, MGR will automatically set it to read_only=ON and make it a secondary node. But you should:

• Verify it’s successfully rejoined the cluster and is syncing data correctly.
• Check for any replication errors using SHOW SLAVE STATUS\G (or SELECT * FROM performance_schema.replication_applier_status; for GTID-based replication).
• If there are persistent sync issues, you might need to re-clone the node from the current primary to get it back in sync.

By combining these steps, you’ll have a robust setup that keeps your Laravel app running smoothly even when the primary MySQL node fails.

内容的提问来源于stack exchange，提问作者GKMelbo