Page tree
Skip to end of metadata
Go to start of metadata

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

Modify Backup Sets Required

If you are using a warm spare backup you must disable CDR records from being backed up. This is because you will be restoring to the same database you are backing up from and may result in loss of data.

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.

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.