I recently transitioned from Elastix 2.5 to FreePBX 13. I built a new box for FreePBX and configured everything from scratch. The only data that needs to be brought over is the call history - CDR Reports, missed calls, etc.
What is the recommended method? Export rows from tables (which?) in MySQL and append?
If you actually stored your data in a database, exporting it to a CSV and importing it using something like SQLYog is perfectly reasonable. This way, you can match up the columns and headers without having to worry (as much) about schema changes. Simply connect to the equivalent to asteriskcdrdb/cdr on your current system with the tool, export it to CSV, and connect to the new database and compare the field names column locations. When you export the table, be sure to include column headers.
If you stored your data in CSV files, you can import those into the asteriskcdrdb/cdr database/table using the same tool.
If you use CSV files for your intermediate step, you can also open them in Excel or OpenOffice and make updates/changes/additions/deletions that may be important to you. You can also “dummy up” fields that you might want to add in the new database.
It may be possible to bring the database over intact if you are using the same schema and database engine. I’m not sure that’s likely, so using CSV files as your intermediate step will probably give you the most bang for your figurative buck. For example, if you database is MySQL 5.5 on both the source and destination machines AND you are using MyISAM database files, you MIGHT be able to just copy them from the old server to the new server.
Under no circumstances should you try to use any kind of backup/restore utility. There are just too many ways your can hork your database and render the new installation broken.
Install phpmyadmin in the Elastix box and from there export the cdr table. Finally install phpmyadmin at the FreePBX box and import the file.
@jfinstrom and I have fundamental problems with people installing phpmyadmin on a FreePBX server. I don’t hink it’s a good idea, and he was quite vocal on his lack of support for it.
Allowing remote access to the Asteriskcdrdb database through a specific user from a specific IP address is by far more secure and less likely to cause problems down the line. You also don’t have to install any new software on the server if you use an external app to access your data.
I’m not saying “don’t do it” but I am saying that it is much easier to control access to your system is you don’t have a wide-open webservice running on your phone system. After all, we already have a web service running that can cause havoc with your phones and your database (FreePBX).
He can always remove it. He installs it, make the transition, uninstalls it.
You know that Excel has a direct MySQL connector, right? There is no need to put phpmyadmin on your machine. Unless, of course, you like random people hacking your phone system. I, personally, don’t.
Assuming that Elastix haven’t messed with the CDR table too much, this should be sufficient:
On the old machine: mysqldump --skip-opt -t -c asteriskcdrdb cdr > /tmp/cdr.sql
Copy the file /tmp/cdr.sql to the new machine, and then - assuming you’ve put it in /tmp on the new machine
mysql asteriskcdrdb < /tmp/cdr.sql
If you don’t use --skip-opt -t -c you’ll blow away the calls that have ALREADY been made on your system.
Successfully ran imports from both Elastix 2.5 and 4.0 using xrobau’s recommended method. There are 23 fields in both Elastix versions that match-up with what is present in FreePBX 13 (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield,did,recordingfile,cnum,cnam,outbound_cnum,outbound_cnam,dst_cnam) with only a difference in order.
For future reference, here are some tips:
elastix_admin_passwords --change
mysql -u root -pshow databases;\q
mysqldump -u root -p --skip-opt -t -c asteriskcdrdb cdr > /tmp/cdr.sql
mysql -u root -p asteriskcdrdb < /tmp/cdr.sql
Hope this helps. Thanks again, Rob!
Just to add, the password to the mysql asteriskcdrdb could be stored in /etc/freepbx.conf
I used the AMPDBUSER and AMPDBPASS listed there and the export proceeded with no problems.
Thanks!
Because of the possible vagaries in the various tables (cdr isn’t usually too bad, but if you use this as a template to get the rest of the data in the other databases), you should use a variation on this mysqldump command:
mysqldump -u root -p --complete-insert --insert-ignore --no-create-db --no-create-info -t -c asteriskcdrdb cdr > tmp.sql
This is particularly important if you are pulling data from an old database to a new one, since the old one’s schema may not exactly match your new database table definition.
This method allows you to pull the old data and insert it into the new database. Any field that doesn’t have data will be left with the “default” value for that field, and any removed fields will be ignored (since you are ignoring insert errors).