How Does One Migrate the CDR Database?

Hello! I am running an older FreePBX (v12.0.19, Asterisk v13.0.2) in a virtual machine and have setup a new VM with the current FreePBX (v14.0.1.1, Asterisk v13.17.0).

I’d greatly prefer to migrate the old CDR database onto the new setup, right before I go live with it (and so do by way of copying the database files, instead of exporting/importing, etc).

I’m wondering if there is something like:
copy C:\FreePBX.12\data\CDR.dbf C:\FreePBX.14\data*.*
that’ll do this?

(I’ll translate to SCP as necessary ; )

Cheers & thanks for any leads!

All the files are in

/var/lib/mysql/asteriskcdrdb

first do on old annew

mysql -u user -ppassword asteriskcdrdb -e ‘describe cdr;describe cel’ >oldstructure|newstructure
put the two fiiles in the sae directory and then

diff oldstructure newstructure

to make sure they have the same structure. If the are the same you can copy the directory but stop mysql before doing the copy, but what is wrong with mysqldump asteriskcdrdb > save then copy and mysqlimport -u user -ppassword save ?

1 Like

Slowly…

I would look at incremental dumps depending on size

Nah - fire and forget. Start it on Friday and let it run all weekend - or all week. It’s not “anything-intensive” enough worry about it getting in the way.

Another hint - create the database structures first (as part of the install) and use the options to NOT delete and recreate the database tables on the mysqldump.

If you have installed

mysql-utilities

you will have a whole sh*t load of useful utilities all with man support,

mysql(tab key)

look into mysqldbexport/mysqlimport and the “type” qualifier, you will have all the tools you could use with mysql and probably mariadb (untested as yet)

Performing the indicated dbf struct comparision leads to a brick
wall - a summary of the impediments is as follows:

– CDR changes –

  • fields uniqueid and did become MUL keys
  • fields *_cnum, *_cnam expand from 40 chr to 80
  • 3 new fields added
    – CEL changes –
  • field context becomes MUL key
  • fields src, dst, channel, dstchannel disappeared
  • field extra/512 replaced with eventextra/256 and userfield/256

The outlook wasn’t brilliant for the Mudville Nine that day;
the score stood four to two, with but one inning more to play.
And then when Cooney died at first, and Barrows did the same,
a sickly silence fell upon the patrons of the game.

A straggling few got up to go in deep despair. The rest
clung to that hope which springs eternal in the human breast;
they thought, if only Casey could get but a whack at that –
they’d put up even money, now, with Casey at the bat.

Sort of surprising to me that given the maturity of both PBX
tech and *, that such underpinnings as CDR & the like
haven’t solidified in their layouts. #FirstWorldProblems

But Flynn preceded Casey, as did also Jimmy Blake,
and the former was a lulu and the latter was a cake,
so upon that stricken multitude grim melancholy sat,
for there seemed but little chance of Casey’s getting to the bat.

But Flynn let drive a single, to the wonderment of all,
and Blake, the much despised, tore the cover off the ball;
and when the dust had lifted, and the men saw what had occurred,
there was Jimmy safe at second and Flynn a-hugging third.

Then from five thousand throats and more there rose a lusty yell;
it rumbled through the valley, it rattled in the dell;
it knocked upon the mountain and recoiled upon the flat,
for Casey, mighty Casey, was advancing to the bat.

Ok, so I’ll look into the idea of importing the data - most of
the fields are the same and if it goes to stink I can just
zero out CDR & cel.

There was ease in Casey’s manner as he stepped into his place;
there was pride in Casey’s bearing and a smile on Casey’s face.
And when, responding to the cheers, he lightly doffed his hat,
no stranger in the crowd could doubt 'twas Casey at the bat.

Ten thousand eyes were on him as he rubbed his hands with dirt;
five thousand tongues applauded when he wiped them on his shirt.
Then while the writhing pitcher ground the ball into his hip,
defiance gleamed in Casey’s eye, a sneer curled Casey’s lip.

And now the leather-covered sphere came hurtling through the air,
and Casey stood a-watching it in haughty grandeur there.
Close by the sturdy batsman the ball unheeded sped–
“That ain’t my style,” said Casey. “Strike one,” the umpire said.

Yeah, my first appempts at exporting & importing didn’t go well. Seems
that I need to specify the table into which I wish to import the data,
not just the whole database.

From the benches, black with people, there went up a muffled roar,
like the beating of the storm-waves on a stern and distant shore.
“Kill him! Kill the umpire!” shouted someone on the stand;
and it’s likely they’d have killed him had not Casey raised his hand.

With a smile of Christian charity great Casey’s visage shone;
he stilled the rising tumult; he bade the game go on;
he signaled to the pitcher, and once more the spheroid flew;
but Casey still ignored it, and the umpire said: “Strike two.”

I start to go looking for logfiles for what mysql are up to but
enjoy no fortune…

“Fraud!” cried the maddened thousands, and echo answered fraud;
but one scornful look from Casey and the audience was awed.
They saw his face grow stern and cold, they saw his muscles strain,
and they knew that Casey wouldn’t let that ball go by again.

The sneer is gone from Casey’s lip, his teeth are clenched in hate;
he pounds with cruel violence his bat upon the plate.
And now the pitcher holds the ball, and now he lets it go,
and now the air is shattered by the force of Casey’s blow.

I forge onward, and do the import with results like:

  • 75 records imported
  • 1979 warnings
    (for a home-PBX system that’s been running for 5 years -
    far too few legit records imported.)

Oh, somewhere in this favored land the sun is shining bright;
the band is playing somewhere, and somewhere hearts are light,
and somewhere men are laughing, and somewhere children shout;
but there is no joy in Mudville — mighty Casey has struck out.

Again, so very surprising given that I would have predicted that
many peeps would want to import their old CDR into a newly-setup
system. Unless they’re all doing incremental, in-place upgrading.

Anyway, that’s my journey - better luck to others who might follow.

And thanks dicko for the leads - it at least took me into 'discovering’
mysql, right in my own backyard.

When doing the mysqldump to import, are there any issues with the mismatched structures? Importing doesn’t do much good if half of the data is lost or never displayed anyway.