Under Construction! Please ignore this page until this sign is gone.
Connect Asterisk to a MySQL back-end through ODBC
This is a short tutorial on how to quickly setup Asterisk to use MySQL, the ODBC MySQL connector and ODBC. We'll use CentOS 6 as the OS in this tutorial. However, the same essential steps apply for any popular Linux distro.
Installing and Configuring MySQL
- Install MySQL server package and start the DB service.
- Secure the installation if appropriate.
- Configure a user and database for Asterisk in MySQL
Install MySQL server package and start the DB service
# sudo yum install mysql-server # sudo service mysqld start
Secure the installation if appropriate
# sudo /usr/bin/mysql_secure_installation
Configure a user and database for Asterisk in MySQL
If you want to use a GUI to manage your database then now is the time to set that GUI up and use it to create your asterisk user. Otherwise we will provide basic instructions for user setup below.
First, login using the root password you set earlier.
# mysql -u root -p
Now verify you are at the MySQL command prompt. It should look like "mysql>". Then enter the following commands:
# CREATE USER 'asterisk'@'%' IDENTIFIED BY 'replace_with_strong_password'; # CREATE DATABASE asterisk; # GRANT ALL PRIVILEGES ON asterisk.* TO 'asterisk'@'%'; # exit
# mysql -u asterisk -p asterisk # exit
Install ODBC and the MySQL ODBC connector
Be sure you have followed the previous sections as we presume you already have MySQL installed on your CentOS server along with a database and user for Asterisk configured. The database name should be 'asterisk' and the username should be 'asterisk'.
Install the latest unixODBC and GNU Libtool Dynamic Module Loader packages
The development packages are necessary as well, since later Asterisk will need to use them when building ODBC related modules.
# sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
Install the latest MySQL ODBC connector
# sudo yum install mysql-connector-odbc
Configure ODBC and the MySQL ODBC connector
Configure odbcinst.ini for ODBC
With recent UnixODBC versions the configuration should already be done for you in the /etc/odbcinst.ini file.
Verify that you have the following configuration:
# Driver from the mysql-connector-odbc package # Setup from the unixODBC package [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so Setup = /usr/lib/libodbcmyS.so Driver64 = /usr/lib64/libmyodbc5.so Setup64 = /usr/lib64/libodbcmyS.so FileUsage = 1 Pooling = Yes CPTimeout = 120
We configure pooling for the connection as that is probably desired by most users. Consult the unixODBC documentation to learn more about the function of ODBC connection pooling.
There may also be configuration for PostgreSQL which you can comment out if you are not planning to setup PostgreSQL as well. Comments begin the line with a hash (#) symbol.
You can also call
odbcinst to query the driver, verifying that the configuration is found.
# odbcinst -q -d
The output should read simply "[MySQL]"
Configure the MySQL ODBC connector
Now we'll configure the /etc/odbc.ini file to create a DSN (Data Source Name) for Asterisk. The file may be empty, so you'll have to copy-paste from this example or write this from scratch.
Add the following to /etc/odbc.ini
[asterisk-connector] Description = MySQL connection to 'asterisk' database Driver = MySQL Database = asterisk Server = localhost Port = 3306 Socket = /var/lib/mysql/mysql.sock
You may want to verify that mysql.sock is actually in the location specific here. It will differ on some systems depending on your configuration.
Test the ODBC Data Source Name connection
Now is a good time to test your database by connecting to it and performing a query. The unixODBC package provides
isql; a command line utility that allows you to connect to the Data Source, send SQL commands to it and receive results back. The syntax used is:
isql -v dsn_name db_username db_password
So, for our purposes you would enter:
# isql -v asterisk-connector asterisk replace_with_strong_password
It is important to use the -v flag so that if isql runs into a problem you will be alerted of any diagnostics or errors available.
At this point you should get an SQL prompt. Run the following command:
SQL> select 1
You should see some simple results if the query is successful. Then you can exit.
SQL> select 1 +---------------------+ | 1 | +---------------------+ | 1 | +---------------------+ SQLRowCount returns 1 1 rows fetched SQL> quit
Configuring Asterisk to Use the New ODBC and MySQL Install
Now you have a MySQL database, ODBC and an ODBC MySQL connector installed and basically configured. The next step is to recompile Asterisk so that the ODBC modules which required the previously mentioned items can now be built. Once those modules exist, then you can configure the proper configuration files in Asterisk depending on what information you want to write to or read from MySQL.
Getting the right Asterisk modules
If you already had Asterisk installed from source and the modules you need are already selected by default in menuselect - then the recompilation process could be as simple as navigating to the Asterisk source and running a few commands.
# cd ~/asterisk-source/ # ./configure # make && make install
Otherwise you should follow the typical Asterisk installation process to make sure modules such as res_odbc, res_config_odbc, cdr_odbc, cdr_adaptive_odbc and func_odbc have their dependencies fulfilled and that they will be built.
Configuring Asterisk's ODBC connection
The basic configuration for an Asterisk ODBC connection is handled in res_odbc.conf. You should check out the Configuring res_odbc page and follow it using the DSN and database username and password you setup earlier.
After you have the connection set up in Asterisk you are ready to then configure your database tables with the proper schema depending on what exactly you want to do with them. Asterisk comes with some helpful tools to do this, such as Alembic. See the Managing Realtime Databases with Alembic section to get started with Alembic if you are working towards an Asterisk Realtime setup.