How to remove old CDRs

Ive got a couple of PBXs that have millions upon millions of lines of CDRs going back 5-10 years and its getting impossible to run backups or restores. Is there an easy way to export and delete old CDRs, say anything older than a year?

I read the article in the wiki about remote cdr databases but there isn’t much info there about how to remove old CDR lines, just how to start recording everything on the new remote server. Or will it move the entire CDR database to the remote location once I set that up? Not sure how that would effect QXact reporting.

Or is there an easy way to just backup a certain date range of CDR? I would consider saving a CDR backup for each year somewhere remote.

Thanks.

https://wiki.freepbx.org/display/FPG/Remote+CDR

I did this on my own phone server to test and it did not work as expected lol

Database changed
MariaDB [asteriskcdrdb]> DELETE from cdr WHERE ‘2018-06-21 09:58:00’ < calldate < ‘2018-06-21 10:00:00’;
Query OK, 95902 rows affected, 1 warning (4.50 sec)

Havent tried it, but I saved this from a previous post for future use.

That’s where my syntax came from. Apparently date 1 < calldate < date 2 does not work as it deleted everything in my CDR.

Sorry, I didnt look that closely at your syntax. I just remebered I had one saved for future use. Now I am interested to see if anyone has other suggestions on how to skin the cat.

I’d love to export chunks at a time so I can reimport them if need be, like every month or every year, before deleting them from the PBX.

Use mysqldump with similar condition before
the delete, you will get a text file of the soon to be deleted calks that you could import back if necessary

1 Like

Working from memory:

DELETE FROM cdr WHERE calldate BETWEEN ‘2018-06-21 09:58:00’ AND ‘2018-06-21 10:00:00’;

1 Like

Did you end up using this cron job on a live system successfully?

I thought we settled for just deleting everything older than a couple months but I just looked and it was never implemented. Will do some testing this weekend.

DELETE FROM cdr WHERE calldate BETWEEN ‘2018-06-21 09:58:00’ AND ‘2018-06-21 10:00:00’;

does work but when I copied and pasted it didn’t like the ’ (needs to be a single quote). Its still not clear what optimizing the database does, and I still have no idea how to export everything to a csv from the command prompt. I went looking for a log that would show me what happens when I click Search in the CDR module in the GUI but cant find anything.

When you work with all this stuff, you need to understand how the shell you are using (probably bash) works with quotes and double quotes. without that knowledge you will remain confused , but luckily we have google to alleviate your angst.

1 Like

I just deleted about 4 million lines from the asteriskcdrdb database but it didn’t do much of anything to reduce disk usage. It looks like I need to rebuild the tables as the empty cells are still taking up space. Will the optimize command do that? And how safe is that to do on a database this large?

image

No, removing the records when you are using innodb never reduces the size of the the underlying table, there are google remedies out there for that , but logistically 4 million cdr records are not going to be impactful, my guess is it your cel table that is the pig

So running the optimize command didn’t actually change the size of the cdr much. I can’t find documentation on the cel table in the freepbx wiki. Any links would be appreciated.

*edit: it looks like you just replace the column from calldate to eventtime, but it does seem to take forever. I’ll have to mess with this some more.

image

Adding an index would probably help if there isn’t one, from, mysql :-

USE asteriskcdrdb;
SHOW INDEXES FROM cel;

if not there then

ALTER TABLE `cel` ADD INDEX `eventtime` (`eventtime`);

Think of the CDR and the CEL tables as the “Header” and the “Details” tables.

The CDR has basic information about the call, and the CEL has all of the details; how long it rang, when it was picked up, when the bridge was created, etc.

Needless to say, there will be a LOT more data in the CEL table… to get rid of the records, you will need to delete from both tables.

One way would be using two delete commands;

DELETE FROM CDR Where calldate < DATE_SUB(now(), INTERVAL 6 MONTH);
DELETE FROM CEL WHERE eventtime < DATE_SUB(now(), INTERVAL 6 MONTH);

However, a more “proper” way to do it would be with a delete join…

DELETE cdr, cel FROM cdr RIGHT JOIN cel ON cdr.uniqueid = cel.uniqueid WHERE calldate < DATE_SUB(now(), INTERVAL 6 MONTH);

*** DISCLAIMER *** OK… someone double-check that… use at your own risk, always make backups, and cover your children’s eyes before messing with your database. That being said, that should delete the records older than 6 months from the cdr, along with the associated records for each call in cel.

3 Likes

This might need to be in a different thread but how do I move the CEL and CDR database and table to a different directory on the same virtual machine? I tried creating a new directory (/media/CELtable as a test) and gave it the same ownership and properties that /var/lib/MySQL/asteriskcdrdb has (0700 and owner MySQL). Then I inserted that path into the remote CEL DB Table field in Advanced Settings. I left the DB name blank and left it enabled. Did a fwconsole restart and don’t see anything happening.

Should probably be a different thread… but here you go.

Shut down mysql
Move all of the files in your current data directory to the new location
Locate my.ini file, and edit it to change the datadir parameter to point to the new location
start mysql

1 Like