How to configure PHP PDO to connect to a multimaster cluster?

Question

I am using Percona XtraDB Cluster to load-balance database.

The usual approach to connect to the database is:

$db = new \PDO('mysql:dbname=foo;host=127.0.0.1');

However, there are multiple master, each of which may possibly be dead at any point in time.

How to establish a connection to a database cluster with load-balancing and safe fail-over?

Answer 1

My intuitive guess is as simple as:

<?php
$cluster = ['10.128.155.150', '10.128.155.151', '10.128.155.152'];

shuffle($cluster);

foreach ($cluster as $i => $ip) {
    try {
        $db = new \PDO('mysql:dbname=foo;host=' . $ip . ';charset=utf8');
        
        break;
    } catch (\PDOException $e) {
        if ($i === count($cluster)) {
            throw $e;
        }
    }
}

Though, I would like to know if there are any more bullet-proof solutions developed.

Edit 2013 May 3, 14:42. I was explained that this approach is bad simply because user will need to wait until the connection timesout. The right tool for the job is https://www.php.net/manual/en/intro.mysqlnd-mux.php.

Beware though, that:

The proof-of-concept does not support unbuffered queries, prepared statements, and asynchronous queries.

Edit 2013 May 3, 20:38. Ended up looking at the heartbeat and HAProxy.