Asterisk with MySQL Database

Process to connect Asterisk with database.

Operating System : - Fedora Core 5

KERNAL Version: - 2.6.20-1.2307.fc5smp

RPMS Required

MySql Installation :

yum install mysql

yum install mysql-server

yum install mysqlclient10

yum install mysql-devel

yum install php-mysql

Check all the RPMs has installed or not

rpm –q <package name>

Start the service/database MySQL by any of the following two commands

/etc/rc.d/init.d/mysqld start

service mysqld start

mysql_install_db

(The script will run mysql_install_db to create a default database in /var/lib/mysql/mysql/ if the mysql init script has never been run before. The install script will not be run again as long as the default database directory exists.)

The database executes as user mysqld and group mysqld.

Notes:

* One may manually initialize the database with the command: /usr/bin/mysql_install_db

Creates system tables in /var/lib/mysql/mysql/

Only execute the first time MySQL is installed.

* Databases located in: /var/lib/mysql/

* Default config file installed by RPM: /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

[mysql.server]

user=mysql

basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Post installation:

1. Admin user id: root

Default password: blank

The first task is to assign a password:

[prompt]$ mysqladmin -u root password 'new-password'

Note: the following SQL commands will also work:

mysql> USE mysql;

mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE

user='root';

mysql> FLUSH PRIVILEGES;

2. Create a database: (Creates directory /var/lib/mysql/asterisk)

[prompt]$ mysqladmin -h localhost -u root -ppassword create asterisk

(or use SQL command: CREATE DATABASE asterisk;)

3. Add tables, data, etc:

Connect to database and issue the following SQL commands:

(mysql –h localhost –u root – p <database name>

[prompt]$ mysql -h localhost -u root –p asterisk

or by the follwing commad

[prompt]$ mysql -h localhost -u root -ppassword

...

mysql> use asterisk

mysql> create table employee (Name char(20),Dept char(20),jobTitle char(20));

mysql> DESCRIBE employee

mysql> show tables;

mysql> INSERT INTO employee VALUES (Sandip’,’Systems’,'Programer');

Now MySQL Installation is complete.

Now for connection using other application we need to install odbc driver

yum install MyODBC

Get the rpms unixODBC-2.2.7.tar.gz

unixODBC install instructions:

cd /usr/src

tar zxf unixODBC-2.2.7.tar.gz

cd unixODBC-2.2.7

./configure --disable-gui

make

make install

Configuring ODBC

Write the following command

$ odbcinst –j

result will be something like

unixODBC 2.2.7

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

USER DATA SOURCES..: /home/auser/.odbc.ini

So edit the file

$ vi /usr/local/etc/odbcinst.ini

and make the following changes

Basic odbcinst.ini:

This is where you configure your various odbc drivers.

[MySQL]

Description = MySQL ODBC MyODBC Driver

Driver = /usr/lib/libmyodbc3.so

Setup = /usr/lib/libodbcmyS.so

FileUsage = 1

Note: you may have to change the path to the .so files depending on where they are installed and even check the version libmyodbc3.so or libmyodbc.so.

Default location for: /usr/lib/

$ vi /usr/local/etc/ odbc.ini

and make the following changes

Basic odbc.ini:

This is where you configure your datasources.

[MySQL-asterisk]

Description = MySQL ODBC Driver Testing

Driver = MySQL #(This is same as [MySQL] in odbcinst.ini)

Socket = /var/run/mysqld/mysqld.sock

Server = 10.201.4.108 #(ip of the My Sql Server)

User = root #( Mysql User)

Password = dialnet #(password for the user)

Database = asterisk #(database name)

Option = 3

#Port = 3306

Test ODBC:

isql -v MySQL-asterisk myusername mypassword

Edit the file cdr_odbc.conf for providing asterisk the DSN name and enter the following

$ vi /etc/asterisk/cdr_odbc.conf

[global]

dsn=MySql-asterisk # same as the DSN name

username=root

password=dialnet

loguniqueid=yes

;dispositionstring=yes

;table=Employees ;”Cdr” to default table Name

;usegmtime ;set to “yes” to log in GMT

check that cdr_addon_mysql.c under /usr/src/asterisk-addons-1.2.5

recompile asterisk module

recompile asterisk

open the /etc/asterisk/extension.conf file and put the following lines

exten => s,2,MYSQL,"Connect connid hostname user password dbname";

exten => s,3,MYSQL,"Query resultid ${connid} SELECT username,credit FROM credit ";

exten => s,4,MYSQL,"Fetch fetchid ${resultid} datavar1 datavar2";

exten => s,5,GotoIf,"${fetchid}=1?s|6:s|8";

exten => s,6,blablabla ${datavar1} ${datavar2} (does blablabla, datavar1 = username, datavar2 = credit);

exten => s,7,Goto,s|4

exten => s,8,MYSQL,"Clear ${resultid}";

exten => s,9,MYSQL,"Disconnect ${connid}";