Asterisk Realtime with MySQL.

This is a How-to for the beginners to work Realtime Asterisk with MySQL.

Getting Asterisk-addons.
-----------------------------------------
1. Download asterisk-addons tar from the asterisk.org site (ftp://ftp1.digium.com/pub/asterisk/releases/asterisk-addons-1.2.3.tar.gz).
2. Untar it under /usr/src
3. cd /usr/src/asterisk-addons
4. make clean
5. make install
6. This installs the module for mysql, "res_config_mysql.so" into your asterisk modules dir.
7. Copy "res_mysql.conf.sample" from "/usr/src/asterisk-addons/configs" and place it under "/etc/asterisk/"
8. Rename "res_mysql.conf.sample" it to "res_mysql.conf"

Creating database in MySQL.
----------------------------------------
I have created a database by name "test" with two tables "sip_buddies" and "extensions"

Create table "sip_buddies" using:
-----------------------------------------
CREATE TABLE `sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`accountcode` varchar(20) default NULL,
`amaflags` varchar(13) default NULL,
`callgroup` varchar(10) default NULL,
`callerid` varchar(80) default NULL,
`canreinvite` char(3) default 'yes',
`context` varchar(80) default NULL,
`defaultip` varchar(15) default NULL,
`dtmfmode` varchar(7) default NULL,
`fromuser` varchar(80) default NULL,
`fromdomain` varchar(80) default NULL,
`fullcontact` varchar(80) default NULL,
`host` varchar(31) NOT NULL default '',
`insecure` varchar(4) default NULL,
`language` char(2) default NULL,
`mailbox` varchar(50) default NULL,
`md5secret` varchar(80) default NULL,
`nat` varchar(5) NOT NULL default 'no',
`deny` varchar(95) default NULL,
`permit` varchar(95) default NULL,
`mask` varchar(95) default NULL,
`pickupgroup` varchar(10) default NULL,
`port` varchar(5) NOT NULL default '',
`qualify` char(3) default NULL,
`restrictcid` char(1) default NULL,
`rtptimeout` char(3) default NULL,
`rtpholdtimeout` char(3) default NULL,
`secret` varchar(80) default NULL,
`type` varchar(6) NOT NULL default 'friend',
`username` varchar(80) NOT NULL default '',
`disallow` varchar(100) default 'all',
`allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
`musiconhold` varchar(100) default NULL,
`regseconds` int(11) NOT NULL default '0',
`ipaddr` varchar(15) NOT NULL default '',
`regexten` varchar(80) NOT NULL default '',
`cancallforward` char(3) default 'yes',
`setvar` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) TYPE=MyISAM ROW_FORMAT=DYNAMIC;

Create table "extensions" using:
---------------------------------------
CREATE TABLE `extensions` (
`id` int(11) NOT NULL auto_increment,
`context` varchar(20) NOT NULL default '',
`exten` varchar(20) NOT NULL default '',
`priority` tinyint(4) NOT NULL default '0',
`app` varchar(20) NOT NULL default '',
`appdata` varchar(128) NOT NULL default '',
PRIMARY KEY (`context`,`exten`,`priority`),
KEY `id` (`id`)
) TYPE=MyISAM;

Add some data into them, no need to enter all the details in sip_buddies, add only the necessary fields.

I have added a context like

name = 102
canreinvite = no
context = default
dtmfmode = rfc2833
host = dynamic
port= yes
type = friend
username = 102
secret = 102

(if u r getting error in uploadin data, change the field type from NOT NULL to NULL)

Add some data into extensions table also.

500 default VoiceMailMain
123 default Playback welcome

Configuration in res_mysql.conf.
-------------------------------------------
[general]
dbhost = localhost
dbname = test
dbuser = username
dbpass = password
dbport = 3306
dbsock = /tmp/mysql.sock

(check the mysql.sock path, it could be /var/lib/mysql/mysql.sock)

Configuration in extconfig.conf.
--------------------------------------------
sipusers => mysql,test,sip_buddies
sippeers => mysql,test,sip_buddies
extensions => mysql,test,extensions

Configuration in sip.conf.
--------------------------------------------
[general]

rtcachefriends=yes

Configuration in extensions.conf.
--------------------------------------------
[default]

switch => Realtime/default@extensions

Start the asterisk,
--------------------------------------------
In the CLI mode,
--------------------------------------------
CLI> realtime mysql status

This shows the status of your mysql connection, like

"Connected to test@localhost, port 3306 with username root for 46 minutes, 30 seconds"

Anand Kumar Dayal
akdayal@yahoo.com