Remote CDR

If you process a large amount of calls, or have several years worth of call records, you may find yourself with huge database of call records. This can cause a significant slowdown in backups, takes hours to complete upgrades, and generally have overall poor performance. Not to mention if you are doing any sort of custom CDR reporting by accessing the database directly, this puts undesired load on your PBX system. The solution to this is to diversify the services.

The advanced setting module lets you set the location of your remote cdr database, but you must make some additional modifications to actually use it. Bellow I will show you how to enable CDR records to be stored on a remote database.

Common use case:

• Large CDR database
• Business Requirement to encrypt database.
• PBX Performance gains (backups & compression are expensive and time consuming)
• Centralized database management.
• Use existing database for streamlined optimization.
• Faster Warm Spare failovers
• Alternative HA setup.

Step-by-step guide

In this example we have FBPX1 with an IP address of 192.168.10.10 and a database (DB01) with an IP address of 192.168.10.50

1. Install a database server on a remote server, or VM. We use MariaDB as a drop in replacement for MySQL.

   apt-get install mariadb-server

2. Verify connectivity from FreePBX to the remote database.

   mysql --host=192.168.10.50 -uroot -p asteriskcdrdb

3. Enable read-only settings, and editing of read-only settings.
   
4. Update Remote database settings.
   

5. Stop FreePBX 

   fwconsole stop

6. Update odbc.ini on the FreePBX system. Replace the server=localhost with the address of the remote database.

   nano /etc/odbc.ini

   [MySQL-asteriskcdrdb]
   Description=MySQL connection to 'asterisk' database
   driver=MySQL
   server=192.168.10.50
   database=asteriskcdrdb
   Port=3306
   #Socket=/var/lib/mysql/mysql.sock
   option=3

   or if you want to specify user and password

   nano /etc/odbc.ini

   [MySQL-asteriskcdrdb]
   Description=MySQL connection to 'asterisk' database
   driver=MySQL
   server=192.168.10.50
   User=THE_user
   Password=THE_password
   database=asteriskcdrdb
   Port=3306
   #Socket=/var/lib/mysql/mysql.sock
   option=3

   Note: You can change the port as well if necessary.

7. Start FreePBX

   fwconsole start

8. Make a call and verify data
9. Check Remote CDR database for call record

Optimizing Database

Depending on the database you choose you may have additional options to increase the system's responsiveness.

• Consider adding a cronjob to archive or prune old data from the database. 
• Consider adding a replication slave.
• Consider adding indexes for fields you search on regularly.

Warm Spare Backups

Backing up CDR records

Now that the cdr database is no longer on the FreePBX system. You may want to use standard backup tools and procedures for backing up your database. In our example we use MariaDB and btrfs file system. This allowed us to have instant snapshots without stopping the database, and use our standard tools to perform the database backup.

Depending on how you installed your database, you may or may not have the ability to take instant snapshots, but you should be able to use standard processes for making backups.

• mysqldump
• Xtrabackup
• http://snapper.io/

If you wish to use the FreePBX backup module to perform your database backup, you should create a separate backup job to backup the CDR record.

Notes:

When relocating your CDR database you should take precaution to assure that the remote database is available, that the network link is stable, and that it has enough capacity to handle your volume.

Consider adding a monitoring service to monitor the remote database state, your network link availability and bandwidth. 

Related articles

• Page:
  Remote CDR