[asterisk-dev] Multiple MySQL Database ‘Syncing’ Within an Asterisk Cluster

[Joseph Benden]

Hello,

A reader/writer layer would need applied. In it you would account for a
load-balancing method (random sort and pluck the top, or round robin,
least used, etc.) for the readers and a single master.  On a reader
level, the status would need polled such that you don't continue to use
a down server.  A background thread would need to check downed readers
to see if they are back online. (ping, for instance)

The writer would need to spool failed queries for future injection.  You
wouldn't want to 'hang' Asterisk with a downed master server.

And ideally, this would be a generic database layer, such that other
parts of Asterisk could reuse the same logic - or even a C library so
that other software could use the same (PHP frontend for billing or
provisioning, for instance.)

This would be the easiest method.  The most complex would be multiple
readers and writers which would entail spooling on a per server level
when a query fails, so that when it's online it could accept the queries
not yet processed. And it goes without saying that everything falls
apart if the same library isn't used by all parts.  And, if the machine
dies and the spool files are lost, then you could have out-of-sync
issues.

-Joe
Thralling Penguin LLC


On Thu, 2006-07-06 at 10:24 -0500, JR Richardson wrote:

> Just need to bounce this off of you guys, help get my mind right………….
> 
> Using the ARA gives the ability for multiple PBX's to pull info from a
> MySQL database cluster.  Clustering MySQL is pretty simple when using
> mysql master/slave database replication and distributing the database
> lookups across the database replication cluster.
> 
> This gives good database HA for MySQL but I'm running into an issue
> specifically with the PBX-to-database write operation then followed by
> the database replication.
> 
> MySQL server layout:
> Master id:1 provisioning front end, human interaction
> Slave1 id:2 hosting database for pbx 1, 2, 3
> Slave2 id:3 hosting database for pbx 4, 5, 6
> Slave3 id:4 hosting database for pbx 7, 8, 9
> 
> When we make changes to the Master, changes are immediately replicated
> to all the slaves and the PBX's perform accordingly.
> 
> When PBX 1 registers a phone and updates the "full contact info" to
> database Slave1 for that UA, we now need a sync operation from Save1
> back to the database Master so the Slave2 and Slave3 remain in sync.
> 
> There are database sync utilities with MySQL but are accessed by
> manually running the utilities.  I'm looking for an automated solution
> that will keep all these databases in 'sync' rather than just
> replicated from one master.
> 
> Can ARA accomplish this by separating the read and write operations,
> configuring the write operation to the Master database and configuring
> the read operation to pull from another database?  This functionality
> would need to be added to sip/iax users/peer operation, something
> like:
> 
> sip.conf
> 
> readdbname=slave1
> readdbhost=10.10.10.11
> readdbuser=asteriskdb
> readdbpass=asteriskdbpassword
> 
> writedbname=master
> writedbhost=10.10.10.10
> writedbuser=asteriskdb
> writedbpass=asteriskdbpassword
> 
> Any thoughts or ideas on how to accomplish will be greatly appreciated.
> 
> Thanks.
> 
> JR
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-dev/attachments/20060706/7056b009/attachment.htm