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.
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.
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?
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
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.
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.