Asterisk Realtime with MySQL

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

Getting Asterisk-addons.

---

1. Download asterisk-addons tar ball from the asterisk.org site.
2. Untar it under /usr/src
3. cd /usr/src/asterisk-addons
4. make
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 PRIMARY KEY, `name` varchar(80) NOT NULL UNIQUE KEY 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 ,);

Create table "extensions" using:

---

CREATE TABLE `extensions` ( `id` int(11) NOT NULL auto_increment primary key, `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) ;

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

 

进入MySQL的控制台：insert into sip_buddies(name,canreinvite,context,dtmfmode,host,port,type,username,secret) values('102','no','default','rfc2833','dynamic','yes','friend','102','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 = voice

 dbuser = username

 dbpass = password

dbport = 3306

dbsock = /var/lib/mysql/mysql.sock

Configuration in extconfig.conf.

---

sipusers => mysql,voice,sip_buddies

sippeers => mysql,voice,sip_buddies

extensions => mysql,voice,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 voice@localhost, port 3306 with username root for 46 minutes, 9 seconds"

This confirms that u have successfully made the connection with mysql, try registering a softphone and dial 500, u will get the voicemail menu, and dialing 123 will playback welcome music file :)