asterisk users - Dec 2006 - Realtime +Mysql +Failover

Hoping someone out there has run into this or has some ideas for us.

We currently have asterisk set up with Realtime (using mysql) for its
extensions,sip and voicemail files.

The problem we are trying to solve, is one of a failover mechanism. What
if our mysql server went down.

Can Realtime be set up with a secondary mysql server to get its data
from. We can set up mysql to sync with its fellow server, and maybe when
it goes down, it couldn't make any changes (write), but either way, you
could still get the extension info, etc, so your phones would still
survive a mysql outage.

Any ideas?
Thanks,
Rob

On 12/13/06, Rob Schall <rschall@callone.net>
wrote:
> Hoping someone out there has run into this or has some ideas for us.
>
> We currently have asterisk set up with Realtime (using mysql) for its
> extensions,sip and voicemail files.
>
> The problem we are trying to solve, is one of a failover mechanism. What
> if our mysql server went down.
>
> Can Realtime be set up with a secondary mysql server to get its data
> from. We can set up mysql to sync with its fellow server, and maybe when
> it goes down, it couldn't make any changes (write), but either way, you
> could still get the extension info, etc, so your phones would still
> survive a mysql outage.
>
> Any ideas?
> Thanks,
> Rob
I don't think Realtime can be setup with a secondary server (someone
please correct me if I'm wrong).

Two possibilities come to mind...

1. You can run MySQL in an HA arangement with on box as the hot standby.
2. If you can allow for ocassional asterisk reloads, you could use
Realtime Static

Regards,
David

> > Can Realtime be set up with a secondary mysql server to get its data
> > from. We can set up mysql to sync with its fellow server, and maybe
when
> > it goes down, it couldn't make any changes (write), but either
way, you
> > could still get the extension info, etc, so your phones would still
> > survive a mysql outage.
I run MySQL Replication from a Master MySQL Server (only runs the
database function).  Apply patch
http://bugs.digium.com/view.php?id=5881to the various asterisk servers
and configure them as slaves to the master.  Write only to the Master
database and all the slaves update automatically, in realtime.  Have
the PBX's write to the Master and read from 127.0.0.1(localhost), if a
PBX goes down, it doesn't affect database operations with the rest of
the cluster.  Also if the Master goes down, all the PBX's can still
read from their local database.

snip from res_mysql.conf

[general]
dbname=asterisk
dbuser=asterisk
dbpass=asterisk
dbport = 3306
dbsock = /tmp/mysql.sock

[read]
dbhost = 127.0.0.1

[write]
dbhost = 10.10.10.10


For MySQL redundancy, have a second MySQL server running in a
Master-to-Master replication with the first Master.   There is your
backup if one goes down, switch IP or run LVS or Heatbeat between the
two Master MySQL servers.

This setup, IMHO, is less costly than a MySQL Cluster (due to less
servers needed to implement), is easier to configure and get running,
easier to recover during a failure, greatly increases realtime
performance on the PBX (reading from localhost than from across a
network), scales very well (15-20 slaves for each Master).  Also MySQL
replication is more mature that MySQL clustering (I'm using 3.23, not
5.0 which is needed for clustering) so the software footprint is
smaller comparatively).

Hope this helps.

JR Richardson
Engineering for the Masses