Moving CDR records from old Elastix system to FreePBX after a migration

I’m testing the migration script from

My old system is running Elastix 2.5, which is running on top of FreePBX 2.11.10 r17.

I installed the new system using FreePBX-SNG7-FPBX-64bit-1707-1 on a bootable USB key. I had purchased the System Builder Starter Bundle and the Endpoint manager before installing and activating the new system.

I’m working on figuring out how to copy the CDR (call records) from the Elastix system to the FreePBX system. I don’t know mysql very well, so I’m learning as I go.

I think I’ve figured it out. Can anyone see any problems with the below approach?

On Elastix system at Linux prompt:

mysqldump -p asteriskcdrdb cdr > elastix.sql (I needed to use a password here, hence the -p option)

Copy elastix.sql to another PC with mysql installed.
Create a new database, and run/import elastix.sql into that database. This recreates the cdr table from Elastix. I used the HeidiSQL graphical interface on a windows PC.

On FreePBX system at the linux prompt:

mysqldump asteriskcdrdb cdr > freepbx.sql (I didn’t need to use a password here)

Compare the table definitions from elastix.sql and freepbx.sql; the FreePBX table had a few new columns.

In HeidiSQL on the Windows PC run the following SQL commands to add the extra columns to the table:

alter table cdr
add did varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
after userfield;
alter table cdr
add linkedid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’;
alter table cdr
add peeraccount varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’;
alter table cdr
add sequence int(11) NOT NULL DEFAULT ‘0’;

Sill in HeidiSQL on the windows PC, right click on the cdr table and choose “export as sql” to newfreepbx.sql.

Edit newfreepbx.sql to comment out any “drop table” “drop database” “create database” and “create table” SQL commands at the beginning of the file. I did this because I wanted to keep using the same database and table that FreePBX originally created.

copy newfreepbx.sql to the FreePBX system. At the linux prompt, in the same directory you copied newfreepbx.sql to:

use asteriskcdrdb
source newfreepbx.sql

And this imports the cdr records! I tried a couple of reports from the FreePBX reports/cdr reports and they look ok. Note that reports are limited to 100 results by default – I kept wondering why I was only getting 100 call records until I noticed this setting on the right of the report query screen.

If you can see anything in the above that will cause me problems when I do a live migration, please let me know. Thanks!

Mysqldump has these options built in - you don’t have to edit the file to get this to work.

I know that I can tweak mysqldump now – I’m very much still learning this stuff as I go.