Installation of MySQL Database Server

.

These simple steps install and start MySQL database server on Fedora 20:

  • Install the package “community-mysql-server”: # yum -y install community-mysql-server
  • Start the daemon service: # systemctl start mysqld.service
  • Enable the daemon service: # systemctl enable mysqld.service
  • Connect to MySQL: # mysql -u root. Type Enter
  • Set the root password: mysql> set password for root@localhost=password(‘password’);

You can replace the localhost with your domain or IP address.

  • Delete anonymous users: mysql> delete from mysql.user where user=”;
  • mysql> exit

If you forgot the root password, do these:

  • # mysqld_safe –skip-grant-tables &
  • mysql> use mysql;
    mysql> update user set password=PASSWORD(“New-Password”) where User=’root’;
    mysql> flush privileges;
    mysql> quit
  • # systemctl stop mysqld
  • # systemctl start mysqld
  • # mysql -u root -p

Advertisements

Homer as a SIP Capturing System – Part 2

.

Introduction

In Part 1, i have talked about the definition of Homer. In this article i will talk about these:

  • Compilation and Installation of SipCapture module with OpenSIPS. To have more control over the installation process, i will do the compilation from the source.
  • Preparing Homer database.
  • Installation and configuration of Homer web interface.

Please have a look here OpenSIPS Compilation and Installation.

Some Packages must be Installed

To be able to compile the Sip_Capture module and install it, you need some packages installed on your system. Here i am using Fedora Red Hat.

# yum  install autoconf automake bzip2 cpio curl curl-devel curl-devel expat-devel fileutils make gcc gcc-c++ gettext-devel gnutls-devel ncftp nmap openssl openssl-devel mod_ssl mcrypt perl patch unzip wget zip zlib zlib-devel bison flex mysql mysql-server mysql-devel pcre-devel libxml2-devel sox httpd php php-gd php-mysql php-json git php-mysql php-devel php-gd

# yum install php-pecl-jsonc php-pecl-jsonc-devel

Some of above packages are needed for Homer Web interface.

Compilation and Installation of SipCapture Module

  • Go to the folder where you have the source code of OpenSIPS. SipCapture module is included with OpenSIPS (“/usr/local/src/opensips_1_11/modules/sipcapture”). To compile it, do these:

# cd  /usr/local/src/opensips_1_11/

  • Compile the SipCapture module as following:

# make include_modules=”sipcapture” modules

  • Install it:

# make install

  • Go to “/usr/local/opensips_1_11/lib64/opensips/modules/” and be sure there is a file named sipcapture.so

Downoad Homer Web

 # cd /var/www/

# git clone https://code.google.com/p/homer/

Or

# git clone https://github.com/sipcapture/homer/

# cd homer

Preparing Homer Databases

Now we want to prepare the databases that are used by the capturing server and the web interface:

