{"id":334,"date":"2010-11-18T10:00:21","date_gmt":"2010-11-18T09:00:21","guid":{"rendered":""},"modified":"2018-11-01T11:09:14","modified_gmt":"2018-11-01T10:09:14","slug":"adding-mysql-support-kamailio-31-debian-lenny","status":"publish","type":"post","link":"https:\/\/nil.uniza.sk\/en\/adding-mysql-support-kamailio-31-debian-lenny\/","title":{"rendered":"Adding MySQL support into Kamailio 3.1 (debian lenny)"},"content":{"rendered":"

The article describes how to add the MySQL support for the Kamailio 3.1.0 server. Activities described here are following the basic Kamailio server installation, described in the article Installing Kamailio 3.1 on debian lenny<\/a>.<\/p>\n

Installing Mysql server and mysql module for Kamailio<\/h2>\n

To use such persistant storage as a database we first need to install DB server, the Mysql here, and kamailio modules which the kamailio will use to connect into database, the kamailio-mysql-modules here.<\/p>\n

Installation, thanks to adding kamailio repository for debian<\/a>, is straightforward<\/p>\n

\r\npstest:\/home\/palo# apt-get install mysql-server kamailio-mysql-modules<\/pre>\n
During the Mysql server installation we are asked to add root password for mysql server.<\/p>\n
Next, we have to specify parameters, which the kamailio server will use for database connection. Therefore we need to edit the \/etc\/kamailio\/kamctlrc file and we need to set up required parameters, which the kamailio server and kamctl cmd will use.<\/p>\n
We will add or uncomment following lines:<\/p>\n
\r\nSIP_DOMAIN=ps.sip.uniza.sk\r\nDBENGINE=MYSQL\r\nDBHOST=localhost\r\n# I changed DBNAME from default named "openser<\/em>", cause I've already created opernser for other OpenSER process\r\nDBNAME=kamailio\r\nDBRWUSER=openser\r\nDBRWPW="openserrw"\r\nDBROUSER=openserro\r\nDBROPW=openserro\r\nDBROOTUSER="root"\r\nALIASES_TYPE="DB"\r\nCTLENGINE="FIFO"\r\n# rewrite OSER_FIFO="FIFO", because \r\n# it is causing the fifo error<\/a>\r\nOSER_FIFO="\/tmp\/kamailio_tmp"<\/span>\r\nVERBOSE=1\r\nPID_FILE=\/var\/run\/kamailio.pid<\/pre>\n
 Next we will create DB tables which will be used by the Kamailio as a storage<\/div>\n
\r\npstest:\/etc\/kamailio# kamdbctl create<\/span>\r\nMySQL password for root:\r\ndatabase engine 'mysql' loaded\r\nINFO: test server charset\r\nINFO: creating database kamailio ...\r\nCreating core table: standard\r\nCreating core table: acc\r\nCreating core table: lcr\r\nCreating core table: domain\r\nCreating core table: group\r\nCreating core table: permissions\r\nCreating core table: registrar\r\nCreating core table: usrloc\r\nCreating core table: msilo\r\nCreating core table: alias_db\r\nCreating core table: uri_db\r\nCreating core table: speeddial\r\nCreating core table: avpops\r\nCreating core table: auth_db\r\nCreating core table: pdt\r\nCreating core table: dialog\r\nCreating core table: dispatcher\r\nCreating core table: dialplan\r\nINFO: Core Kamailio tables succesfully created.<\/span><\/pre>\n
 As a final point we will try add a new user into the database<\/p>\n
\r\npstest:\/etc\/kamailio# k<\/span>amctl add palo palo_password<\/span>\r\ndatabase engine 'MYSQL' loaded\r\nControl engine 'FIFO' loaded\r\nis_user: user counter=0\r\ncheck_db_alias: alias counter=0\r\nnew user 'palo' added<\/pre>\n
Correct, everything works!<\/p>\n
Configuration of the Kamailio for using of the MySQL<\/h2>\n
Loading required modules<\/h3>\n
We need to check, if required kamailio modules for the Mysql usage are loaded. If not, we will uncomment lines with modules that are required to be loaded. So open the \/etc\/kamailio\/kamailio.cfg file. Looking first time in is for me a little surprise comparing to the old fashioned OpenSER configs. Kamailio 3.1.x comes with a new feature called DEFINE zone blocks, mentioned in http:\/\/www.kamailio.org\/dokuwiki\/doku.php\/features:new-in-3.1.x?s[]=define<\/a><\/p>\n
After a few second of familiarization with this feature we should start. <\/span><\/span>As a first step we define zone blocks for mysql support (WITH_MYSQL), database authentication (WITH_AUTH), database aliasing (WITH_ALIASDB), and persistent location storage (WITH_USRLOCDB) (recommended inside of the config file as notes).<\/p>\n
At the end of this article we will see, that using of this define blocks make easier our effort, because everything was prepared for us (of course, the configation of the Mysql support is a very simple example of the kamailio usage).<\/p>\n
So as a next step define following zone blocks.<\/p>\n
\r\n#!define WITH_MYSQL \r\n#!define WITH_AUTH\r\n#!define WITH_ALIASDB\r\n#!define WITH_USRLOCDB\r\n<\/pre>\n
Go next throught the cfg file and check lines to find inside of the ####### Defined Values ######### <\/strong><\/em>config part <\/em>DB_URL defien block. Check if your DB URL is configured correctly.<\/p>\n
\r\n#!define DBURL "mysql:\/\/openser:openserrw@localhost\/kamailio"\r\n<\/pre>\n
In my case,  because I’ve changed table name in \/etc\/kamailio\/kamctlrc file during mysql initiation, my DB_URL is pointing to the "kamailio" DB table, not to the default "openser" DB table.<\/p>\n
Next, inside of the module section we find lines:<\/p>\n
\r\n#!ifdef WITH_MYSQL\r\nloadmodule "db_mysql.so"\r\n#!endif\r\n<\/pre>\n
because we define #!define WITH_MYSQL <\/em><\/strong>support, I’m assuming that it is ok, and db_mysql.so module will be loaded.<\/p>\n
Following next we also find lines for DB authentication<\/p>\n
\r\n#!ifdef WITH_AUTH\r\nloadmodule "auth.so"\r\nloadmodule "auth_db.so"\r\n#!endif\r\n<\/pre>\n
and because we also defined #!define WITH_AUTH <\/em><\/strong>block zone, I again supposing, that respective modules will be loaded (as we will see).<\/p>\n
Next block zone for DB aliasing is in<\/p>\n
\r\n#!ifdef WITH_ALIASDB\r\nloadmodule "alias_db.so"\r\n#!endif\r\n<\/pre>\n
as a last step check if usrloc module is commented out (by, default is yes)<\/p>\n
\r\nloadmodule "usrloc.so"\r\n<\/pre>\n
That’s all for the module section, all required module should be loaded.<\/p>\n
Setting up module parameters<\/h3>\n
Now we will check and will set up required module parameters to ensure that loaded modules will work properly.<\/p>\n
Move to the part of the kamailio.cfg file starting with line<\/p>\n
\r\n# ----------------- setting module-specific parameters --------------\r\n<\/pre>\n
The module parameters section is starting here.<\/p>\n
Going through the config file we first find modparams regarding usrloc module. Because we define block WITH_USRLOCDB previously, the parameters will be setted up.<\/p>\n
\r\n# ----- usrloc params -----\r\n\/* enable DB persistency for location entries *\/\r\n#!ifdef WITH_USRLOCDB\r\nmodparam("usrloc", "db_url", DBURL)\r\nmodparam("usrloc", "db_mode", 2)\r\nmodparam("usrloc", "use_domain", MULTIDOMAIN)\r\n#!endif\r\n<\/pre>\n
All parameters meaning looks clear, just  we will check what is db_mode =2<\/em>. Looking inside of the usrloc module docs <\/a>for db_mode =2 we will find, that 2 means<\/p>\n
\n
2 – Write-Back scheme. This is a combination of previous two  \t\t\tschemes. All changes are made to memory and database  \t\t\tsynchronization is done in the timer. The timer deletes all  \t\t\texpired contacts and flushes all modified or new contacts to  \t\t\tdatabase.  Use this scheme if you encounter high-load peaks  \t\t\tand want them to process as fast as possible. The mode will  \t\t\tnot help at all if the load is high all the time.  Also, latency  \t\t\tof this mode is much lower than latency of mode 1, but slightly  \t\t\thigher than latency of mode 0. \t\t\t<\/em><\/p>\n<\/blockquote>\n
ok, accepted.<\/p>\n
Next, check params for auth_db, there are default setting,<\/p>\n
\r\n# ----- auth_db params -----\r\n#!ifdef WITH_AUTH\r\nmodparam("auth_db", "db_url", DBURL)\r\nmodparam("auth_db", "calculate_ha1", yes)\r\nmodparam("auth_db", "password_column", "password")\r\nmodparam("auth_db", "load_credentials", "")\r\nmodparam("auth_db", "use_domain", MULTIDOMAIN)\r\n#!endif\r\n<\/pre>\n
where it means, that DB table is located at DBURL location, and the server use the password hashed from the DB table column named password.<\/p>\n
Ok, accepted.<\/p>\n
Next alias_db params<\/p>\n
\r\n# ----- alias_db params -----\r\n#!ifdef WITH_ALIASDB\r\nmodparam("alias_db", "db_url", DBURL)\r\nmodparam("alias_db", "use_domain", MULTIDOMAIN)\r\n#!endif\r\n<\/pre>\n
accepted and this is the end of parameters section, next we are going to modify the route logic.<\/p>\n
Modifying route logic for using DB<\/h2>\n
Setting up registrar server<\/h3>\n
We will use our Kamailio server not only as Proxy but also as Registrar (therefore inside of the config file there is also loaded registrar module "registrar.so" <\/em>(<\/strong>by default)) to build up a location bindings stored in the DB. The registration process will authenticate users to accept registration. Inside of the main route logic there is a calling into route(AUTH);<\/em><\/strong> block zone. Finding this block in cfg file we may see, that this is the part of route logic handling the authentication process. The first part of the route(AUTH) concerns about the handling of the REGISTER message.<\/p>\n
\r\n#!ifdef WITH_AUTH\r\n        if (is_method("REGISTER"))\r\n        {\r\n                # authenticate the REGISTER requests (uncomment to enable auth)\r\n                if (!www_authorize("$td", "subscriber"))\r\n                {\r\n                        www_challenge("$td", "0");\r\n                        exit;\r\n                }\r\n\r\n                if ($au!=$tU)\r\n                {\r\n                        sl_send_reply("403","Forbidden auth ID");\r\n                        exit;\r\n                }\r\n        } else {\r\n\r\n<\/pre>\n
by default evrything is commented out, so we do not need to change anything.<\/p>\n
next part<\/p>\n
\r\n# authenticate if from local subscriber\r\n                if (from_uri==myself)\r\n                {\r\n                        if (!proxy_authorize("$fd", "subscriber")) {\r\n                                proxy_challenge("$fd", "0");\r\n                                exit;\r\n                        }\r\n                        if (is_method("PUBLISH"))\r\n                        {\r\n                                if ($au!=$tU) {\r\n                                        sl_send_reply("403","Forbidden auth ID");\r\n                                        exit;\r\n                                }\r\n                        } else {\r\n                                if ($au!=$fU) {\r\n                                        sl_send_reply("403","Forbidden auth ID");\r\n                                        exit;\r\n                                }\r\n                        }\r\n\r\n                        consume_credentials();\r\n                        # caller authenticated\r\n                } else {\r\n                        # caller is not local subscriber, then check if it calls\r\n                        # a local destination, otherwise deny, not an open relay here\r\n                        if (!uri==myself)\r\n                        {\r\n                                sl_send_reply("403","Not relaying");\r\n                                exit;\r\n                        }\r\n                }\r\n        }\r\n#!endif\r\n\r\n<\/pre>\n
and mainly the sub-part<\/p>\n
\r\nif (from_uri==myself)\r\n                {\r\n                        if (!proxy_authorize("$fd", "subscriber")) {\r\n                                proxy_challenge("$fd", "0");\r\n                                exit;\r\n                        }<\/pre>\n
is doing the INVITE message proxy authentication, by default is not commented, so it should work!<\/p>\n
And we are at the end of configuration, next we will do testing of the configuration.<\/p>\n
Testing<\/h2>\n
First we check if the config is syntactically correct and if we did not do some mistypes. We will use the -c paramerter of the kamailio cmd.<\/p>\n
\r\npstest:\/etc\/kamailio# kamailio -c kamailio.cfg\r\nloading modules under \/usr\/lib\/kamailio\/modules_k\/:\/usr\/lib\/kamailio\/modules\/\r\nListening on\r\n             udp: 158.193.139.51:5060\r\n             tcp: 158.193.139.51:5060\r\nAliases:\r\n             tcp: pstest:5060\r\n             tcp: pstest.ps.sip.uniza.sk:5060\r\n             udp: pstest:5060\r\n             udp: pstest.ps.sip.uniza.sk:5060\r\n             *: ps.sip.uniza.sk:*\r\n\r\nconfig file ok, exiting..\r\n<\/pre>\n
Next we will restart the kamailio service to accept cfg changes…<\/p>\n
\r\npstest:\/etc\/kamailio# \/etc\/init.d\/kamailio restart\r\nRestarting kamailio: kamailioloading modules under \/usr\/lib\/kamailio\/modules_k\/:\/usr\/lib\/kamailio\/modules\/\r\nListening on\r\n             udp: 158.193.139.51:5060\r\n             tcp: 158.193.139.51:5060\r\nAliases:\r\n             tcp: pstest:5060\r\n             tcp: pstest.ps.sip.uniza.sk:5060\r\n             udp: pstest:5060\r\n             udp: pstest.ps.sip.uniza.sk:5060\r\n             *: ps.sip.uniza.sk:*\r\n\r\n<\/pre>\n
ok…<\/p>\n
Next we will add a new user into the DB<\/p>\n
\r\npstest:\/etc\/kamailio# kamctl add jojo jojo's_password\r\ndatabase engine 'MYSQL' loaded\r\nControl engine 'FIFO' loaded\r\nis_user: user counter=0\r\ncheck_db_alias: alias counter=0\r\nnew user 'jojo' added\r\n<\/pre>\n
and we will use our favorite client to make a registration. To obseve the registration process we start the ngrep as the frontend process and will do registration usign the UA.<\/p>\n
\r\nngrep -p -q -W byline port 5060\r\n<\/pre>\n
\r\nREGISTER sip:ps.sip.uniza.sk SIP\/2.0.\r\nVia: SIP\/2.0\/UDP 192.168.10.108:40468;branch=z9hG4bK-d8754z-f23264153e04534e-1---d8754z-;rport.\r\nMax-Forwards: 70.\r\nContact: <sip:jojo@192.168.10.108:40468;rinstance=d5b063d544035d5a>.\r\nTo: "jojo"<sip:jojo@ps.sip.uniza.sk>.\r\nFrom: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=766b1952.\r\nCall-ID: NjA5OGNiZjVhNzdmNmYxODBlNDQ4YTEwMDdmNDAwZWM..\r\nCSeq: 1 REGISTER.\r\nExpires: 3600.\r\nAllow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, NOTIFY, MESSAGE, SUBSCRIBE, INFO.\r\nUser-Agent: eyeBeam release 1102q stamp 51814.\r\nContent-Length: 0.\r\n.\r\n\r\n\r\nU 158.193.139.51:5060 -> 158.193.152.64:28507\r\nSIP\/2.0 401 Unauthorized.\r\nVia: SIP\/2.0\/UDP 192.168.10.108:40468;branch=z9hG4bK-d8754z-f23264153e04534e-1---d8754z-;rport=28507;received=158.193.152.64.\r\nTo: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=f11c829fa10fd0f1cba4621773c131eb.7aac.\r\nFrom: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=766b1952.\r\nCall-ID: NjA5OGNiZjVhNzdmNmYxODBlNDQ4YTEwMDdmNDAwZWM..\r\nCSeq: 1 REGISTER.\r\nWWW-Authenticate: Digest realm="ps.sip.uniza.sk", nonce="TOZNH0zmS\/PNRBKh6l0jzT2BpN5uANl2".\r\nServer: kamailio (3.1.0 (x86_64\/linux)).\r\nContent-Length: 0.\r\n\r\n\r\nU 158.193.152.64:28507 -> 158.193.139.51:5060\r\nREGISTER sip:ps.sip.uniza.sk SIP\/2.0.\r\nVia: SIP\/2.0\/UDP 192.168.10.108:40468;branch=z9hG4bK-d8754z-371ce327c1613374-1---d8754z-;rport.\r\nMax-Forwards: 70.\r\nContact: <sip:jojo@192.168.10.108:40468;rinstance=d5b063d544035d5a>.\r\nTo: "jojo"<sip:jojo@ps.sip.uniza.sk>.\r\nFrom: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=766b1952.\r\nCall-ID: NjA5OGNiZjVhNzdmNmYxODBlNDQ4YTEwMDdmNDAwZWM..\r\nCSeq: 2 REGISTER.\r\nExpires: 3600.\r\nAllow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, NOTIFY, MESSAGE, SUBSCRIBE, INFO.\r\nUser-Agent: eyeBeam release 1102q stamp 51814.\r\nAuthorization: Digest username="jojo",realm="ps.sip.uniza.sk",nonce="TOZNH0zmS\/PNRBKh6l0jzT2BpN5uANl2",uri="sip:ps.sip.uniza.sk",response="6a22108222110e5b4df904c08c5e6879",algorithm=MD5.\r\nContent-Length: 0.\r\n.\r\n\r\n\r\nU 158.193.139.51:5060 -> 158.193.152.64:28507\r\nSIP\/2.0 200 OK.\r\nVia: SIP\/2.0\/UDP 192.168.10.108:40468;branch=z9hG4bK-d8754z-371ce327c1613374-1---d8754z-;rport=28507;received=158.193.152.64.\r\nTo: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=f11c829fa10fd0f1cba4621773c131eb.00fb.\r\nFrom: "jojo"<sip:jojo@ps.sip.uniza.sk>;tag=766b1952.\r\nCall-ID: NjA5OGNiZjVhNzdmNmYxODBlNDQ4YTEwMDdmNDAwZWM..\r\nCSeq: 2 REGISTER.\r\nContact: <sip:jojo@192.168.10.108:40468;rinstance=d5b063d544035d5a>;expires=3600.\r\nServer: kamailio (3.1.0 (x86_64\/linux)).\r\nContent-Length: 0.\r\n.\r\n<\/pre>\n
Perfect, it was simplier as look likes when I saw the kamailio.cfg file for the firstime after OpenSER 1.3.2. At the page attachment you can find the working cfg file.<\/p>","protected":false},"excerpt":{"rendered":"
The article describes how to add the MySQL support for the Kamailio 3.1.0 server. Activities described here are following the basic Kamailio server installation, described in the article Installing Kamailio 3.1 on debian lenny<\/a>.<\/p>\n
Installing Mysql server and mysql module for Kamailio<\/h2>\n
To use such persistant storage as a database we first need to install DB server, the Mysql here, and kamailio modules which the kamailio will use to connect into database, the kamailio-mysql-modules here.<\/p>","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_seopress_robots_primary_cat":"","_seopress_titles_title":"","_seopress_titles_desc":"","_seopress_robots_index":"","_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[781],"tags":[],"class_list":["post-334","post","type-post","status-publish","format-standard","hentry","category-kamailio-en"],"taxonomy_info":{"category":[{"value":781,"label":"Kamailio"}]},"featured_image_src_large":false,"author_info":{"display_name":"admin","author_link":"https:\/\/nil.uniza.sk\/en\/author\/admin\/"},"comment_info":12,"category_info":[{"term_id":781,"name":"Kamailio","slug":"kamailio-en","term_group":0,"term_taxonomy_id":779,"taxonomy":"category","description":"","parent":771,"count":29,"filter":"raw","cat_ID":781,"category_count":29,"category_description":"","cat_name":"Kamailio","category_nicename":"kamailio-en","category_parent":771}],"tag_info":false,"_links":{"self":[{"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/posts\/334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/comments?post=334"}],"version-history":[{"count":0,"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/posts\/334\/revisions"}],"wp:attachment":[{"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/media?parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/categories?post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nil.uniza.sk\/en\/wp-json\/wp\/v2\/tags?post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}