MySQL instalation and Asterisk realtime configuration

André Guimarães 2011-07-20

Switzernet

This document describes how to install a MySQL server and how to configure Asterisk to read the phones from a MySQL database.

Installation

Access the server where you going to install asterisk. Log in as root.

All commands should be executed as root. To show this all commands are preceded by a hash (#).

Execute as root the following command (the result should be similar to the lines that follow it). This will install the mysql server and client, php5 and mysql modules (not mandatory for this procedure) and the mysql module for asterisk. During the following configuration a screen will appear asking for the root password for MySQL.

# apt-get install mysql-server mysql-client libmysqlclient15-dev php5 libapache2-mod-php5 php5-mysql php5-gd php5-cli asterisk-mysql

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following extra packages will be installed:

apache2-mpm-prefork apache2-utils apache2.2-common bsd-mailx defoma exim4 exim4-base exim4-config exim4-daemon-light fontconfig-config libapr1 libaprutil1 libdb4.5 libdbd-mysql-perl libdbi-perl libexpat1 libfontconfig1 libfreetype6

libgd2-xpm libhtml-template-perl libjpeg62 libmysqlclient15off libnet-daemon-perl libplrpc-perl libpng12-0 libsqlite3-0 libt1-5 libterm-readkey-perl libx11-6 libx11-data libxau6 libxcb-xlib0 libxcb1 libxdmcp6 libxpm4 mailx

mime-support mysql-client-5.0 mysql-common mysql-server-5.0 openssl-blacklist php5-common python python-minimal python2.5 python2.5-minimal ssl-cert ttf-dejavu ttf-dejavu-core ttf-dejavu-extra x11-common zlib1g-dev

Suggested packages:

apache2-doc apache2-suexec apache2-suexec-custom defoma-doc dfontmgr psfontmgr x-ttcidfont-conf mail-reader eximon4 exim4-doc-html exim4-doc-info libmail-spf-query-perl swaks php-pear dbishell libfreetype6-dev libgd-tools

libipc-sharedcache-perl tinyca python-doc python-tk python-profiler python2.5-doc binfmt-support

Recommended packages:

libft-perl

The following NEW packages will be installed

apache2-mpm-prefork apache2-utils apache2.2-common asterisk-mysql bsd-mailx defoma exim4 exim4-base exim4-config exim4-daemon-light fontconfig-config libapache2-mod-php5 libapr1 libaprutil1 libdb4.5 libdbd-mysql-perl libdbi-perl

libexpat1 libfontconfig1 libfreetype6 libgd2-xpm libhtml-template-perl libjpeg62 libmysqlclient15-dev libmysqlclient15off libnet-daemon-perl libplrpc-perl libpng12-0 libsqlite3-0 libt1-5 libterm-readkey-perl libx11-6 libx11-data

libxau6 libxcb-xlib0 libxcb1 libxdmcp6 libxpm4 mailx mime-support mysql-client mysql-client-5.0 mysql-common mysql-server mysql-server-5.0 openssl-blacklist php5 php5-cli php5-common php5-gd php5-mysql python python-minimal python2.5

python2.5-minimal ssl-cert ttf-dejavu ttf-dejavu-core ttf-dejavu-extra x11-common zlib1g-dev

0 upgraded, 61 newly installed, 0 to remove and 23 not upgraded.

Need to get 74.4MB of archives.

After this operation, 213MB of additional disk space will be used.

Do you want to continue [Y/n]? Y

...

Fetched 74.4MB in 11s (6234kB/s)

Extracting templates from packages: 100%

Preconfiguring packages ...

...

Setting up php5 (5.2.6.dfsg.1-1+lenny13) ...

Setting up php5-cli (5.2.6.dfsg.1-1+lenny13) ...

Creating config file /etc/php5/cli/php.ini with new version

Setting up php5-gd (5.2.6.dfsg.1-1+lenny13) ...

Setting up php5-mysql (5.2.6.dfsg.1-1+lenny13) ...

Setting up ssl-cert (1.0.23) ...

Setting up mailx (1:20071201-3) ...

Choose a secure password for mysql-server when asked:

Test the connection to MySQL as shown below:

# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 37

Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

+--------------------+

2 rows in set (0.00 sec)

mysql> \q

Bye

Verify if the asterisk mysql modules were correctly installed (the ones in /usr/lib/asterisk/modules):

# updatedb

# locate mysql.so

/usr/lib/asterisk/modules/app_addon_sql_mysql.so

/usr/lib/asterisk/modules/cdr_addon_mysql.so

/usr/lib/asterisk/modules/res_config_mysql.so

/usr/lib/perl5/auto/DBD/mysql/mysql.so

/usr/lib/php5/20060613/mysql.so

/usr/lib/php5/20060613/pdo_mysql.so

Access MySQL again and execute the following code replacing PASSWORD by the password that asterisk will use to access MySQL:

CREATE DATABASE asterisk;

GRANT INSERT, UPDATE, SELECT

ON asterisk.*

TO asteriskpeers@localhost

IDENTIFIED BY 'PASSWORD';

Before executing commands on MySQL execute the command BEGIN; as show below. If it shows any errors, write ROLLBACK; to disable every change since the BEGIN command. Type COMMIT; if there are no errors.

In the example we chose '758nm,.-fsg' as PASSWORD.

# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 37

Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE asterisk;

Query OK, 1 row affected (0.00 sec)

mysql>

mysql> GRANT INSERT, UPDATE, SELECT

-> ON asterisk.*

-> TO asteriskpeers@localhost

-> IDENTIFIED BY '758nm,.-fsg';

Query OK, 0 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

Change to the newly created asterisk database and create a table ‘sippeers’ where asterisk will authenticate the phones, using the SQL:

USE asterisk;

BEGIN;

# Table structure for table `sippeers`

CREATE TABLE `sippeers` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(80) NOT NULL default '',

`host` varchar(31) NOT NULL default '',

`nat` varchar(5) NOT NULL default 'no',

`type` enum('user','peer','friend') NOT NULL default 'friend',

`accountcode` varchar(20) default NULL,

`amaflags` varchar(13) default NULL,

`call-limit` smallint(5) unsigned default NULL,

`callgroup` varchar(10) default NULL,

`callerid` varchar(80) default NULL,

`cancallforward` char(3) default 'yes',

`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,

`insecure` varchar(4) default NULL,

`language` char(2) default NULL,

`mailbox` varchar(50) default NULL,

`md5secret` varchar(80) default NULL,

`deny` varchar(95) default NULL,

`permit` varchar(95) default NULL,

`mask` varchar(95) default NULL,

`musiconhold` varchar(100) default NULL,

`pickupgroup` varchar(10) default NULL,

`qualify` char(3) default NULL,

`regexten` varchar(80) default NULL,

`restrictcid` char(3) default NULL,

`rtptimeout` char(3) default NULL,

`rtpholdtimeout` char(3) default NULL,

`secret` varchar(80) default NULL,

`setvar` varchar(100) default NULL,

`disallow` varchar(100) default 'all',

`allow` varchar(100) default 'gsm,ulaw,alaw',

`fullcontact` varchar(80) NOT NULL default '',

`ipaddr` varchar(15) NOT NULL default '',

`port` smallint(5) unsigned NOT NULL default '0',

`regserver` varchar(100) default NULL,

`regseconds` int(11) NOT NULL default '0',

`lastms` int(11) NOT NULL default '0',

`username` varchar(80) NOT NULL default '',

`defaultuser` varchar(80) NOT NULL default '',

`subscribecontext` varchar(80) default NULL,

`useragent` varchar(20) default NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`),

