HOWTO: CDR with MySQL

Asterisk Asterisk Support
1

Hey all.

I just got my CDR working in MySQL. Below is a little writeup. In my case, I had Asterisk up and running, and only then I decided that I want to install MySql. If you install MySQL before you install Asterisk, things might be different.

I’m using Asterisk 1.4.19 and Asterisk-AddOns 1.4.6 on a Debian 4.0 (Etch) system.

Please let me know if you find any mistakes…

------------------------------------
 1  Install MySql
------------------------------------

Install the basics:
  apt-get install mysql-server mysql-client libmysqlclient-dev

If you want, install some more convenient tools:
  apt-get install mysql-admin mysql-navigator mysql-query-browser


------------------------------------
 2  Create CDR Database and Table
------------------------------------

enter the following SQL commands in mysql:

  CREATE DATABASE AsteriskCDR;
  USE AsteriskCDR;

  CREATE TABLE cdr (
       calldate datetime NOT NULL default '0000-00-00 00:00:00',
       clid varchar(80) NOT NULL default '',
       src varchar(80) NOT NULL default '',
       dst varchar(80) NOT NULL default '',
       dcontext varchar(80) NOT NULL default '',
       channel varchar(80) NOT NULL default '',
       dstchannel varchar(80) NOT NULL default '',
       lastapp varchar(80) NOT NULL default '',
       lastdata varchar(80) NOT NULL default '',
       duration int(11) NOT NULL default '0',
       billsec int(11) NOT NULL default '0',
       disposition varchar(45) NOT NULL default '',
       amaflags int(11) NOT NULL default '0',
       accountcode varchar(20) NOT NULL default '',
       userfield varchar(255) NOT NULL default '',
       uniqueid varchar(32) NOT NULL default ''
  );

  ALTER TABLE cdr ADD INDEX ( calldate );
  ALTER TABLE cdr ADD INDEX ( dst );
  ALTER TABLE cdr ADD INDEX ( uniqueid );

You can add more indices if you want.


------------------------------------
 3  Create DB User for CDR Database
------------------------------------

enter the following SQL commands in mysql:

  CREATE USER asterisk@localhost IDENTIFIED BY <password>;
  CREATE USER asterisk@<servername> IDENTIFIED BY <password>;

  GRANT ALL ON AsteriskCDR.* TO asterisk@localhost;
  GRANT ALL ON AsteriskCDR.* TO asterisk@<servername>;


test the database:

  $> mysql -p -u asterisk -h <servername> AsteriskCDR

  mysql> SELECT * FROM cdr;


------------------------------------
 4  Recompile asterisk-addons
------------------------------------

edit
  asterisk-addons-1.4.6/cdr/cdr_addon_mysql.c
and add
  #define MYSQL_LOGUNIQUEID


make clean
./configure
make menuselect   # check that cdr_addon_mysql is selected
make
make install


------------------------------------
 5  edit cdr.conf
------------------------------------

The minimum you need is:

[global]
enable=yes


------------------------------------
 6  edit cdr_mysql.conf
------------------------------------

[global]
hostname=<servername>
dbname=AsteriskCDR
table=cdr
user=asterisk
password=<password>
port=3306


------------------------------------
 7  Finish
------------------------------------

Reboot!  (this is so windows, but for some reason I had to, don't know why)

check 'cdr status' on the CLI, you should see
  CDR registered backend: mysql

make some calls and query the database.

Thanks to all the people who posted bits and pieces in the forums.

2

The naming convention of your database is a little weird to me. You are pigeon holing cdr into this database, now what if you wanted to do realtime storage are you going to create a database for each thing you want to have in realtime?

In other words I would have created a database for example named asterisk(note all lower case, makes it easier), and then created a table in there called cdr. Now you can have all asterisk related items in this database and not confuse yourself or others!

Thats just my 2 pennies and of course imho.

Thanks for taking the time to write that up!

3

Hello everyone,
I’m a newbie. I’ve tried many things but nothing’s displayed in mysql database.
I use redhat 5, asterisk 1.6.0.5, asterisk add-ons 1.6.0.

The error I can see after asterisk -vccccc command is:
[Mar 19 14:17:04] ERROR[4079]: cdr_addon_mysql.c:508 my_load_module: Unable to query table description!! Logging disabled.
cdr_addon_mysql.so => (MySQL CDR Backend)

Please help me, thank you!