Run Mysql server: # systemctl start mysqld.service

  • We will login to mysql as root (# mysql -uroot -p password) and add the super user “homeruser” :

MariaDB [(none)]> CREATE USER homeruser@localhost IDENTIFIED BY “homerpass”;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO homeruser@localhost WITH GRANT OPTION;

  • Create the database “homer_db” by the user “homeruser”:

# mysql –uhomeruser –phomerpass -e “create database IF NOT EXISTS homer_db”;

  • Create another database “homer_users“:

# mysql -uhomeruser -phomerpass -e create database IF NOT EXISTS homer_users;

  • Create the required tables:

# mysql -uhomeruser -phomerpass -Dhomer_db < sql/create_sipcapture_version_4.sql

# mysql -uhomeruser -phomerpass -Dhomer_db < webhomer/sql/statistics.sql
# mysql -uhomeruser -phomerpass -Dhomer_users < webhomer/sql/homer_users.sql

  • Some nodes are added as default. So  i will delete them:

# mysql -uhomeruser -phomerpass -Dhomer_users -e TRUNCATE TABLE homer_nodes;

  • Now i will add my node:

# mysql -uhomeruser -phomerpass -Dhomer_users -e “INSERT INTO homer_nodes VALUES(1,’127.0.0.1′,’homer_db’,’3306′,’homeruser’,’homerpass’,’sip_capture’,’node1′, 1);”

  • Add web user: user level can be: 1 – ADMIN, 2 – Power User, 3 – User

I will delete the default user (test@test.com,test123):

# mysql -uhomeruser -phomerpass -Dhomer_users -e TRUNCATE TABLE homer_logon;

Now i will add my admin:

# mysql -uhomeruser -phomerpass -Dhomer_users -e “INSERT INTO homer_logon VALUES(NULL, ‘Admin’, MD5(‘admin123’), 1);”

Configuration of Homer Web Interface

We are in this folder “/var/www/homer/webhomer/”. The configuration and preferences PHP pages are exist in that folder. Just rename the files “configuration_example.php”  to “configuration.php” and  “preferences_example.php” to “preferences.php”.

# cp configuration_example.php configuration.php
# cp preferences_example.php preferences.php

Give (Read,Write,Execute) permission to Homer tmp folder:

# chmod 777 tmp/

If you have SELinux enabled, please run SELinux troubleshooter and follow what it will suggest to do to give permission to “tmp/”

Set the time zone of the Homer server

Take the time zone of your system (# system-config-date) and set it in the variable “HOMER_TIMEZONE” in the file “preferences.php”.

# vim preferences.php

define(‘HOMER_TIMEZONE’, “Europe/Stockholm”);

Configuration of database access credentials

Adjust the database access credentials in the configuration file “configuration.php”.

/* Access Credentials to “homer_users” Database */
define(‘HOST’, “localhost”);
define(‘PORT’, 3306);
define(‘USER’, “homeruser”);
define(‘PW’, “homerpass”);
define(‘DB’, “homer_users”);

/* Access Credentials to “homer_db” Database */
define(‘HOMER_HOST’, “localhost”);
define(‘HOMER_PORT’, 3306);
define(‘HOMER_USER’, “homeruser”);
define(‘HOMER_PW’, “homerpass”);
define(‘HOMER_DB’, “homer_db”);
define(‘HOMER_TABLE’, “sip_capture”);

Some paths must be adjusted:

define(‘PCAPDIR’,”/var/www/homer/webhomer/tmp/”);
define(‘WEBPCAPLOC’,”/var/www/homer/webhomer/tmp/”);
define(‘APILOC’,”/var/www/homer/webhomer/api/”);

Apache Configuration

# vi /etc/httpd/conf/httpd.conf

Add these to httpd configuration file:

<Directory /var/www/homer/webhomer>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>

Alias /homer /var/www/homer/webhomer

Restart the Httpd

# systemctl restart httpd

Open the browser and go to the URL : http://IP_address/homer. You will be here:

Login by this user  (username=”admin”, password=”admin123″). If it is ok with authentication, you will be here:

If you could not login from another computer, check the firewall configuration (Open the port 80 and 443 on your server). 443 is the HTTPS port /HTTP over TLS/SSL). We will see the related configuration in another article.

Till now Homer database is empty and we need to run the capturing server which is responsible for capturing packets and insert them in the database.

OpenSIPS Configuration to be a Homer Capturing Server

OpenSIPS configuration file will be like the following:

# This Configuration file is taken from Homer GIT Page:

####### Global Parameters #########

debug=3
log_stderror=no
log_facility=LOG_LOCAL1

fork=yes
children=5

listen=udp:10.0.0.9:5060   # CUSTOMIZE ME

disable_tcp=yes
disable_tls=yes
#db_default_url="mysql://opensips:opensipsrw@localhost/opensips"

####### Modules Section ########
#set module path
mpath="/usr/local/opensips_1_11/lib64/opensips/modules/"

loadmodule "db_mysql.so"
loadmodule "sipcapture.so"

####### Routing Logic ########
modparam("sipcapture", "db_url", "mysql://homeruser:homerpass@localhost/homer_db")
modparam("sipcapture", "capture_on", 1)
modparam("sipcapture", "table_name", "sip_capture")
/* activate HEP capturing */
modparam("sipcapture", "hep_capture_on", 1)

/* configuration for Mirroring PORT */
modparam("sipcapture", "raw_socket_listen", "10.0.0.9:5060-6000")
modparam("sipcapture", "raw_interface", "eth1")
/* activate monitoring port capturing */
modparam("sipcapture", "raw_moni_capture_on", 1)
modparam("sipcapture", "raw_sock_children", 4)
/* Promiscious mode */
modparam("sipcapture", "promiscious_on", 1)

####### Routing Logic ########


# main request routing logic

# Main SIP request routing logic
# - processing of any incoming SIP request starts with this route
route {
        #For example, you can capture only needed methods...
        #if (!(method =~ "^(OPTIONS|NOTIFY|SUBSCRIBE)$"))) {
                sip_capture();
        #}
        drop;
}

onreply_route {

        #And only needed reply or needed requests method
        #if(status =~ "^(1[0-9][0-9]|[3[0-9][0-9]|4[0-9]|[56][0-9][0-9])") {
        #if(!($rm =~ "^(NOTIFY|SUBSCRIBE|OPTIONS|)$")) {
                sip_capture();
        #}
        drop;
}

Save the file and restart OpenSIPS:

# systemctl restart opensips.service

Troubleshooting

Missed column  “authorization” in the capturing table “sip_capture”

MariaDB [(none)]> alter table sip_capture ADD authorization VARCHAR(125);

Now the capturing server can store the packets in the database homer_db

OpenSIPS as a SIP Capturing Agent

OpenSIPS SIPTRACE module can send the captured packets to an external HEP server (Homer capturing server) instead of storing them in the local database. The following is the configuration to send the captured packets out:

modparam(“siptrace”, “duplicate_uri”, “sip:10.0.0.1:9060″) # The capturing server address to send a duplicate of traced message to.

modparam(“siptrace”, “duplicate_with_hep”, 1)  # Enable HEP

modparam(“siptrace”, “trace_to_database”, 0) # Disable tracing to the local database


Next

The next part will be exploring Homer through its web interface and enabling statistics.


More Information


Troubleshooting of Query Execution in Mysql DB

 

To know what mysql is doing we enable the query logging.

Enable Query logging

  • Enter mysql console as root

# mysql -uroot -p

  • Enable logging:mysql> SET global general_log = 1; SET global log_output = ‘table’;
  • View the log and see what is supposed to be executed

mysql> select * from mysql.general_log;

Disable Query loggning

mysql> SET global general_log = 0;

This is useful when you want to track whats going on in your SIP server’s database.

To know about other types of mysql logging Mysql-Server-Logs

 

 

OpenSIPS Compilation and Installation Troubleshooting On Fedora

.

OpenSIPS Installation

Here i will install OpenSIPS 1.11. To get more control over the installation process, I will compile the OpenSIPS from the source.

# cd /usr/local/src/

Get the latest release of OpenSIPS source from OpenSIPS’s GitHub repository:

Go to the place where “GitHub.com” is remotely storing OpenSIPS and get the OpenSIPS ‘s repo HTTP-address https://github.com/OpenSIPS/opensips.git“. Then clone the repo.

# git clone https://github.com/OpenSIPS/opensips.git -b 1.11 opensips_1_11

# cd opensips_1_11

Note: The option -b is used to choose the branch “-b 1.11”

Install the prerequisites needed to configure the compilation:

  • Flex and Bison needed for parsing of configuration file.
  • ncurses-libs, ncurses-devel, and ncurses-static for grafical display (text-based user interfaces).

Some prerequisites must be installed for GCC like Flex, Bison, TCl and others. If you could not compile, check what is missed in your system. Have a look here.

Open the configuration interface:

# make menuconfig

If you couldn’t open the interface and have an error like “Make error *255*“, this means  needed prerequisites are not all installed. You could also need  to try these:

  • Remove GCC and install it again:

# yum remove gcc

# yum install gcc

If you are patient, try to do the same thing with some broken installed prerequisites.

  • You could need to change the Makefile (In my case: “/usr/local/src/opensips_1_11/menuconfig/Makefile“). For example adding include (-I) and library (-L) paths.

all: $(MENUCONFIG_FILES)
$(CC) -o $(EXEC_NAME) $(CFLAGS) $^ -I/usr/include -L/usr/lib -lncurses

  • Still have a problem, do system restart.

The text-based user interface to configure and compile will look like this:

Here we can do the following

  • Configure some compile options used at OpenSIPS compilation time.
  • Compile and install OpenSIPS.
  • Cleanup OpenSIPS sources.
  • Generate OpenSIPS script
  • Exit and save all changes.

To get the features you want, go to the first menu and configure the compilation. You can select the modules you want to compile (“Configure Compile Options” –> “Configure Excluded Modules”). The selected modules here (marked with star *) will be included in the compilation of the OpenSIPS. Also you can configure the installation directories (“Configure Compile Options” –> “Configure Install Prefix”). It can be for example “/usr/local/opensips_1_11/”. Note: press q letter or left-arrow to go back to the previous screen.

After the configuration press on “Save Changes” to save the changes you did. After this you will get a message of the required libraries which must be installed according to the modules included in the configuration of the compilation (module dependencies). Go ahead and download them before compilation. Put the graphical interface in the background (press control-Z) or open a new terminal so you can see the names of the required libraries from the graphical interface and install them in the new terminal.

For example the module “db_mysql” needs mysql-devel (mysql client development library) to be installed.

Back to the interface and select “Compile and Install OpenSIPS” , then the compilation will start. It takes few minutes.

After the installation is completed, we get OpenSIPS installed as it is configured. In my case, it will be installed in “/usr/local/opensips_1_11/”. This directory contains these:

In “etc” we have the OpenSIPS configuration files.

In “lib” we have OpenSIPS modules

In “sbin” we have the executable files.

In “share” we have the documentation.

Create OpenSIPS Mysql Database

Install your database server. As an example install MySQL database server.

Now we are in “/usr/local/opensips_1_11/”

  • Configure the database parameters: Edit the configuration file “etc/opensips/opensipsctlrc”. This file contains the configuration of the database connection. # vim opensipsctlrc. Adjust the parameters like the name of the database, the read/write user that the opensips will be using, password, the database root user, and others.
  • Create the OpenSIPS database.Now back to “/usr/local/opensips_1_11/”. In the sub-directory /sbin we have opensipsdbctl tool. Use it to create the database. Here is the command and its output:

# ./opensipsdbctl create  

        MySQL password for root:

INFO: test server charset

INFO: creating database opensips …

INFO: Core OpenSIPS tables succesfully created.

Install presence related tables? (y/n): y

INFO: creating presence tables into opensips …

INFO: Presence tables succesfully created.

Install tables for imc cpl siptrace domainpolicy carrierroute userblacklist b2b registrant call_center? (y/n): y

INFO: creating extra tables into opensips …

INFO: Extra tables succesfully created.

opensipsdbctl tool takes its configuration from the file “etc/opensipsctlrc”

Notes

  • Install and configure mysqld as a system service:

# yum -y install community-mysql-server

       # systemctl start mysqld.service

       # systemctl enable mysqld.service

  • Check if its running: # ps aux | grep mysql
  • Default password is empty so only press “Enter” when asking for password. For security, set root password as following:

  mysql> set password for root@localhost=password(‘fedoracore2’);

Generate OpenSIPS Configuration File

The prerequisite is to install m4 package which comes by default with most Linux distribution. M4 is GNU macro processor and it is useful for writing text files which can be logically parsed.

Now we are in “/usr/local/opensips_1_11/”

Go to sbin and execute ./osipsconfig. You will get the text interface to generate the configuration file.

snapshot3

Three kinds of configuration files/scripts can be generated:

  • Residential scripts: If we have customers and we want to offer them certain services.
  • Trunking scripts: where we have PBXs and SIP trunks and we want to route the traffic.
  • Load-balancing scripts: Simple load balancer that can load the traffic between chosen destinations.

Example: Generating residential script (“Generate OpenSIPS Script” –> “Residential Script” –> “Configure Residential Script”) so we come to this page:

snapshot4

Here we can configure how OpenSIPs will work. After this configuration press “q” to go to the previous page then click on “Generate Residential Script”.

snapshot5

The generated script will be placed in the OpenSIPs etc folder (/usr/local/opensips_1_11/etc/opensips) marked with the date of its generation. Example: “opensips_residential_2014-9-25_12:30:52.cfg”. After the generation of the file we need to customize it little bit. For example fix the IP addresses and ports that the OpenSIPs will be working on. For example:

listen=udp:10.0.0.4:5060 # CUSTOMIZE ME
Also customization of the places where DB connection is required. Example:

modparam(“siptrace”, “db_url”, “mysql://user:passwd@host/dbname”)
modparam(“usrloc”, “db_url”,”mysql://opensips:fedoracore2@localhost/opensips”) # CUSTOMIZE ME

Don’t forget to check the path of the installed modules: mpath=”/usr/local/opensips_proxy/lib/opensips/modules”

OpenSIPS Init Script

To make OpenSIPS works as system service, we need Init script. Follow these steps:

  • Go to the src folder : “/usr/local/src/opensips_1_11/packaging”
  • Here you have to select your system. For example if you have Fedora go to fedora sub-directory.
  • In that folder you will see the script opensips.init which is the actual init script. Copy this script to /etc/init.d/opensips and give it execution right.

# cp opensips.init /etc/init.d/opensips
# chmod +x /etc/init.d/opensips

  • Edit the script
    # vim  /etc/init.d/opensips

Here is a part of the script where there will be changes. The red line replace the line above it. Please see the corresponding to this in your system.

prog=opensips

opensips=/usr/sbin/$prog

opensips=/usr/local/opensips_1_11/sbin/$prog

cfgdir=”/usr/local/opensips_1_11/etc/$prog”

pidfile=”/var/run/$prog.pid”

lockfile=”/var/lock/subsys/$prog”

configfile=”/etc/$prog/$prog.cfg”

configfile=”$cfgdir/opensips_residential_2014-9-25_12:30:52.cfg”

Include New Syslog Entry for OpenSIPS

  • Open the configuration file. In the Global Configuration section you will see the following :

          debug=3

          log_stderror=no

          log_facility=LOG_LOCAL0

          Change 0 to 1

          log_facility=LOG_LOCAL1

  • Edit rsyslog.conf: #vim  /etc/rsyslog.conf
    Add this entry:   local1.*                        /var/log/opensips.log
  • Restart rsyslog daemon : #systemctl restart rsyslog OR  #service rsyslog restart
    All OpenSIPs log messages will be stored in this file /var/log/opensips.log

   By adding ““, the logging will be in Asynchronous mode so opensips will not wait until the messages are propagated.

Add Linux User and Group For OpenSIPS

Execute “# system-config-users”. Add user=opensips and create group=opensips for that user

Run OpenSIPS

The database server must be running before starting OpenSIPS. For example if the database server is mysql, check its status (# systemctl status mysqld.service) and run it if it is not running (# systemctl start mysqld.service).

To enable and start opensips as a system service.

# systemctl start opensips.service

# systemctl enable opensips.service

Check the file “/var/log/opensips.log” to see if everything is ok and no error messages (# tail -f  /var/log/opensips.log).

Example of an error message in the opensips.log:

ERROR:uri:db_checks_fixup1: configuration error – no database URL is configured!

uri” is the name of the module which has a problem.

Note: Problems which arise before the loading of the configuration file are still going to “/var/log/messages”.

Now the opensips should be running and we can check this by executing some commands:

♠ Check the status of the OpenSIPS’s service (i.e. Loaded and Running): #systemctl status opensips.service

♠ Check network’s state of the OpenSIPS server: # netstat -lpn |grep opensips

tcp 0 0 127.0.0.1:5060 0.0.0.0:* LISTEN 32634/opensips

udp 0 0 127.0.0.1:5060 0.0.0.0:* 32634/opensips

Note: OpenSIPS will not start on database init failure and it will exit immediately (return -1) on module initialization failure (calling the function “mod_init”).

Start/Restart OpenSIPS

To restart OpenSIPS, do the following:

# systemctl restart opensips.service

or

#  cd /etc/init.d/

# ./opensips restart

Start Working

Now after OpenSIPS has been installed successfully, you can start using its modules in the routing script. What i recommend when using any module:

  • Reading in the module documentation (Last Version). Note the section “Exported MI Functions” in the documentation of each module lists the module’s exported MI functions that can be called from outside of OpenSIPS (External applications). Example:

# opensipsctl fifo debug 1 

OpenSIPS project is growing quickly so please always read in the latest documentation.

  • Sometimes you need to look at the Database Schema. Some fields are pushed to database only when a specific module parameter is set. Example: In multi-domain scenarios, you might need to save the domain part of the user so the domain part and the username part are used to identify the user. So in this case you need to set the “use_domain” parameter of “usrloc” module to non zero value which means true.

modparam(“usrloc”, “use_domain”, 1)


More Information