INTRO
This is a bit of a noob tutorial for those who want to read in bulk data into a FreePBX machine that they may have created on another FreePBX installation. Basically if you want to have full control over just the bits you want to restore.
BACKGROUND
I have a warm spare backup box in case something goes wrong with my main machine. I keep it on an alternative cloud server. When I restore the backup from the main machine all sorts of things fall over because it seems to overwrite passwords and change IP addresses.
I gave up tryiing to figure out what was wrong (I am sure there is a solution but I couldnt find it and I am not that experienced nor a professional). I decided to just get most things in the manual way so as to not overwrite everyting.
Bulk handler does this well, but it is lacking ringgroups, pagegroups, trunk details and most importantly for me, the IVR which took me days to setup.
So my main aim here was to find out a way to just import the IVR setup manally from one machine to another and in doing so, found out I could get the ringgroups and pagegroups in as well so happy dayz!
Here we go.
A. SIMPLE WRITOUTS AND READ BACK IN
- Bring in extensions and Inbound Routes (DID’s) via export/import using bulk handler.
- Bring in your asterisk phone book (if you use this) via its own export and import capabilities.
- Bring in your user manager users and groups via bulk handler but it wont work unless you make up a pwd because it doesnt write this out (so best to handle another way if this is important)
Then edit each user and go into contact manager tab and add them to the User Manager group so that they appear in contact manager - I use contact manager to forward calls to some cell phones. There doesnt appear to be a way to add data into contact manager directly from the GUI.
B. GETTING YOUR OTHER DATA
- Do a default backup of your main machine (machine1) via the Freepbx GUI. Then goto restore on the GUI and download the tgz file to your harddrive.
- In that archive you will find a mysql-2.sql file. This has all the information you need to recreate a lot of things on the new machine. It is easy to read.
- Create a new sql file with just the information you want to port into the new system. You will want the “DROP TABLE IF EXISTS” groups of data and the “INSERT INTO” entries.
In my case all I wanted was:
The IVR: Tables are ivr_details, ivr_entries, recordings
The Outbound Routes: outbound_route_patterns, outbound_route_sequence, outbound_route_trunks, outbound_routes
The Ring and Page Group Tables: ringgroups, paging_config, paging_groups
Trunks: trunks
Note inserting Trunks doesnt bring in the connection data. PJSIP trunks even worse…no pwd and some parameters are missing. I havent explored where this is stored. I brought in the trunk names then had to go into each one to recreate the data. It was helpful but not ideal. I didnt have many trunks so not a big deal.
You can save all these as separate sql files or in one file depending on how “granular” you like to do things.
C. IMPORTING YOUR SQL DATA
Here is where I needed to understand how to read in bulk sql data into FREEPBX. I am sure there are many ways to do this but because I am a webmin fan and already have this installed on my system I found this the easiest way for me.
For those who do not know or use webmin - it is a web based control panel to manage all sorts of stuff to do with Linux machines. There are many (the most popular seems to be cPanel) but I like webmin because it does what I need and is free.
Freepbx has gotten to a point where it provides this sort of control for most things now but I still use it to manage my FTP, Cron jobs, postfix mail and provide an overview of ram, storage space, cpu usage, etc and I found it has MySQL database server functionality too which is what I used to get the data back into Freepbx quite simply.
If you want to install webmin - most linux machines will just install it the usual command line way and the webmin page provides guidance on this. However because I am using the Freepbx distro “SNG7-FPBX-64bit-1703-1” it doesnt recognise what actual flavour of Linux I am using. So you need to install it manually so it can prompt you to tell it.
Here is the best article on how to do this: http://doxfer.webmin.com/Webmin/Installing_Webmin and scroll down to the heading “Installing the tar.gz package”. Say yes to most defaults and I chose the Centos 7.3 operating system and it installed fine.
- Now go into webmin https://yourmachineIP:10000/ (if 10000 is the port you wanted it on), and goto Servers/MySQL database server on the left menu system.
- Double click the asterisk database folder and there you can search for tables already in there and view the data. In our case we want to hit the “Execute SQL” button, then the “Run SQL from File” tab.
- Choose “From uploaded file” and pick one of your prepared sql files and voila it should just read in the data for you.
GOING FULL MANUAL - You can probably see in webmin that you could get your SQL data from here too instead of using Freepbx restore (from Item B above). You can even hunt for the specific tables you want and just write those out. You can also export in csv format and read that back in also using the “import text file” tab in webmin if you prefer that format. - If you have IVR recordings then you now want to copy the actual recording files over too. I just did this via transfer of files directlyl from one machine to the other using fish protocol under net connection in Krusader. The location for these files for me were in:
/var/lib/asterisk/sounds/en/custom
CONCLUSION
So far everything is working fine. Let me know if you foresee any issues or happy to hear about some further tips 
Once you have been through this exercise - you can probably figure out when doing your intial Config backup in FREEPBX that you can exclude all sorts of tables. If you exclude everything except the tables you want, then you probably could just say within freepbx’s backup and restore functionality. This will be my next adventure. Or maybe the backup section needs an “include” where you just pick what you want and exclude all else and it will be the automated version of all this above.
Anyway, hope this helps or at least furthers your understanding of FreePBX.
Cheers
Chris