Aska

Setting up a MySQL 8 odbc connection for Asterisk 18

In today's post, we are going to set up an ODBC connection between our MySQL 8 database and Asterisk 18 server within Centos 7.

Requirements

• [Asterisk 18]
• [Mysql 8]
• Pre-installed Asterisk Database

Step 1 - Install the ODBC packages.

Let's start by installing the packages that are needed for our ODBC connection.

yum install unixODBC unixODBC-devel mysql-connector-odbc libtool-ltdl libtool-ltdl-devel

# ouput
Loaded plugins: fastestmirror
Determining fastest mirrors
epel/x86_64/metalink                                                                                                                                                                       | 9.7 kB  00:00:00     
 * base: d36uatko69830t.cloudfront.net
 * epel: nrt.edge.kernel.org
 * extras: d36uatko69830t.cloudfront.net
 * remi-safe: ftp.riken.jp
 * updates: d36uatko69830t.cloudfront.net
base                                                                                                                                                                                       | 3.6 kB  00:00:00     
epel                                                                                                                                                                                       | 4.7 kB  00:00:00     
extras                                                                                                                                                                                     | 2.9 kB  00:00:00     
mysql-connectors-community                                                                                                                                                                 | 2.6 kB  00:00:00     
mysql-tools-community                                                                                                                                                                      | 2.6 kB  00:00:00     
mysql57-community                                                                                                                                                                          | 2.6 kB  00:00:00     
remi-safe                                                                                                                                                                                  | 3.0 kB  00:00:00     
updates                                                                                                                                                                                    | 2.9 kB  00:00:00     
(1/11): base/7/x86_64/group_gz                                                                                                                                                             | 153 kB  00:00:00     
(2/11): extras/7/x86_64/primary_db                                                                                                                                                         | 222 kB  00:00:00     
(3/11): epel/x86_64/group_gz                                                                                                                                                               |  95 kB  00:00:00     
(4/11): epel/x86_64/updateinfo                                                                                                                                                             | 1.0 MB  00:00:00     
(5/11): mysql-connectors-community/x86_64/primary_db                                                                                                                                       |  68 kB  00:00:00     
(6/11): mysql-tools-community/x86_64/primary_db                                                                                                                                            |  83 kB  00:00:00     
(7/11): mysql57-community/x86_64/primary_db                                                                                                                                                | 247 kB  00:00:00     
(8/11): remi-safe/primary_db                                                                                                                                                               | 1.8 MB  00:00:00     
(9/11): updates/7/x86_64/primary_db                                                                                                                                                        | 4.7 MB  00:00:00     
(10/11): epel/x86_64/primary_db                                                                                                                                                            | 6.9 MB  00:00:01   
(11/11): base/7/x86_64/primary_db                                                                                                                                                          | 6.1 MB  00:05:48     
Resolving Dependencies
--> Running transaction check
---> Package libtool-ltdl.x86_64 0:2.4.2-22.el7_3 will be installed
---> Package libtool-ltdl-devel.x86_64 0:2.4.2-22.el7_3 will be installed
---> Package mysql-connector-odbc.x86_64 0:8.0.22-1.el7 will be installed
--> Processing Dependency: mysql-community-client-plugins = 8.0.22 for package: mysql-connector-odbc-8.0.22-1.el7.x86_64
---> Package unixODBC.x86_64 0:2.3.1-14.el7 will be installed
---> Package unixODBC-devel.x86_64 0:2.3.1-14.el7 will be installed
--> Finished Dependency Resolution

Step 2 - Get the right path of MySQL lib

Before we can get started with setting up our ODBC connection, we'll first need to know what MySQL library our server is using. Let's move to the user lib directory.

cd /usr/lib64

Here execute the following command.

ls -la | grep "libmyodbc"

# output
lrwxrwxrwx.  1 root root       14 Jan  6 03:19 libmyodbc5.so -> libmyodbc5w.so
-rwxr-xr-x.  1 root root   352784 Oct 30  2018 libmyodbc5w.so

As you can see above: our server is currently using the libmyodbc5w library.

Step 3 - Setting up our MySQL odbc connection

Let's open the odbc.ini file. Here we'll add the information that is needed for our ODBC connection.

vi /etc/odbc.ini

Add the following parameters to the file and save it. Make sure to set your own MySQL username and password.

[asterisk]
Driver=/usr/lib64/libmyodbc5w.so
Description=asterisk
Server=localhost
Port=3306
User=myuser
Password=myuserpassword
Database=asterisk
Option=3
Socket=

After this, reload the ODBC settings with the following command.

odbcinst -i -s -f /etc/odbc.ini

Step 4 - Testing the MySQL odbc connection

Testing the MySQL odbc connection can be done with the following command.

isql asterisk

# output
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
+---------------------------------------+

As you can see above: our ODBC connector is now connected to our Asterisk database.

Step 5 - Setting up the odbc connection in Asterisk 18

To set up the ODBC connection in Asterisk, we'll need to update our res_odbc.conf file. Let's move to the Asterisk directory and open this file.

cd /etc/asterisk
vi res_odbc.conf

Here we will add the following. Make sure to set your own MySQL username and password

[asterisk]
enabled => yes
dsn => asterisk
username => myusername
password => myuserpassword
pre-connect => yes

After this, we'll restart our Asterisk.

systemctl restart asterisk

Step 6 - Test Asterisk odbc connection

Let's test our ODBC connection in Asterisk, we'll first need to start our Asterisk CLI.

asterisk -cvvvvvvvvvvvvvvvr;

In the Asterisk CLI, we'll write the following.

odbc show

# ouput
ip-172-31-29-37*CLI> odbc show

ODBC DSN Settings
-----------------

  Name:   asterisk
  DSN:    asterisk
    Number of active connections: 1 (out of 1)
    Logging: Disabled

As you can see above, our MySQL 8 server is now connected to our Asterisk 18 through ODBC. You can now start developing your real-time database for Asterisk.