Author: Simone Caruso

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

Build dovecot 2.0.1 debian packages

Simone Caruso/ / Linux

I really like the apt system, i often use it for my personal binary packages distribution between my systems and it’s great to install test systems in few minutes.

In august dovecot 2.0.1 was released, i’d have liked to test the new software on my lenny servers, but i could not find any .deb binary package online.. so i build it by myself.

Continue reading “Build dovecot 2.0.1 debian packages” »

Share