How to Query a Database from MySQL with Asterisk 1.6 ODBC

I’ve been administering Asterisk servers for many years and everyday I’m learning something new.  As the size of the projects have gotten bigger, so have the demands of my customers.  One common request is database integration.  In the past I’ve used external AGI scripts to run database queries to return values to Asterisk.  But more recently I’ve been avoiding AGI in favor of direct access SQL.  This is achieved by building ODBC modules into Asterisk at build time.  Let me tell you, it’s worth it!

Asterisk & ODBC provide the ability to easily update and retrieve data by defining SQL statements as special variables that can be called from the dial plan.  For instance, we could bill ‘long distance’, take credit cards and let Asterisk access other types of information.  In my tutorial we will configure Asterisk to dial a variable that represents a SQL statement.  This could also be done by calling an AGI script, but why do the extra work?

This ‘How to’ is specifically geared towards MySQL.  However, if you set the up the ODBC driver correctly you can use any SQL server you like.  Which leads me to my next thought, before you start, make sure ODBC is set up correctly.  Follow one of my previous tutorials:

configure Linux ODBC for MS-SQL

configure Linux ODBC for MYSQL

Once you do this we are ready to continue…

The goals of this post:

– create MySQL table with example data
– configure res_odbc.conf
– configure func_odbc.conf
– configure extensions.conf for query



How to use the ODBC driver to Connect to MySQL database in Fedora 10

A few weeks back I covered connecting ODBC to MS-SQL, but today we are going to do the same thing with MySQL.  The process is very similar.  One might ask, why use ODBC driver in the first place?  The reason is simple, there are many flavors of SQL and the ODBC driver allows one standard to connect them.  For that reason many software developers use ODBC for connecting their applications to SQL.

For my purposes, the Asterisk PBX platform can use ODBC to call ‘dial plan’ functions from the database, but that is another article!  This post assumes you have a working MySQL server and at least one table with data to pull from.

Goal of this Post:

– Install ODBC driver for MySQL
– configure odbc.ini
– configure odbcinst.ini
– verify connectivity with isql



How to Change the root Password in MySQL and Backup with mysqldump

I’m not DBA, but most Linux system administrators need to at least know a little about SQL. Like adding users, making simple databases, changing passwords, backups, etc… This article describes backing up a database to a fresh machine. In my case I happen to be backing up my wordpress blog. However these steps can applied to Sugar CRM, Joomla, or any database driven site that uses MySQL. The following tutorial does not completely cover backing up a wordpress blog; it only covers the database portion.  Which applies to many LAMP projects.  Enjoy!

The Goals of this Post:

– to remind myself how to backup databases

LAB: Backup WordPress Blog Database to new machine

I’m changing the default password of nothing to the same password as my root user. Please note that mysql ‘root’ user is not the same as Linux system root. Also, they can be different passwords and probably should.

[root@mattcom1 ~]# mysqladmin -u root password mynewpassword
[root@mattcom1 ~]# mysql -u root -p
Enter password:
[root@mattcom1 ~]#

Create a blank database on the machine we will repopulating.

Welcome to the MySQL monitor. Commands end with ; or \g
Your MySQL connection id is 10
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database savelono;

Use mysqldump command to back up the database on the original server. The database can be named anything, I usually back up to with a ‘DATE-NAME.sql’ convention.

[root@savelono ~]# mysqldump -u root savelono > savelono-04-24-09.sql

Now copy your sql database to the new server and import it.

[root@mattcom1 ~]# mysql -u root -p -D savelono < savelono-04-24-09.sql

That’s it!  Piece of cake.


VoiceIP Solutions offers Asterisk PHP GUI for large scale deployments

VoiceIP Solutions
is a Asterisk ‘consulting & deployment’ company in Seattle Washington.  They deploy Asterisk solutions for businesses of all sizes.  From small offices to universities and call centers.  They have sites deployed all over the United States, but mostly on the West Coast.  I’ve been following them for some time; I guess they started deploying Asterisk before 1.2 was released.  I talked to one of their sales rep’s(I think his name was Liam) about the business and wondered if they had done any development work?  He told me that they had done some PHP work for managing larger installs and proceeded to direct me to one of there engineer/developers.



Errors loading Asterisk addons, CDR(Call Detail Records) to MySQL

The other day I was doing an Asterisk 1.2 –> 1.4 upgrade.  I have a MySQL database that Asterisk records the Call Detail Record’s(CDR) too.  I deleted the asterisk 1.2 modules, then compiled/installed asterisk 1.4 & asterisk-addons 1.4.  Being that I hadn’t set up the CDR MySQL stuff in a while I loaded the res_mysql.conf, but I copied it from the cdr_addon.conf file by accident.  Because the two files have similer syntax It took me a while to figure this one out.  I checked all the passwords and I even checked the Asterisk CLI to see if the module loaded.  MySQL was populated with the same tables as before.  I also kept seeing this error (see below).

Possibly a mixed up addon file.

Possibly a mixed up addon file.

The error:

[Mar 15 03:25:44] ERROR[23793]: res_config_mysql.c:629 mysql_reconnect: MySQL RealTime: Failed to connect database server asterisk on  (err 2002). Check debug for more info.

The res_mysql & cdr_mysql have very similer config files, but you can’t ‘cut & paste’ between them.

Sample configuration for res_mysql.conf:

;dbhost =
;dbname = asterisk
;dbuser = myuser
;dbpass = mypass
;dbport = 3306
;dbsock = /tmp/mysql.sock

; Sample configuration for cdr_mysql.conf:


Be careful not to mix the two between upgrades!