Jun 10

The old versions of proftpd dont support mysql md5 algorithm for password checks,
so for who have to use debian lenny with proftpd 1.3.1 the only way to make MD5 work is to use PAM-MySQL + libnss-mysql.

Continue reading “Proftpd with MySQL + MD5 users’ password on lenny” »


Sep 19

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 checks):

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