MySQL Failover in Dialplan

Synopsis

Addressing MySQL Database failover in the Asterisk Dialplan

Introduction

HA installations require failover in event of a database failure. Included here are some dial plan configurations that can facilitate switchover inside of the dialplan.

Extensions.conf

[globals]
DBDefaultHost=192.168.170.21
DBBackupHost=192.168.170.22
DBCurrentHost=192.168.170.21
DBname=astdb
DBuser=root
DBpass=stellance

[macro-db-connect]
exten => s,1,MYSQL(Connect connid ${DBCurrentHost} ${DBuser} ${DBpass} ${DBname})
exten => s,n,GotoIf($["${connid}" = ""]?changedb)
exten => s,n,MacroExit
exten => s,n(changedb),NoOp("Switching DB Servers")
exten => s,n,GotoIf($["${DBCurrentHost}" = "${DBDefaultHost}"]?backup)
exten => s,n,Set(DBCurrentHost=${DBDefaultHost}|g)
exten => s,n,Macro(write-cdr_mysql)
exten => s,n,MacroExit
exten => s,n(backup),Set(DBCurrentHost=${DBBackupHost}|g)
exten => s,n,Macro(write-cdr_mysql)
exten => s,n,MacroExit

[macro-write-cdr_mysql]
exten => s,1,System(/bin/echo "'[global]'" > /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'hostname=${DBCurrentHost}'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'dbname=astdb'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'table=cdr'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'password=stellance'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'user=root'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'port=3306'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'sock=/etc/asterisk/mysql.sock'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'userfield=1'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,Wait(1)
exten => s,n,System(asterisk -r -x "reload cdr_addon_mysql.so")

Improvements and Explanations

The initial connection will still fail, however the connection macro can be modified to try to connect to the backup database. You will have to be wary of loops.


See also

Synopsis

Addressing MySQL Database failover in the Asterisk Dialplan

Introduction

HA installations require failover in event of a database failure. Included here are some dial plan configurations that can facilitate switchover inside of the dialplan.

Extensions.conf

[globals]
DBDefaultHost=192.168.170.21
DBBackupHost=192.168.170.22
DBCurrentHost=192.168.170.21
DBname=astdb
DBuser=root
DBpass=stellance

[macro-db-connect]
exten => s,1,MYSQL(Connect connid ${DBCurrentHost} ${DBuser} ${DBpass} ${DBname})
exten => s,n,GotoIf($["${connid}" = ""]?changedb)
exten => s,n,MacroExit
exten => s,n(changedb),NoOp("Switching DB Servers")
exten => s,n,GotoIf($["${DBCurrentHost}" = "${DBDefaultHost}"]?backup)
exten => s,n,Set(DBCurrentHost=${DBDefaultHost}|g)
exten => s,n,Macro(write-cdr_mysql)
exten => s,n,MacroExit
exten => s,n(backup),Set(DBCurrentHost=${DBBackupHost}|g)
exten => s,n,Macro(write-cdr_mysql)
exten => s,n,MacroExit

[macro-write-cdr_mysql]
exten => s,1,System(/bin/echo "'[global]'" > /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'hostname=${DBCurrentHost}'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'dbname=astdb'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'table=cdr'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'password=stellance'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'user=root'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'port=3306'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'sock=/etc/asterisk/mysql.sock'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,System(/bin/echo "'userfield=1'" >> /etc/asterisk/cdr_mysql.conf)
exten => s,n,Wait(1)
exten => s,n,System(asterisk -r -x "reload cdr_addon_mysql.so")

Improvements and Explanations

The initial connection will still fail, however the connection macro can be modified to try to connect to the backup database. You will have to be wary of loops.


See also

Created by: stellance, Last modification: Thu 26 of Oct, 2006 (23:24 UTC)
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+