How to configure Asterisk Realtime Database Connection on Debian 12
To Configure Asterisk Realtime Database Connection On Debian 12
Introduction:
Asterisk is a powerful open-source PBX (Private Branch Exchange) software that offers Realtime Database Connectivity. This feature enables dynamic configuration and management of telephony resources directly from a database, enhancing flexibility, scalability, and ease of administration when deploying and managing VoIP (Voice over IP) systems.
Procedure:
Step 1: Check the OS version by using following command.
root@linuxhelp:~# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
Step 2: Install the MySQL by using following command.
root@linuxhelp:~# apt install default-mysql-server -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
galera-4 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl
libdaxctl1 libdbd-mariadb-perl libdbi-perl libfcgi-bin libfcgi-perl
libfcgi0ldbl libhtml-template-perl libmariadb3 libndctl6 libpmem1
libsigsegv2 libterm-readkey-perl liburing2 mariadb-client
mariadb-client-core mariadb-common mariadb-plugin-provider-bzip2
mariadb-plugin-provider-lz4 mariadb-plugin-provider-lzma
mariadb-plugin-provider-lzo mariadb-plugin-provider-snappy mariadb-server
mariadb-server-core pv rsync socat
Suggested packages:
gawk-doc libmldbm-perl libnet-daemon-perl libsql-statement-perl
libipc-sharedcache-perl mailx mariadb-test netcat-openbsd doc-base
python3-braceexpand
The following NEW packages will be installed:
default-mysql-server galera-4 gawk libcgi-fast-perl libcgi-pm-perl
libconfig-inifiles-perl libdaxctl1 libdbd-mariadb-perl libdbi-perl
libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl libndctl6
libpmem1 libsigsegv2 libterm-readkey-perl liburing2 mariadb-client
mariadb-client-core mariadb-plugin-provider-bzip2
mariadb-plugin-provider-lz4 mariadb-plugin-provider-lzma
mariadb-plugin-provider-lzo mariadb-plugin-provider-snappy mariadb-server
mariadb-server-core pv rsync socat
The following packages will be upgraded:
libmariadb3 mariadb-common
Setting up rsync (3.2.7-1) ...
rsync.service is a disabled or a static unit, not starting it.
Setting up libcgi-fast-perl (1:2.15-1) ...
Setting up mariadb-client-core (1:10.11.6-0+deb12u1) ...
Setting up libdbd-mariadb-perl (1.22-1+b1) ...
Setting up mariadb-server-core (1:10.11.6-0+deb12u1) ...
Setting up mariadb-client (1:10.11.6-0+deb12u1) ...
Setting up mariadb-server (1:10.11.6-0+deb12u1) ...
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /l
ib/systemd/system/mariadb.service.
Setting up mariadb-plugin-provider-bzip2 (1:10.11.6-0+deb12u1) ...
Setting up mariadb-plugin-provider-lzma (1:10.11.6-0+deb12u1) ...
Setting up mariadb-plugin-provider-lzo (1:10.11.6-0+deb12u1) ...
Setting up mariadb-plugin-provider-lz4 (1:10.11.6-0+deb12u1) ...
Setting up mariadb-plugin-provider-snappy (1:10.11.6-0+deb12u1) ...
Setting up default-mysql-server (1.1.0) ...
Processing triggers for libc-bin (2.36-9+deb12u3) ...
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for mariadb-server (1:10.11.6-0+deb12u1) ...
Step 3: Login to the MySQL by using following command.
root@linuxhelp:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Step 4: Create database by using following command.
MariaDB [(none)]> create database asterisk;
Query OK, 1 row affected (0.001 sec)
Step 5: Create user by using following command.
MariaDB [(none)]> create user 'asterisk'@'localhost' identified by 'asterisk@123';
Query OK, 0 rows affected (0.001 sec)
Step 6: Make Grant privileges to the user by using following command.
MariaDB [(none)]> grant all privileges on *.* to 'asterisk'@'localhost';
Query OK, 0 rows affected (0.001 sec)
Step 7: Refresh the privileges by using following command.
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
Step 8: Exit from MySQL by using following command.
MariaDB [(none)]> exit;
Bye
Step 9: Install unixodbc by using following command.
root@linuxhelp:~# apt install unixodbc unixodbc-dev -y
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
unixodbc-dev is already the newest version (2.3.11-2+deb12u1).
The following NEW packages will be installed:
unixodbc
0 upgraded, 1 newly installed, 0 to remove and 72 not upgraded.
Need to get 28.0 kB of archives.
After this operation, 100 kB of additional disk space will be used.
Get:1 http://deb.debian.org/debian bookworm/main amd64 unixodbc amd64 2.3.11-2+deb12u1 [28.0 kB]
Fetched 28.0 kB in 0s (129 kB/s)
Selecting previously unselected package unixodbc.
(Reading database ... 170258 files and directories currently installed.)
Preparing to unpack .../unixodbc_2.3.11-2+deb12u1_amd64.deb ...
Unpacking unixodbc (2.3.11-2+deb12u1) ...
Setting up unixodbc (2.3.11-2+deb12u1) ...
Processing triggers for man-db (2.11.2-2) ...
Step 10: Download the Dependencies of mysql-connector-odbc by using following command.
root@linuxhelp:~# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-plugins_8.3.0-1debian12_amd64.deb
--2024-06-20 05:05:33-- https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-plugins_8.3.0-1debian12_amd64.deb
Resolving downloads.mysql.com (downloads.mysql.com)... 23.58.39.183, 2600:140f:9800:1b8::2e31, 2600:140f:9800:191::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.58.39.183|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-8.3/mysql-community-client-plugins_8.3.0-1debian12_amd64.deb [following]
--2024-06-20 05:05:34-- https://cdn.mysql.com/archives/mysql-8.3/mysql-community-client-plugins_8.3.0-1debian12_amd64.deb
Resolving cdn.mysql.com (cdn.mysql.com)... 23.58.42.45, 2600:140f:9800:1b1::1d68, 2600:140f:9800:197::1d68
Connecting to cdn.mysql.com (cdn.mysql.com)|23.58.42.45|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2353436 (2.2M) [application/x-debian-package]
Saving to: ‘mysql-community-client-plugins_8.3.0-1debian12_amd64.deb’
mysql-community-cli 100%[===================>] 2.24M 11.2MB/s in 0.2s
2024-06-20 05:05:34 (11.2 MB/s) - ‘mysql-community-client-plugins_8.3.0-1debian12_amd64.deb’ saved [2353436/2353436]
Step 11: Install the Dependencies of mysql-connector-odbc by using following command.
root@linuxhelp:~# dpkg -i mysql-community-client-plugins_8.3.0-1debian12_amd64.deb
Selecting previously unselected package mysql-community-client-plugins.
(Reading database ... 170268 files and directories currently installed.)
Preparing to unpack mysql-community-client-plugins_8.3.0-1debian12_amd64.deb ...
Unpacking mysql-community-client-plugins (8.3.0-1debian12) ...
Setting up mysql-community-client-plugins (8.3.0-1debian12) ...
Processing triggers for libc-bin (2.36-9+deb12u3) ...
Step 12: Download the mysql-connector-odbc by using following command.
root@linuxhelp:~# wget https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc_8.3.0-1debian12_amd64.deb
--2024-06-20 05:06:24-- https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc_8.3.0-1debian12_amd64.deb
Resolving downloads.mysql.com (downloads.mysql.com)... 23.58.39.183, 2600:140f:9800:191::2e31, 2600:140f:9800:1b8::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.58.39.183|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-connector-odbc-8.3/mysql-connector-odbc_8.3.0-1debian12_amd64.deb [following]
--2024-06-20 05:06:24-- https://cdn.mysql.com/archives/mysql-connector-odbc-8.3/mysql-connector-odbc_8.3.0-1debian12_amd64.deb
Resolving cdn.mysql.com (cdn.mysql.com)... 23.58.42.45, 2600:140f:9800:197::1d68, 2600:140f:9800:1b1::1d68
Connecting to cdn.mysql.com (cdn.mysql.com)|23.58.42.45|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1780780 (1.7M) [application/x-debian-package]
Saving to: ‘mysql-connector-odbc_8.3.0-1debian12_amd64.deb’
mysql-connector-odb 100%[===================>] 1.70M 11.1MB/s in 0.2s
2024-06-20 05:06:25 (11.1 MB/s) - ‘mysql-connector-odbc_8.3.0-1debian12_amd64.deb’ saved [1780780/1780780]
Step 13: Install the mysql-connector-odbc by using following command.
root@linuxhelp:~# dpkg -i mysql-connector-odbc_8.3.0-1debian12_amd64.deb
Selecting previously unselected package mysql-connector-odbc:amd64.
(Reading database ... 170281 files and directories currently installed.)
Preparing to unpack mysql-connector-odbc_8.3.0-1debian12_amd64.deb ...
Unpacking mysql-connector-odbc:amd64 (8.3.0-1debian12) ...
Setting up mysql-connector-odbc:amd64 (8.3.0-1debian12) ...
Registering Unicode driver from in file
Success: Usage count is 1
Registering ANSI driver from in file
Success: Usage count is 1
Step 14: Download the mysql-connector-odbc-setup by using following command.
root@linuxhelp:~# wget https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb
--2024-06-20 05:06:59-- https://downloads.mysql.com/archives/get/p/10/file/mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb
Resolving downloads.mysql.com (downloads.mysql.com)... 23.58.39.183, 2600:140f:9800:1b8::2e31, 2600:140f:9800:191::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.58.39.183|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-connector-odbc-8.3/mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb [following]
--2024-06-20 05:07:00-- https://cdn.mysql.com/archives/mysql-connector-odbc-8.3/mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb
Resolving cdn.mysql.com (cdn.mysql.com)... 23.58.42.45, 2600:140f:9800:1b1::1d68, 2600:140f:9800:197::1d68
Connecting to cdn.mysql.com (cdn.mysql.com)|23.58.42.45|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1486428 (1.4M) [application/x-debian-package]
Saving to: ‘mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb’
mysql-connector-odb 100%[===================>] 1.42M --.-KB/s in 0.1s
2024-06-20 05:07:00 (10.9 MB/s) - ‘mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb’ saved [1486428/1486428]
Step 15: Install the mysql-connector-odbc-setup by using following command.
root@linuxhelp:~# dpkg -i mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb
Selecting previously unselected package mysql-connector-odbc-setup:amd64.
(Reading database ... 170293 files and directories currently installed.)
Preparing to unpack mysql-connector-odbc-setup_8.3.0-1debian12_amd64.deb ...
Unpacking mysql-connector-odbc-setup:amd64 (8.3.0-1debian12) ...
Setting up mysql-connector-odbc-setup:amd64 (8.3.0-1debian12) ...
Registering Unicode driver using setup package
Success: Usage count is 0
Success: Usage count is 1
Registering ANSI driver using setup
Success: Usage count is 0
Success: Usage count is 1
Step 16: Check the Driver update in the odbcinst.ini file by using following command.
root@linuxhelp:~# vim /etc/odbcinst.ini
[MySQL ODBC 8.3 Unicode Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1
[MySQL ODBC 8.3 ANSI Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1
Step 17: Make the configuration for database connection with odbc by using following command.
root@linuxhelp:~# vim /etc/odbc.ini
[asterisk]
Description = MySQL Connection to Asterisk Database
Driver = MySQL ODBC 8.3 Unicode Driver
Server = localhost
Database = asterisk
User = asterisk
Password = asterisk@123
Port = 3306
Option = 3
Step 18: Check the odbc connection by using following command.
root@linuxhelp:~# isql -v asterisk
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL> quit
Step 19: Configure the asterisk odbc connection by using following command.
root@linuxhelp:~# vim /etc/asterisk/res_odbc.conf
[mysql2]
enabled => yes
dsn => asterisk
username => asterisk
password => asterisk@123
pre-connect => yes
Step 20: Login to the asterisk console by using following command.
root@linuxhelp:~# asterisk -rvvvvvvvvvvvvvvvvvvvvvvvvv
Asterisk 20.4.0, Copyright (C) 1999 - 2022, Sangoma Technologies Corporation and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'core show license' for details.
=========================================================================
Connected to Asterisk 20.4.0 currently running on linuxhelp (pid = 961)
linuxhelp*CLI>
Step 21: Load the asterisk odbc module by using following command.
linuxhelp*CLI> module load res_odbc
Unable to load module res_odbc
Command 'module load res_odbc' failed.
[Jun 20 05:20:49] ERROR[4021]: loader.c:283 module_load_error: res_odbc has one or more unknown dependencies.
Step 22: Load the asterisk odbc dependency module to troubleshoot the error by using following command.
linuxhelp*CLI> module load res_odbc_transaction
Loaded res_odbc_transaction
== Registered application 'ODBC_Commit'
== Registered application 'ODBC_Rollback'
== Registered custom function 'ODBC'
Loaded res_odbc_transaction.so => (ODBC transaction resource)
Step 23: Again Load the asterisk odbc module by using following command.
linuxhelp*CLI> module load res_odbc
Loaded res_odbc
[Jun 20 05:21:30] NOTICE[4021]: res_odbc.c:697 load_odbc_config: Registered ODBC class 'mysql2' dsn->[asterisk]
Loaded res_odbc.so => (ODBC resource)
Step 24: Check the connection of the odbc by using following command.
linuxhelp*CLI> odbc show
ODBC DSN Settings
-----------------
Name: mysql2
DSN: asterisk
Number of active connections: 1 (out of 1)
Logging: Disabled
Conclusion:
We have reached the end of this article. In this guide, we have walked you through the steps required to configure Asterisk Realtime Database Connection on Debian 12. Your feedback is much welcome.
Q
What are the benefits of using Asterisk Realtime Database Connection?
A
Dynamic Updates: Enables real-time changes to configurations without needing to reload or restart Asterisk, improving operational flexibility.
Centralized Management: Simplifies administration by storing all configuration data in a single database, reducing the complexity of managing multiple configuration files.
Scalability: Supports large-scale deployments by leveraging database capabilities for efficient data storage and retrieval.
Integration: Facilitates integration with external applications and services through direct access to configuration data stored in the database.
Centralized Management: Simplifies administration by storing all configuration data in a single database, reducing the complexity of managing multiple configuration files.
Scalability: Supports large-scale deployments by leveraging database capabilities for efficient data storage and retrieval.
Integration: Facilitates integration with external applications and services through direct access to configuration data stored in the database.
Q
How do I configure Asterisk to use Realtime Database Connection?
A
Configuration involves several steps:
Ensure Asterisk is compiled with Realtime module support (res_config_realtime).
Define database connection settings in Asterisk configuration files (res_odbc.conf or res_config_realtime.conf).
Create and populate database tables with the necessary configuration data, following Asterisk's Realtime Architecture schema.
Restart Asterisk to apply configuration changes and begin fetching data from the database dynamically.
Ensure Asterisk is compiled with Realtime module support (res_config_realtime).
Define database connection settings in Asterisk configuration files (res_odbc.conf or res_config_realtime.conf).
Create and populate database tables with the necessary configuration data, following Asterisk's Realtime Architecture schema.
Restart Asterisk to apply configuration changes and begin fetching data from the database dynamically.
Q
Which databases are supported by Asterisk Realtime Database Connection?
A
Asterisk Realtime supports a range of databases, including MySQL, PostgreSQL, SQLite, and ODBC-compliant databases. This flexibility allows organizations to use their preferred database platform based on scalability, performance, and integration requirements.
Q
What is Asterisk Realtime Database Connection, and how does it differ from traditional configuration files?
A
Asterisk Realtime Database Connection allows Asterisk to fetch configuration data (such as SIP users, dial plans, queues) directly from a database rather than static configuration files (like sip.conf or extensions.conf). This dynamic approach enables real-time updates and simplifies management by centralizing configuration data in a database.
Data Integrity: Implement proper indexing and data validation in database tables to optimize query performance and maintain data integrity.
Backup and Recovery: Establish backup procedures for the database to safeguard configuration data and ensure continuity in case of database failures or data corruption.
Security: Implement security best practices, such as restricting database access to authorized users and encrypting sensitive data, to protect configuration information stored in the database.
Compatibility: Verify compatibility between Asterisk versions and database systems to ensure smooth operation and minimize compatibility issues during upgrades or migrations.