KEY `name_2` (`name`)

) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

COMMIT;

Test the connection to the new table ‘sippers’ using the user ‘asteriskpeers’ and its password. You should have permissions to connect, select and insert.

mysql asterisk -uasteriskpeers -p

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 43

Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from sippeers ;

Empty set (0.00 sec)

Insert at least two new phone accounts. Replace in the command below PHONELOGIN by the phone login account, PHONEPASS by its password and leave the other options as shown or customize them to your needs. This line assumes that the routing code for these phones is on the context routing that will be created.

INSERT INTO sippeers (name, secret, nat, type, context, host, qualify, allow) VALUES (' PHONELOGIN ', ' PHONEPASS ', 'yes', 'friend', 'routing', 'dynamic', 'no', 'ulaw,alaw,gsm');

Here are two examples that will be used in this configuration.

INSERT INTO sippeers (name, secret, nat, type, context, host, qualify, allow) VALUES ('phone1', 'bgs8905bgf.df', 'yes', 'friend', 'routing', 'dynamic', 'no', 'ulaw,alaw,gsm');

INSERT INTO sippeers (name, secret, nat, type, context, host, qualify, allow) VALUES ('phone2', 'bgs8df5bgf.df', 'yes', 'friend', 'routing', 'dynamic', 'no', 'ulaw,alaw,gsm');

