Database – Heros Eloi

Asterisk + ODBC + Mysql

The latest versions of the Asterisk PBX server connect to the Mysql database server by using ODBC. In this tutorial, I will show you how to set this up.

My test environment is CentOS Linux released on 7.9.2009, Asterisk 16.16.0, unixODBC 2.3.1, and Mysql Community Server 8.

I am taking for granted that you already have your Mysql installed and running. If you have not taken this step yet, you should use a tutorial like this one.

1 – Install the Unix ODBC:

# yum -y install unixODBC

2 – Download the official Mysql library connector from: https://dev.mysql.com/downloads/connector/odbc/

# wget https://cdn.mysql.com//Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.23-linux-glibc2.12-x86-64bit.tar.gz

Decompress the package in /usr/local directory or wherever you like.

# tar -zxvf mysql-connector-odbc-8.0.23-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local

3 – Edit the file /etc/odbcinst.ini and insert the following code block:

[MySQL_ANSI]
Driver=/usr/local/mysql-odbc/lib/libmyodbc8a.so
sageCount=1

4 – Edit the file /etc/odbc.ini and create your connection:

[asterisk-connector]
Description = MySQL connection
Driver = MySQL_ANSI
Database = asterisk
Server = 127.0.0.1
Port = 3306

5 – Now, you must configure your Asterisk to use the connection. The file which contains that information is /etc/asterisk/res_odbc.conf

[asterisk]
enabled => yes
dsn => asterisk-connector
username => user
password => secret
pre-connect => yes
max_connections => 100

6 – Finally, make sure that Asterisk is using the ODBC module, take a look at the file /etc/asterisk/modules.conf and find the following line:

preload => res_odbc.so

If you have any questions, please use the comment box below.

Running Mysql queries using Login Profile

The newest versions of the Mysql server, such as the 8, presents the following message when users try to use the database password in the command line:

# mysql -u root -p"password" -e "show databases"

mysql: [Warning] Using a password on the command line interface can be insecure.

In order to prevent this warning message, you just need to create a password profile, in this example we going to create one called “local”:

# mysql_config_editor set --login-path=local \
--host=localhost --port=3306 \
--socket=/var/lib/mysql/mysql.sock \
--user=root --password

Now, you can easily run queries without providing the password, just use the “local” profile:

# /usr/bin/mysql --login-path=local -h localhost -e "show databases"