Last updated: 21 Feb, 2010
Database replication is an option that allows the content of one database to be replicated to another database or databases, providing a mechanism to scale out the database. Scaling out the database allows more activities to be processed and more users to access the database by running multiple copies of the databases on different machines.
The problem with monolithic database designs is that they don’t establish an infrastructure that allows for rapid changes in business requirements. Here is where database replication comes into play. Replication can be used effectively for many different purposes, such as separating data entry and reporting, distributing load across servers, providing high availability, etc.
Zf_Orm_DataSource is a Zend Framework Replication Adapter class flexible enough to support the most commonly used replication scenarios:
Single-Master Replication
In the simplest replication scenario, the master copy of directory data is held in a single read-write replica on one server called the supplier server. The supplier server also maintains changelog for this replica. On another server, called the consumer server, there can be multiple read-only replicas.
Configuration array:
$config = array(
'adapter' => 'Pdo_Mysql',
'driver_options' => array(PDO::ATTR_TIMEOUT=>5),
'username' => 'root',
'password' => 'root',
'dbname' => 'test',
'master_servers' => 1,
'servers' => array(
array('host' => 'db.master-1.com'),
array('host' => 'db.slave-1.com'),
array('host' => 'db.slave-2.com')
)
);
// or ...
$config = array(
'adapter' => 'Pdo_Mysql',
'driver_options' => array(PDO::ATTR_TIMEOUT=>5),
'dbname' => 'test',
'master_servers' => 1,
'servers' => array(
array('host' => 'db.master-1.com', 'username' => 'user1', 'password'=>'pass1'),
array('host' => 'db.slave-1.com', 'username' => 'user2', 'password' => 'pass2'),
array('host' => 'db.slave-2.com', 'username' => 'user3', 'password' => 'pass3')
)
);
In the setup above, all writes will go to the master connection and all reads will be randomly distributed across the available slaves.
Multi-Master Replication
This type of configuration can work with any number of consumer servers. Each consumer server holds a read-only replica. The consumers can receive updates from all the suppliers. The consumers also have referrals defined for all the suppliers to forward any update requests that the consumers receive.
$config = array(
'adapter' => 'Pdo_Mysql',
'driver_options' => array(PDO::ATTR_TIMEOUT=>5),
'username' => 'root',
'password' => 'root',
'dbname' => 'test',
'master_servers' => 2,
'master_read' => true,
'servers' => array(
array('host' => 'db.master-1.com'),
array('host' => 'db.master-2.com')
)
);
Using a distributed memory caching system
Database connections are expensive and it’s very inefficient for an application to try to connect to a server that is down or not responding. A distributed memory caching system can help alleviate this problem by keeping a list of all the failed connections in memory, sharing that information across multiple servers and allowing the application to access it before attempting to open a connection.
To enable this option, you have to pass an instance of the Memcached adapter class:
class Bootstrap extends Zend_Application_Bootstrap_Base
{
protected function _initCache()
{
...
}
protected function _initDatabase()
{
$config = include APPLICATION_PATH . '/config/database.php';
$cache = $this->getResource('cache');
$dataSource = new Zf_Orm_DataSource($config, $cache, 'cache_tag');
Zend_Registry::set('dataSource', $dataSource);
}
}
And here is a short example of how the Replication Adapter might be used in a ZF application:
class TestDao
{
public function fetchAll()
{
$db = Zend_Registry::get('dataSource')->getConnection('slave');
$query = $db->select()->from('test');
return $db->fetchAll($query);
}
public function insert($data)
{
$db = Zend_Registry::get('dataSource')->getConnection('master');
$db->insert('test', $data);
return $db->lastInsertId();
}
}
Source Code:
https://github.com/fedecarg/zf-replication-adapter
Federico Cargnelutti 
15 responses to “Database Replication Adapter for Zend Framework Applications”
Hi Federico,
it is great to have such a flexible way of pointing the application to access the desired database instance and I am sure it will find its use in enterprise applications that use replication.
But when it comes to flexibility, MySQL replication engine itself beats them all as it allows almost all possible configurations you may think of. Except one.
Well, multi-master replication is referred to as a configuration in which one slave receives updates from more than one master database. While various replication topologies are possible, MySQL does not support multi-master replication out of the box. It is possible by manual or script-controlled switching from one master to another after certain amount of time, but definitely not automatically by MySQL replication engine.
It is also unclear to me why would a replica, or consumer, receive an update request. If it is read-only, it should never receive an update request.
From a semantic point of view,
– A master instance is not a replica. It is the original, while slave databases, being read-only, are copies, or replicas.
– A database will be read-only as long as the user connecting to it has read-only rights.
Been into MySQL replication a lot lately :)
Hi Sasa,
That’s right. Suppliers (masters) are not replicas. Consumers (slaves) are read-only replicas and they can receive updates from one or more Suppliers.
Are you saying that MySQL desn’t allow you to have 2 or more Suppliers + 2 or more Consumers? What about circular replication with 2 or more nodes? Each node is master of the following node and slave of the preceding one. Circular replication allows you to add more nodes into the mix.
Hi Federico,
that’s right. MySQL replication is configured on the slave database using CHANGE MASTER statement, which cannot accept parameters for an array of masters, but only for a single one.
A switch to another master can be done manually using the same statement. Obviously, at the moment the slave is configured to receive updates from the second master, it loses connectivity with the first one.
True, it requires human intervention. And in some cases divine intervention :)
Here’s more info on how to recover from a failover in a multi-master MySQL Cluster replication setup: http://bit.ly/4F5gMn
Thanks Sasa
Thank you! It’s very actual information for me.
For those who are new to database replication, I recommend using a data sharing solution, such as DRBD (Distributed Replicated Block Device), and Heartbeat to increase reliability and availability. Heartbeat manages the switching between servers in the event of a failure.
If you are using AmazonWS, keep in mind that EBS is not the same as DRBD.
More info:
http://dev.mysql.com/doc/refman/5.1/en/ha-heartbeat-drbd.html
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd.html
[…] a recent post on his blog Federico Cargnuletti presents his implementation of Zend_Db replication adapter. It supports single-master and multi-master architectures, as well as connection status caching. […]
nice tut! congrats
[…] https://blog.fedecarg.com/2009/10/02/database-replication-adapter-for-zend-framework-applications/ a few seconds ago from web […]
[…] PHP n’est pas un bon langage de templates – Federico Cargnelutti propose une idée de database replication adapter pour Zend Framework pour gérer les connexions à vos DB dans le cas de réplications single-master […]
Thank you! It’s very actual information for me.
Hi Federico, I stumbled upon your blog here and is very interested to try it out.
How do I actually install this? or to call it?
We are currently having only a single database and we are now wanting to move into this replication without the need to revamp the whole codes.
Thank you so much!
Hi ,
I am not able to get the class here
“http://fedecarg.com/repositories/show/replicationadapter”..
Please let me know where i can find the Zf_Orm_DataSource class
It’s now hosted on GitHub:
https://github.com/fedecarg/zf-replication-adapter
Thank you Federico..
Is there news regarding the Database Replication Adapter component in the future versions of ZF i.e 2.0..
Please let me know if you have any info on this…