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}";