Database Replication Adapter for Zend Framework Applications

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

15 thoughts on “Database Replication Adapter for Zend Framework Applications

  1. 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 :)

  2. 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.

  3. 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.

  4. 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

  5. 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

  6. Pingback: Zend Framework University

  7. Pingback: Federico Cargnelutti (fedecarg) 's status on Thursday, 08-Oct-09 09:27:38 UTC - Identi.ca

  8. Pingback: Les liens du vendredi! | episode 2

  9. 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!

  10. 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

  11. 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s