User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

After running successfully a medium-to-big cluster, one of your concerns is your database keeps growing. If you do not do anything, sooner than later there won't be a server that can host your FusionPBX database.

The good news about this is that it is very easy to know why the database grows so fast. It is logic, more customers, more calls, therefore, your database detailed record grows faster. In technical words, your v_xml_cdr table grows and grows.

Depending on the kind of customers you are hosting, CDR can be very valuable or depreciable. Depending on the country you are, by law, you may be required to store CDR record somewhere. Somewhere not in your production database. So, your solution is to archive them. If someone asks for them, you will deal with that later.

Archive your FusionPBX records

Use the following script to archive. Modify it to meet your needs.

$mysqli = new mysqli('localhost', 'USER', 'PASSWORD', 'fusionpbx');
if ($mysqli->connect_errno) {
    print 'Error: '.$mysqli->connect_error.PHP_EOL;
    exit (1);
$mysqli_archive = new mysqli('ARCHIVE_SERVER', USER', 'PASSWORD', 'fusionpbx');
if ($mysqli_archive->connect_errno) {
    print 'Error 2: '.$mysqli_archive->connect_error.PHP_EOL;
    exit (1);
$sql = 'SELECT * FROM v_xml_cdr WHERE answer_stamp < DATE_SUB(NOW(), INTERVAL 30 DAY) limit 300';
if ($result = $mysqli->query($sql)){
    while ($row = $result->fetch_assoc()){
        $cols = implode(',', array_keys($row));
            foreach (array_values($row) as $value){
                isset($vals) ? $vals .= ',' : $vals = '';
                $vals .= '\''.$mysqli_archive->real_escape_string($value).'\'';
        $sql_archive = 'INSERT INTO v_xml_cdr ('.$cols.') VALUES ('.$vals.')';
        // echo $sql_archive;
        $result_archive = $mysqli_archive->query($sql_archive);
        unset($cols, $vals);
        if ($result_archive === TRUE){
            $uuid = $row['uuid'];
            $sql_delete = 'DELETE FROM v_xml_cdr WHERE uuid="'.$uuid.'"';
            $result_delete = $mysqli->query($sql_delete);
            if ($result_delete === TRUE){
                echo $uuid .' moved'.PHP_EOL;

Good luck!

blog comments powered by Disqus