Asterisk database connectivity via ODBC

Im using ODBC to connect my asterisk to mysql database. This will be needed to saving CDR or CEL in database, query from Dialplan or make your asterisk realtime ,etc . Of course same method can be used for every database that has ODBC driver .

Installing MYSQL
 :CENTOS
sudo yum install mysql-server
sudo service mysqld start
:Debian
sudo apt-get install mysql-server
Configuring MYSQL
sudo /usr/bin/mysql_secure_installation
mysql -u root -p
;'mysql> CREATE USER 'asterisk'@'%' IDENTIFIED BY 'password
Query OK, 0 rows affected (0.00 sec)
 ;mysql> CREATE DATABASE asterisk
Query OK, 1 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
# mysql -u asterisk -p asterisk
Enter password:
mysql>
Installing and Configuring ODBC
:CENOTS
sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
sudo yum install mysql-connector-odbc
:Debian
sudo apt-get install unixODBC unixODBC-dev
sudo apt-get install libmyodbc
Configuring ODBC for MySQL
vim /etc/odbcinst.ini
CENTOS ( uncomment) :
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1
Debian :
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
FileUsage = 1
On 64-bit systems, you will need to change the path of the libraries
from /usr/lib/ to /usr/lib64/ in order to access the correct library files.
Verify that the system is able to see the driver by running the following command. It
should return the label name MySQL if all is well:
# odbcinst -q -d
[MySQL]
vim /etc/odbc.ini
[asterisk-connector]
Description = MySQL connection to 'asterisk' database
Driver = MySQL
Database = asterisk
Server = localhost
User = asterisk
Password = password
Port = 3306
Socket = /var/lib/mysql/mysql.sock
On Debian , the socket location is /var/run/mysqld/mysqld.sock.
Validating the ODBC Connector
echo "select 1" | isql -v asterisk-connector
$ cd ~/src/asterisk-complete/asterisk/1.8
$ ./configure
$ make menuselect
$ make install
Configuring res_odbc to Allow Asterisk to Connect Through ODBC
vim /etc/asterisk/res_odbc.conf
[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => password
pooling => no
limit => 99999
pre-connect => yes


Restarting Asterisk or reloading res_odbc module  :

asterisk -rx "module reload res_odbc.so" or sudo /etc/init.d/asterisk restart  if you dont have active calls 

*CLI> odbc show
ODBC DSN Settings
—————–
Name: asterisk
DSN: asterisk-connector
Last connection attempt: 1969-12-31 19:00:00
Pooled: No
Connected: Yes


Comments