There are TWO steps to get CDRs working with a remote MySQL database. This should work on FreePBX Distro versions 6.12 and 10.13 running either Asterisk 11 or 13. Other versions or non-distro systems may also work, but have not been tested. There are legacy options to use non-mysql databases, but it's not a supported configuration. It is assumed that the remote MySQL database has already been created with user credentials set to appropriate permissions.
- In FreePBX, Advanced settings, scroll to the CDR section and enter necessary details for host, database name, user, password, tables, etc. You can leave fields empty if they are the same default value on the remote database as they are on the local. Click submit, and apply settings. If the details are correct, and the user details have the appropriate permissions, the apply settings will work. If not, you have to address your connectivity issues. Once this step is done, you can browse to Reports, CDR and view the records in the remote database.
- Step one done, you must configure asterisk to write the CDR records to the new database. At the Asterisk CLI, type: "odbc show all", you will see output like:
*CLI> odbc show all ODBC DSN Settings ----------------- Name: asteriskcdrdb DSN: MySQL-test Last connection attempt: 1969-12-31 20:00:00
Take note of the DSN.
Now locate the file: /etc/odbc.ini and create a new section that matches the DSN like this:
[MySQL-test] Description=MySQL connection to remote 'test' database driver=MySQL user=remote_user password=password server=hostname.fqdn.com database=test Port=3306 option=BIG_PACKETS=1 Charset=utf8
Save the file and restart asterisk with 'fwconsole restart' (or 'amportal restart'). Going back to asterisk again, the earlier command will now show something a bit different:
*CLI> odbc show all ODBC DSN Settings ----------------- Name: asteriskcdrdb DSN: MySQL-test Last connection attempt: 1969-12-31 20:00:00 Pooled: No Connected: Yes
You want to confirm that the database is now connected. Now when you make calls, they get written to the remote database and you can see them in FreePBX. This technique should work in both FreePBX 12 and 13 with Asterisk versions 11 and 13.
Configuring FreePBX with Remote Settings Database
Locate the file /etc/freepbx.conf and change values as necessary.