Remote CDR Database Installation Guide
- Nathaniel Halbrooks
Please keep in mind, this is for informational purposes only. Sangoma does not guarantee this as a feature of FreePBX/PBXact. Support may be limited when using a remote CDR database.
Install CentOS 7 onto your new database server. You can use a different OS if you wish, but the guide below was done on CentOS 7. CentOS 7 was used because it is what SNG7 which is the FreePBX OS is based on.
Log into the database server and install MariaDB. Then answer all the questions in the mysql_secure_installation wizard. Once done start the service and allow the FreePBX IP in the firewall using the commands below.
yum install mariadb*
systemctl enable mariadb.service
firewall-cmd --permanent --zone=trusted --add-source=<IP OF FreePBX>/32
firewall-cmd --reload
mysql_secure_installation |
Generate a secure 20 character password using the command below.
Command: < /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c20
Output: v91bxf80h7dQGMMHb3pF |
Now that MariaDB is installed create the user account and the AsteriskCDRDB database using the commands below. Ensure to replace "v91bxf80h7dQGMMHb3pF" below with the secure password you generated in step #3.
mysql
CREATE USER 'freepbxuser'@'%' IDENTIFIED BY 'v91bxf80h7dQGMMHb3pF';
GRANT USAGE ON *.* TO 'freepbxuser'@'%';
GRANT ALL PRIVILEGES ON `asteriskcdrdb`.* TO 'freepbxuser'@'%';
flush privileges;
CREATE DATABASE asteriskcdrdb;
exit; |
5a) Log into FreePBX and verify connectivity to the database server from the FreePBX. When asked for the password enter the one generated in step #3. You will get the "MariaDB [asteriskcdrdb]>" prompt if the connection is successful. Then exit the database.
5b) To verify the firewall is secured, repeat step #5a on a 3rd Linux system (not FreePBX or the Database server). When the 3rd system does the steps above the connection should be rejected as shown below. The error should say "Can't Connect" not "Access Denied". Can't Connect means the firewall on the database server is rejecting the connection.
Next export the FreePBX CDR database to a file. This file will be used to important the CDRs into the database. The fwconsole stop will stop the PBX from running, this way new CDRs will no longer be added. This step will take down calls and should be done during a maintance window. Verify the asteriskcdrdb_export.sql file was created in the local directory without issues.
Copy the CDR export to the new database server. This is done on the FreePBX server.
Then run the following commands to purge the FreePBX CDR database.
This will delete all CDRs and asteriskcdrdb_export.sql will be your only copy of the CDRs at this point.
Log into the database server, and import the database from the /root/asteriskcdrdb_export.sql file. This was copied to the database server in step #7. This will take a bit of time if the file is large.
Configure FreePBX webUI to read the data from the remote database. In the FreePBX webUI go to Settings→Advanced Settings. Enable read-only settings and Enable editing of read-only settings. Then enter the following info. Ensure to put the IP of the database server and change the password to the one generated in step #3 above. Once done submit the changes and apply the configuration in the webUI.
Next SSH into the FreePBX and modify the file /etc/odbc.ini . You will need to set the server option to the IP of the database, comment out the Socket line, and then add the user and password fields as shown below. Ensure to use the password you generated in Step #3. Save the file once these 3 fields are set correctly.
Now run the command below on the FreePBX to start the PBX services.
In the FreePBX webUI go into CDR Reports and verify you can see the previous CDR entries. Then make a new call that connects and lasts for 30 seconds. Once the call is over then verify the CDR reports shows the call.
Next run the commands below on the database server to verify the most recent call is being stored in the database. This command shows the most recent call which should be your test call.
You can now log into the FreePBX server and run the same commands to veirfy there was no CDR data stored on the FreePBX.
Review all of your PBX backup jobs and ensure the CDR Reports and Call Event Logging is removed. This will copy all data from the remote server during each backup. The remote database should now be backed up separately now that it is on its own server.
At this point the database has been moved and verified to be working. It is secured with a password and the firewall is locked to only the IP of the FreePBX system. You are ready now to start production traffic as the database has been moved successfully.
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 and Remote CDR Database
If you are using a warm spare you MUST remove the CDR Reports and Cell Event Logging as shown in step #16 above. As this could lead to data loss and/or duplicate data in the CDR/CEL databases.
Backing up CDR records
Now that the cdr database is no longer on the FreePBX system. You need to use standard database backup tools such as the ones below.
mysqldump
Xtrabackup
If you wish to use the FreePBX backup module and you do not have a warm spare, 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.