数据库复制:PHP中的智能数据库选择?

I'm using PHP with a replicated MySQL database.

I want to do some database load balancing. Currently I have the rather crude:

$ran = rand(1,10);
if ($ran < 5) {
  $db = '10.0.0.2:3306';
} else {
  $db = '10.0.0.3:3306';
}
// connect to the database
$con = mysql_connect($db,'elastic1','oag4Chai')
    or die('Could not connect to the server!');

But obviously if one of the databases is down, then our web pages will show error messages half the time.

Is there a more robust way to do this - i.e. check if the database is up before connecting?

It could be just a try/catch statement I guess, or I could do some more sophisticated load balancing - suggestions gratefully received.

Its not the web page, that shows the errors, its you

die('Could not connect to the server!')

die() has nothing to do in "real code" nowadays.

$ran = rand(1,10);
if ($ran < 5) {
  $db = '10.0.0.2:3306';
} else {
  $db = '10.0.0.3:3306';
}
// connect to the database
$con = mysql_connect($db,'elastic1','oag4Chai');
if (!$con) { /* Connect to the other one */ }

It's pretty rudimentary... but if your database server is brought online and taken offline in some sort of automated process, I would have that process create/delete some sort of lock file on the web server at a certain location. And simply have the script check whether or not the lock file exists or not before making a decision as to which DB server to send the user to. This will be less overhead that making a network request to the DB server to check if it's online or not every single time you want to make a request.

In the case where your DB server actually crashes without your knowledge and your lock file still indicates that the server is online, then you are out of luck and will probably run into some problems. Perhaps a cronjob every minute to test if the DB server is online or not and then manipulate the lock file accordingly.

First off – personally I think this is not a good idea for a PHP script.

If you can afford it, I'd look into the following:

  • monitor availability of servers (snmp, nagios?)
  • use round-robin DNS for your read-slaves (low TTL, easy to update when one goes down)
  • look into mysql-proxy and update its config based on monitoring

If you want a PHP-solution, I'd implement a circuit breaker pattern:

  1. Async process checks availability of MySQL servers (e.g. it mysqli_connect()'s). This could be a script run via cron (every minute or at shorter intervals).
  2. Status of this process is saved into a cache (single-server: APC, multiple: memcached or redis)
  3. Your PHP code checks cache first and then connects. You can randomize (or use an algorithm based on session ID or user ID, etc.) across available servers further if you want to.

I personally like the DNS idea best.

You should consider using mysqlnd_ms - PHP's replication and load balancing plugin.

I think this is better solution than mysql proxy, especially for a production environment, since it's native to PHP and mysql proxy is still in Alpha release.

Useful links: