Tag: mysql

PHP Database class for master/slave mysql cluster

Simone Caruso/ / PHP

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;
	}
}
Share