Asterisk and MySQL on Debian Wheezy (7.0)

Extend your Asterisk setup with MySQL so you can better integrate your phone system with existing applications (not just for call record keeping).

In this setup I will be installing Asterisk 1.8 and MySQL 5.5 on Debian Wheezy, many steps may be version/distro specific, however most will translate to slightly different platforms.

First, make sure your system is up to date:

#~: sudo apt-get update && sudo apt-get upgrade

Next install the packages we need:

#~: sudo apt-get install asterisk mysql-server mysql-client libmyodbc unixodbc

You’ll be prompted to set a root password for MySQL and an area code for Asterisk.

For testing, we’ll create a sample database, table and some data to query from Asterisk:

mysql -u root -p -e "CREATE DATABASE test_db; USE test_db; CREATE TABLE test_table (answer INT(5)); INSERT INTO test_table SET answer=42; GRANT ALL ON test_db.test_table TO 'asterisk'@'localhost' IDENTIFIED BY 'secret'; FLUSH PRIVILEGES;"

That should create a database called test_db, with a table test_table, a single field answer along with a user asterisk with a password of secret.

It’s always good practice to take a copy of the config files before you make any changes, we might as well backup the entire Asterisk config dir. Let’s copy it to our home directory.

#~: sudo cp -r /etc/asterisk ~/

Setup the ODBC config so that Asterisk can communicate with MySQL. Create (or edit) /etc/odbcinst.ini with the following content:

[MySQL]
Description     = MySQL driver
Driver          = libmyodbc.so
Setup           = libodbcmyS.so

…and /etc/odbc.ini:

[asterisk-mysql]
Description     = Asterisk
Driver          = MySQL
Database        = test_db
Server          = localhost
Port            =
Option          = 3
Socket          =

Add the following to the end of /etc/asterisk/res_odbc.conf

[asterisk-mysql]
enabled => yes
dsn => asterisk-mysql
database => test_db
username => asterisk
password => secret
pre-connect => yes

Reload the Asterisk config:

sudo asterisk -rx "core reload"

Asterisk can now talk happily with MySQL. Let’s use a test extension, along with an ODBC function, to play with our new MySQL connection and table :)

Edit /etc/asterisk/func_odbc.conf and add the following function to the end:

; ODBC TEST
[TEST]
dsn=asterisk-mysql
readsql=SELECT answer FROM test_table *Note: For versions prior to 1.8 use read instead of readsql.*

Edit extensions.conf, and right under the [default] section (context) add the following:

; MySQL Test Extension
exten => 250,1,Answer()
exten => 250,2,Set(NUMBERVAR=${ODBC_TEST()})
exten => 250,3,SayNumber(${NUMBERVAR})
exten => 250,5,Hangup()

250 is the extension number. ODBC_TEST() calls the [TEST] function we specified in the previous step. SayNumber() reads out the number returned from the table.

If you haven’t yet, create yourself a SIP extension so you can do some testing!

Add the following to the end of /etc/asterisk/sip.conf:

[2000]
type=friend
username=2000
secret=2000
regexten=2000
host=dynamic

…and finally, reload Asterisk again:

sudo asterisk -rx "core reload"

Connect your favourite SIP soft-phone and dial 250, you should hear 42 :)

You can now read and write to a MySQL backend database throughout Asterisk using custom functions in func_odbc.conf. The possibilities of integrating Asterisk with your application are endless!

Mike Mead

Mike Mead

Asterisk and MySQL on Debian Wheezy (7.0)