Does PHP have a built in mechanism to failover from one database server to another?

Question

I found this:

http://www.evolt.org/failover-database-connection-with-php-mysql

and similar examples. But is there a better way?

I am thinking along the lines of the Automatic Failover Client in the MS SQL Native Client.

Answer 1

No, it does not have that build in. I think an easy way is to employ MySQL Proxy. Otherwise, but the logic (if (fails) { connect to another }) in your application, but that's not so pretty and great in terms of performance.

Answer 2

It's traditional to handle failover strategies at the system level; that way all applications can enjoy a robust environment.

I'd like to refer to MySQL failover strategy and MySQL proxy. The latter describes a MySQL utility that can do load balancing and failover, and seems quite easy to set up.

Not the answer to the question, but it is the more common solution to handle failover.

Answer 3

php has no built in failover mechanism - probable for a good reason. Such failover system should be implemented in the operating system or hardware level.

Anyways your sample provides a cheap and simple failover mechanism.

Answer 4

There's nothing built in to handle that in PHP. You have to write the code yourself.

The best approach would be to abstract your database access and create a class that can encapsulate the failover logic.

Here's some code, off the cuff:

interface MySQL_Interface {
    public function query($sql);
}

class MySQL_Concrete implements MySQL_Interface {
    public function __construct($host, $user, $pass, $dbname) {
        $this->_mysql = mysql_connect($host, $user, $pass) or throw Exception("Could not connect to server");
        mysql_select_db($db, $this->_mysql) or throw Exception("Could not connect to database");
    }
    public function query($sql) {
        return mysql_query($sql) or throw new Exception("Query failed");
    }
}

class MySQL_Failover implements MySQL_Interface {
    public function __construct(MySQL_Interface $one, MySQL_Interface $two) {
        $this->_one = $one;
        $this->_two = $two;
    }
    public function query($sql) {
        try {
            return $this->_one->query($sql);
        } catch (Exception $e) {
            return $this->_two->query($sql);
        }
    }
}

$db = new MySQL_Failover(
    new MySQL_Concrete('host1', 'user', 'pass', 'db'),
    new MySQL_Concrete('host2', 'user', 'pass', 'db')
);

$db->query('SELECT * FROM Users');

PS: I've left quite a bit out of here, it's not to be used verbatim.

*PPS: I'd probably be better to use an existing database abstraction library (Zend_Db, MDB2, ect) and implement their interface instead of creating your own ad-hoc solution*