Exit MySQL and edit /etc/asterisk/sip.conf and add the line rtcachefriends=yes in the correct place (inside the ‘[general]’ block). This line corrects a problem with real time peers that exists in Asterisk 1.4.21.2. Example sip.conf.

# cat /etc/asterisk/sip.conf

[general]

...

rtcachefriends=yes

...

Create the file /etc/asterisk/res_mysql.conf with the following content, replacing ASTERISK_DATABASE_NAME, ASTERISK_DATABASE_USER and ASTERISK_DATABASE_PASSWORD by the correct values. Change its owner to user asterisk and group asterisk.

[general]

dbhost = 127.0.0.1

dbname = ASTERISK_DATABASE_NAME

dbuser = ASTERISK_DATABASE_USER

dbpass = ASTERISK_DATABASE_PASSWORD

dbport = 3306

# chown asterisk.asterisk /etc/asterisk/res_mysql.conf

Add the following lines to /etc/asterisk/extconfig.conf inside ‘[settings]’:

sipusers => mysql,asterisk,sippeers

sippeers => mysql,asterisk,sippeers

Only the lines sipusers and sippers are relevant for the real time phone configuration.

Edit the file /etc/asterisk/extensions.conf and add the routing context if it doesn’t exist or add the following lines to the appropriate context.

[routing]

;As the names are alphanumerical alias were also created

exten => _100X,1,Goto(phone${EXTEN:3},1)

exten => _10XX,1,Goto(phone${EXTEN:2},1)

exten => _pho[n]e.,1,Dial(SIP/${EXTEN},30)

exten => _pho[n]e.,n,Hangup(${HANGUPCAUSE})

Restart asterisk by typing:

# /etc/init.d/asterisk restart

Stopping Asterisk PBX: asterisk.

Starting Asterisk PBX: asterisk.

Enter asterisk and verify if the modules were correctly loaded. They should appear as bellow

# rasterisk

Created by Mark Spencer <markster@digium.com>

Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.

This is free software, with components licensed under the GNU General Public

License version 2 and other licenses; you are welcome to redistribute it under

certain conditions. Type 'core show license' for details.

=========================================================================

This package has been modified for the Debian GNU/Linux distribution

Please report all bugs to http://bugs.debian.org/asterisk

=========================================================================

Connected to Asterisk 1.4.21.2~dfsg-3+lenny5 currently running on ks34280 (pid = 10007)

ks34280*CLI> module show like mysql

Module Description Use Count

cdr_addon_mysql.so MySQL CDR Backend 0

app_addon_sql_mysql.so Simple Mysql Interface 0

res_config_mysql.so MySQL RealTime Configuration Driver 0

3 modules loaded

Validation:

To verify if everything is correctly configured register two phones using the accounts you’ve created. After a successful registration in asterisk you should see the phones. Notice that both phones have a defined IP address and a port number greater than 0.

ks34280*CLI> sip show peers

Name/username Host Dyn Nat ACL Port Status Realtime

phone2/phone2 85.1.0.203 D N 51298 Unmonitored

phone1/phone1 85.1.0.203 D N 60841 Unmonitored

2 sip peers [Monitored: 0 online, 0 offline Unmonitored: 2 online, 0 offline]

Make a call from one of the registered phones to another by typing its login (if you can dial alphanumerical numbers) or its alternative address 10XX (in the example shown above).

In asterisk you’ll see the result: Asterisk.log

Debugging:

Verify if the phone is configured correctly, if the registration requests reach the server (using ngrep) and create an account in sip.conf to test it. You can see if the call messages are the expected ones by running:

# ngrep port 5060 -W byline

If everything is ok, remove these test changes and verify the other configuration files.

In case of problems you can enable asterisk debugging. In the file /etc/asterisk/logger.conf comment with ‘;’ the line bellow and add the following new line:

;console => notice,warning,error

console => notice,warning,error,debug,verbose

then execute in asterisk:

ks34280*CLI> logger reload

Search for any error that might appear.

Resources:

http://www.spiration.co.uk/post/1327/asterisk-addons-setting-up-mysql-cdr-for-Asterisk

http://www.voip-info.org/wiki/view/Asterisk+cdr+mysql

http://www.voip-info.org/wiki/view/Asterisk+RealTime+Sip

http://www.voip-info.org/wiki/view/Asterisk+config+extconfig.conf