Environment
My customer, in this case an ISP, has multiple dns servers was going to scale-out the DNS records database for multiple DNS servers.
The startup configuration was a master/slave, with DRBD failover MySQL cluster; the new configuration was accomplished with a MySQL master database and three servers as slaves, two for DNS servers queries and one for the public management interface; nothing of particularly complex.
The solution
The big work was rewriting the PHP application they was using for the DNS management; the objective was balancing SQL queries between the databases sending SELECTs on slaves and INSERT/UPDATE statements to the master. To accomplish the task i wrote a PHP Database class which redirect the queries to the right server before checking who is the master in case of failover.
I’m posting here the database class’s draft i wrote (without support for failover):
class Database { private $num_rows; private $master_dbh; private $slave_dbh; private $stmt; private $multislave = false; public function Database($dbname){ $this->KMVC =& get_instance(); if(!(isset($config->databases[$dbname]) && is_array($config->databases[$dbname]))) throw new Exception('Database '.$dbname.' does not exists'); $dsn = $config->databases[$dbname]['driver'].":host=".$config->databases[$dbname]['host'].";dbname=".$config->databases[$dbname]['database']; try{ $this->master_dbh = new PDO($dsn, $config->databases[$dbname]['user'], empty($config->databases[$dbname]['pass']) ? NULL : $config->databases[$dbname]['pass']); //$this->master_dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); }catch(Exception $e){ log("Error connecting: ". $e->getMessage().". Looking for slaves now"); } if(isset($config->databases[$dbname]['slaves']) && count($config->databases[$dbname]['slaves']) > 1){ $this->multislave = true; $rand = rand(0, count($config->databases[$dbname]['slaves']) - 1); $dbname = $config->databases[$dbname]['slaves'][$rand]; $slave_dns = $config->databases[$dbname]['driver'].":host=".$config->databases[$dbname]['host'].";dbname=".$config->databases[$dbname]['database']; $this->slave_dbh = new PDO($slave_dsn, $config->databases[$dbname]['user'], empty($config->databases[$dbname]['pass']) ? NULL : $config->databases[$dbname]['pass']); } } public function query($sql, $data = false){ if($this->multislave == true){ if(preg_match('/^select /i', $sql) > 0) $dbh =& $this->slave_dbh; else $dbh =& $this->master_dbh; }else $dbh =& $this->master_dbh; if(!$this->stmt = $dbh->prepare($sql)) throw new Exception(implode($this->stmt->errorInfo()), $stmt->errorCode()); if($data != false || is_null($data)){ $i = 0; if(is_array($data)) foreach($data as $row) $this->stmt->bindValue(++$i, $row); else $this->stmt->bindValue(++$i, $data); } if(!$this->stmt->execute()) if($config->debug) throw new Exception(implode(' - ', $this->stmt->errorInfo()), (int)$this->stmt->errorCode()); else throw new Exception("Error in query", (int)$this->stmt->errorCode()); $this->num_rows = $this->stmt->rowCount(); return true; } } |
