Asterisk Realtime 1.4.x with MySQL
First we need to install asterisk click here for a quick guide. We must configure Asterisk using its configuration files in /etc/asterisk/. One more problem we face in Asterisk configuration is that we need to reload Asterisk configuration by explicitly issuing a command in asterisk cli. We can get rid of these problems by using Asterisk Realtime. There are two types of configurations available in Asterisk Realtime "STATIC" and "REALTIME". STATIC configuration is used just to store configuration files in the database and you need to reload the settings every time you make changes on the other hand when we use REALTIME settings then all the settings are loaded on the fly.
The required components that you need before you work with Asterisk Realtime engine are Asterisk, MySQL server and libmysqlclient and finally Asterisk-Addons.
Once every thing is ready to go make sure that mysql server is up and running issue the following command
/etc/init.d/mysqld status
or
/etc./init.d/mysql status
You can start mysql server by issuing the following command
/etc/init.d/mysqld start
or
/etc/init.d/mysql start
now we need to create a database, login to mysql
mysql -u root -pyourpassword
change yourpassword with the root password. This will take you to mysql command line. You can also use phpmyadmin to create your databases. Now create a database:
CREATE DATABASE asterisk;
Now we will create tables for configuration files.
For SIP Users/Peers:
CREATE TABLE `asterisk`.`sip_buddies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`accountcode` varchar(20) default NULL,
`amaflags` varchar(7) 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(20) 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',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=MyISAM;
For extentions:
CREATE TABLE `asterisk`.`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`)
) ENGINE=MyISAM;
For Voice Mail Users:
CREATE TABLE `asterisk`.`voicemessages` (
`id` int(11) NOT NULL auto_increment,
`msgnum` int(11) NOT NULL default '0',
`dir` varchar(80) default '',
`context` varchar(80) default '',
`macrocontext` varchar(80) default '',
`callerid` varchar(40) default '',
`origtime` varchar(40) default '',
`duration` varchar(20) default '',
`mailboxuser` varchar(80) default '',
`mailboxcontext` varchar(80) default '',
`recording` longblob,
PRIMARY KEY (`id`),
KEY `dir` (`dir`)
) ENGINE=MyISAM;
For Queues:
CREATE TABLE `asterisk`.`queues` (
`name` varchar(128) NOT NULL,
`musiconhold` varchar(128) default NULL,
`announce` varchar(128) default NULL,
`context` varchar(128) default NULL,
`timeout` int(11) default NULL,
`monitor_type` varchar(50) NOT NULL,
`monitor_format` varchar(128) default NULL,
`queue_youarenext` varchar(128) default NULL,
`queue_thereare` varchar(128) default NULL,
`queue_callswaiting` varchar(128) default NULL,
`queue_holdtime` varchar(128) default NULL,
`queue_minutes` varchar(128) default NULL,
`queue_seconds` varchar(128) default NULL,
`queue_lessthan` varchar(128) default NULL,
`queue_thankyou` varchar(128) default NULL,
`queue_reporthold` varchar(128) default NULL,
`announce_frequency` int(11) default NULL,
`announce_round_seconds` int(11) default NULL,
`announce_holdtime` varchar(128) default NULL,
`retry` int(11) default NULL,
`wrapuptime` int(11) default NULL,
`maxlen` int(11) default NULL,
`servicelevel` int(11) default NULL,
`strategy` varchar(128) default NULL,
`joinempty` varchar(128) default NULL,
`leavewhenempty` varchar(128) default NULL,
`eventmemberstatus` varchar(4) default NULL,
`eventwhencalled` varchar(4) default NULL,
`reportholdtime` tinyint(1) default NULL,
`memberdelay` int(11) default NULL,
`weight` int(11) default NULL,
`timeoutrestart` tinyint(1) default NULL,
`periodic_announce` varchar(50) default NULL,
`periodic_announce_frequency` int(11) default NULL,
`ringinuse` tinyint(1) default NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM;
For Queue Members:
CREATE TABLE `asterisk`.`queue_members` (
`uniqueid` int(10) unsigned NOT NULL auto_increment,
`membername` varchar(40) default NULL,
`queue_name` varchar(128) default NULL,
`interface` varchar(128) default NULL,
`penalty` int(11) default NULL,
`paused` tinyint(1) default NULL,
PRIMARY KEY (`uniqueid`),
UNIQUE KEY `queue_interface` (`queue_name`,`interface`)
) ENGINE=MyISAM;
Now add the following lines to your asterisk configuration files so that it can connect to mysql server
vi /etc/asterisk/res_mysql.conf
This file should look something like:
[general]
dbhost = localhost
dbname = asterisk
dbuser = asterisk
dbpass = yourpassword
dbport = 3306
Next we need to tell asterisk which files to load from our database, for this we need to edit extconfig.conf. The syntax for this file is "configfile => db_type,db_name,table". Now we will direct all above tables to our database. Add these lines to /etc/asterisk/extconfig.conf
[settings]
sipusers => mysql,asterisk,sip_buddies
sippeers => mysql,asterisk,sip_buddies
extensions => mysql,asterisk,extensions
voicemail => mysql,asterisk,voicemessages
queues => mysql,asterisk,queues
queue_members => mysql,asterisk,queue_members
Now for every context in that should be loaded from mysql we need to direct that context to our database i.e.
[incoming]
switch => Realtime/@extensions
For every context just add "switch => Realtime/@extensions" underneath it. We are all set to go at this point. Restart your asterisk server once and check if asterisk is connected to your database by
asterisk -r
restart now
realtime mysql status
If everything went fine your status will show that you are connected to the database. Now time to run some sample configuration to check if every thing is fine. We will add two extensions:
INSERT into sip_buddies (id, name, callerid, context, canreinvite, insecure, type, host, secret, allow, nat)
VALUES ('','8000','Qasim','incoming','no','port,invite','friend','dynamic','secret1','all','yes');
INSERT into sip_buddies (id, name, callerid, context, canreinvite, insecure, type, host, secret, allow, nat)
VALUES ('','8001','Awais','incoming','no','port,invite','friend','dynamic','secret2','all','yes');
This will add two extensions for you to use i.e "8000" with password "secret1" and "8001" with password "secret2". Now we need to define extension so that they can call each other:
INSERT into extensions (id, context, exten, priority, app, appdata)
VALUES ('','incoming','8000','1','Dial','SIP/8000');
INSERT into extensions (id, context, exten, priority, app, appdata)
VALUES ('','incoming','8001','1','Dial','SIP/8001');