MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility.
This tutorial will explains how to install MySQL, create a sample database, create a table, insert records into the table, and select records from the table.
Full article here:
An Introduction To MySQL Database (Unixmen)
I have not tried or tested this, but just wanted to point it out as it might prove useful to some readers. I still prefer MondoRescue for a full system backup, but I can see how this might also come in handy in certain situations:
How to do Painless MySQL Server Backups with AutoMySQLBackup (Linux.com)
It appears there is a nasty bug in certain versions of PHP (almost certainly in version 5.3.3, and perhaps other versions as well) that can cause SOME inbound and outbound routes in FreePBX to be ignored. Add this to the list of difficulties to plague some FreePBX users, but for once it’s not something the FreePBX developers did.
The symptom appears to be that you have an inbound route with a DID that starts with the number “2”, or an Asterisk Phonebook entry that starts with a “2”, but it doesn’t work as expected or gets changed to a negative number. Not all patterns starting with “2” appear to be affected, but ten digit numbers in certain area codes (such as 248 here in Michigan) definitely are. In the case of inbound routes, it appears that the numbers get stored in the MySQL database correctly, but when they get written out to extensions_additional.conf (the dialplan file that F—PBX generates for Asterisk to use) they get transformed into negative numbers. Therefore, when a call comes in on that DID, Asterisk doesn’t recognize it and the call gets dropped (or if you accept calls for any DID then it’s handled as such a call, but a warning message is posted to the CLI and the log file).
For a discussion of this issue, see these threads in the PBX in a Flash forum:
Inbound DID routing (only catchall works)
(240) area code in Asterisk Phonebook
Just wanted to alert you to this in case you happen to have the buggy PHP version and things just aren’t working as they should be. The best fix is probably to upgrade PHP to a later version but that can cause other issues, or you may encounter unmet dependencies when trying to upgrade (in fact, if you come across a foolproof way to upgrade PHP on Centos, please let us know). If you are using PBX in a Flash, they’re aware of the issue and one would hope they’ll have a fix real soon now. If you are using any other FreePBX-based distro then you will have to bug the distributors of that distro for a fix. If you rolled your own, then good luck to you in upgrading PHP!
This article was originally published in August, 2011 and may contain outdated information.
I just spent an interesting couple of hours trying to install phpMyAdmin on an Asterisk server running CentOS 5.5. As I encountered each problem and solved it, I had to wade through a lot of pages that weren’t applicable to my installation, etc. Since many readers of this blog run similar configurations I thought I’d just list the hiccups I encountered, and what I had to do to solve them. Note that some distributions come with phpMyAdmin already installed, so make sure you don’t already have it before you try to install it!
NOTE: Think carefully about whether you really want to follow the instructions below, particularly if it requires adding a repository. If you do that, make sure you only install the software you actually need from that repository, then disable it (set enabled=0). If you don’t do that, you could easily get into a situation where some of your curent software (such as PHP) simply will not upgrade no matter what you do. And if you are running a PBX “install and go” distribution, they may specifically warn you not to add repositories, or it will break your installation, so don’t do it!
If you do anything suggested below, you do it at your own risk!
• yum install phpmyadmin doesn’t work — try using the dag repository — there are several pages on the Web that tell how to do this. Use Google to search for “how to enable the dag repository” (without the quotes) if you need help. The basic idea is you need to create a file called /etc/yum.repos.d/dag.repo (with the proper permissions, ownership, etc.) and put something like this inside:
[dag]
name=Dag RPM Repository for Red Hat Enterprise Linux
baseurl=http://apt.sw.be/redhat/el$releasever/en/$basearch/dag
gpgcheck=1
enabled=1
BUT you also need to install a GPG key, and getting THAT can be a bit of a problem. Some instructions will tell you to do this:
rpm –import http://dag.wieers.com/rpm/packages/RPM-GPG-KEY.dag.txt
That link no longer works, and you have to do this instead:
rpm –import http://apt.sw.be/RPM-GPG-KEY.dag.txt
But for some people even THAT doesn’t work, in which case it’s suggested you use wget to obtain the file, then import it:
wget http://apt.sw.be/RPM-GPG-KEY.dag.txt
rpm –import RPM-GPG-KEY.dag.txt
I’m being a bit non-specific because the instructions could change, and I’d prefer you find a current reference on how to enable this repository. Also, some may prefer to install RPMforge, which is a collaboration of Dag and other packagers. Regardless of the effort involved, I do suggest you install phpMyAdmin using yum, because it will install everything in the correct locations for CentOS, and you don’t have to compile it or anything like that.
Note that when you do install phpMyAdmin using yum, it may also install required dependencies such as libmcrypt and php-mcrypt (another advantage to using yum).
• You don’t have permission to access /phpmyadmin/ on this server.
Go to /etc/httpd/conf.d/phpmyadmin.conf
Under the line:
Allow from 127.0.0.1
You could add a line to allow access from your local network, for example:
Allow from 192.168.0.0/255.255.255.0
(But use values appropriate to your network).
If you are accessing the box remotely, then add a line allowing access from your IP address. Be VERY careful, because you don’t want to let the entire world into your databases!
• Existing configuration file (./config.inc.php) is not readable.
If you’re doing this on a system running FreePBX, scroll down to where I discuss changing the ownership of all phpMyAdmin-related files and directories to be the same as the MySQL user. Otherwise, the easiest solution (though not necessarily the most secure) is to change the permissions of the file /usr/share/phpmyadmin/config.inc.php from the default of 640 to 644 (add user read permission). If no one can get to your system from outside your local network, this probably isn’t an issue, but if anyone has a better idea on this, feel free to leave a comment.
• “Error
The configuration file now needs a secret passphrase (blowfish_secret).”
Open /usr/share/phpmyadmin/config.inc.php and find this section:
* This is needed for cookie based authentication to encrypt password in
* cookie
*/
$cfg[‘blowfish_secret’] = ‘oh my this is such a wonderful passphrase‘; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
Insert any phrase you like (within reason) between the second pair of single quotes in the last line shown above (but don’t use ‘oh my this is such a wonderful passphrase‘, I just inserted that as an example. Be creative!). Don’t worry, this isn’t something you’ll actually have to type in every time you want to use phpMyAdmin.
• #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
You don’t login as root, you use your MySQL username and password. In FreePBX-based systems these can be found in /etc/amportal.conf, in the AMPDBUSER and AMPDBPASS settings. BUT… if you enter a wrong user name before logging in correctly, it may have already set a cookie with that username and password and then you won’t be able to get in even if you DO use the correct username and password. The solution is to clear all browser cookies for the address of your server, then try again — and make sure you get it right this time! 😉
I will note here that you can avoid some of these cookie-related issues, probably including those mentioned above, by going into /usr/share/phpmyadmin/config.inc.php and finding this section:
/* Authentication type */
$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;
If your system is behind a hardware firewall or is otherwise VERY secure, you could change the auth_type from ‘cookie’ to something else, such as ‘http’. This will save you a lot of frustration during the login process, but at the possible expense of making your database less secure. For those concerned about security, a document on the phpMyAdmin wiki advises you to “See the page on Security or the multi–user sub–section of the FAQ for additional information, especially FAQ 4.4.” I personally found their security documentation rather useless, because they make a lot of suggestions but provide no specific examples of how to implement those suggestions. Anyway, I personally feel that as long as a system is behind a good firewall that doesn’t permit anyone on the “outside” to access phpMyAdmin, ‘http’ is a good compromise between a security model that might drive you crazy (‘cookie’) and one of the other models that’s fairly insecure, such as ‘config’ (which some consider insecure because it stores your server username and password in plain text). However, if your system is otherwise VERY secure and you just don’t want to have to enter a password to use phpMyAdmin, then it is possible to change the ‘auth_type’ to ‘config’ and (in the same config file), look for these lines:
/*
* End of servers configuration
*/
And just above those lines, insert these lines:
$cfg[‘Servers’][$i][‘user’] = ‘mysqluser’;
$cfg[‘Servers’][$i][‘password’] = ‘mysqlpassword’;
Change mysqluser and mysqlpassword to the correct vales for your system (on a FreePBX-based system, these are the values in /etc/amportal.conf mentioned above). I do not recommend using ‘config’ because it is less secure (be sure to read the page on Security mentioned above), but it’s up to you to decide how secure you want your system to be.
(I’m fully aware that any objections to storing the user and password values in plain text in the phpMyAdmin config.inc.php fall a bit flat when you realize the same values are stored in plain text in amportal.conf, but I also feel as though the fewer places those values are exposed, the better. Why give potential attackers one more place to find this information?)
• phpMyAdmin – Error
Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly.
Check your /var/log/httpd/error_log – in my case, the first error message of each set contained a phrase like “open(/var/lib/php/session/sess_somerandomstring, O_RDWR) failed: Permission denied (13)” and I figured that the problem was another permissions issue.
On some sites I have found a suggestion that you change the ownership of all phpMyAdmin-related files and directories to be the same as the MySQL user (in the case of an Asterisk/FreePBX system, that would be asterisk:asterisk). On a FreePBX-based system, you could try this (check to make sure these are the correct paths before doing this):
chown asterisk:asterisk /usr/share/phpmyadmin -R
chown asterisk:asterisk /var/lib/php/session -R
If that doesn’t resolve the issue (or you’re doing this on a system that’s not running FreePBX), perhaps the easiest solution (though not necessarily the most secure) is to change the permissions of the offending file. If you have the same issue I had, try changing the permissions of the directory /var/lib/php/session from the default of 770 to 777 (add full user permissions).
Strangely, this one didn’t show up until after I’d successfully run phpMyAdmin a few times. Go figure. Also, after fixing this, I had to delete cookies again (as mentioned in the previous item) before I could log in, but that was when I still had the ‘auth_type’ set to ‘cookie’ (another reason I decided to change that to ‘http’).
Found and solved any other “gotchas” while installing phpMyAdmin under CentOS? Think I could have solved a problem in a better way? Feel free to share your solutions in the comments.
EDIT: There is one other thing that can happen after you install or update PHP on your system (as might happen if you let a FreePBX-based distribution do an upgrade). You may start seeing PHP warning messages such as:
PHP Warning: PHP Startup: mcrypt: Unable to initialize module
Module compiled with module API=20050922, debug=0, thread-safety=0
PHP compiled with module API=20060613, debug=0, thread-safety=0
These options need to match
in Unknown on line 0
If that happens try updating the dependencies that came with phpMyAdmin, for example:
yum update libmcrypt
yum update php-mcrypt
It was the second of those two that vanquished the PHP warning messages for me.
And why did I NEED to install phpMyAdmin, you ask? Well, because someone (ahem) made a slight configuration error and caused an endless loop, that within the space of about ten seconds or so, generated over a THOUSAND bogus records in the ‘asteriskcdr’ (Call Detail) database. The only easy way to I knew of at the time to clean them out was phpMyAdmin (since I don’t “speak” MySQL), but I don’t recommend you attempt something like that unless you know what you’re doing, because one wrong move and you could delete your entire FreePBX database (trust me, that would be a VERY bad thing!). In retrospect I probably could have used Webmin, since it also has the ability to access the MySQL database, but I didn’t think of that at the time.
If you use a recent version of FreePBX, you are familiar with the new and tedious method of entering Outbound Route Dial Patterns and Trunk Dialed Number Manipulation Rules. Fortunately, version 2.9 and above offer a way to import a list of patterns from a CSV file (there’s a way to patch FreePBX 2.8 to get this functionality as well — see Ticket #4691).
What they don’t give you is a way to export a list of patterns once you have them installed. So if you want to clone a route and you’ve lost your original CSV file (or never had one to begin with because FreePBX converted your existing routes and trunks when you upgraded from version 2.7), what do you do? Fear not, because it actually is possible, if not exactly the most straightforward process.
EDIT: Now there is an easy way around all this — see this thread on the FreePBX Swiss Army Knife Module. If you use that module, you don’t need to read the rest of this article (although, you might be interested in the part about editing CSV files). Unfortunately, it is reported that the module does not work with FreePBX 2.10 or above, and the author has said he will not fix it to work with newer versions. It is possible that the ability to export outbound route and trunk data to a CSV file may be present in the newest version(s) of FreePBX.
EDIT: Steps 1 and 2 involve using a Database editor module to export the data to a CSV file. Unfortunately, one commenter says that this module no longer works with FreePBX 2.9. There are other ways to accomplish the same thing — see the edit at the end of this article to use Webmin or phpMyAdmin instead of the Database editor module. If you export the data using one of those other programs, then skip to step 3 below.
Step 1: Go to the FreePBX bug tracker and look for Ticket #4793 — Database editor module (like phpMyAdmin for FreePBX). On that page you’ll find a download link for dbeditor-1.0.tgz which (at the time of this writing) is the only version of this software available. Download and install it as you would any third-party module (download it to your computer, then in FreePBX’s GUI navigate to Module Admin and then click the “Upload Module” link, then upload the module and follow the directions to complete the install).
Step 2: Once you have the Database Editor installed, it will appear under the FreePBX “Tools” tab, in the “System Administration” section. Click on the “Database Editor” link, and you should see a list of database tables used by FreePBX. The two you are interested in are called outbound_route_patterns and trunk_dialpatterns. Near each pattern name you will see two links for “Export” and “Drop” — do not click either of those (especially be careful not to click drop!), because the “Export” here will export the table in MySQL format, which is not what you want. Instead, click on the name of the table (that is, click on either outbound_route_patterns or trunk_dialpatterns) and a new page will open. Near the top of the page you will see a place where it says, “Export to CSV: pipe – tab – comma – semicolon” — click on comma and it should bring up a file save dialog that will let you save the file to your system.
Step 3. Load the downloaded file into any text editor that can deal with Linux/Unix-style line endings and not change them (so, don’t use Notepad!), or better yet, use a CSV editor if you have one (an excellent free one is CSVed, which runs under Windows but will also install and run under CodeWeavers’ CrossOver on a Mac, which tends to make me think it would probably also run under WINE on a Linux or Mac OS X computer). I do NOT recommend opening the file in a spreadsheet application such as Excel, because if you have any patterns that start with one or more leading zeroes, those might be removed, and it’s also possible that any non-numeric characters may be misinterpreted or removed.
The first number in each line is associated with a particular outbound route or trunk, so, you want to cut out the lines not applicable to the route or trunk you want to keep. Don’t erase the top (header) line. If you have many routes or trunks, it may be a bit tricky to figure out which is which, since the numbers don’t tell you the name of the route or trunk they are associated with.
After you do that, you also have to get rid if the first column in each line. So let’s say you are using route 3, and each line starts with 3,. What you want to do is a search and replace on <newline>3, (or expressed as a regular expression: n3,) and replace it with a newline only (n as a regular expression). In a CSV editor you may be able to just delete the first column. For a trunk, the principle is the same except that you will need to remove the first AND last columns, leaving only the middle three.
While you’re at it, it’s also possible to use search and replace in other ways. For example, if you are duplicating list of outbound route patterns but need to change the extension field pattern in all lines, you could do that using search and replace, if you understand what you’re doing.
Step 4. Once you have edited out all the lines except the ones pertaining to the route or trunk you want, you need to change the header line at the top. It’s important to get this right. For an outbound route you want to change it from this:
route_id,match_pattern_prefix,match_pattern_pass,match_cid,prepend_digits
(Note that the route_id may be missing after the previous edit) to this:
prefix,match pattern,callerid,prepend
Visually inspect the lines following the header to make sure you have four fields separated by exactly three commas.
For a trunk, you will need to change the first header line from this:
trunkid,match_pattern_prefix,match_pattern_pass,prepend_digits,seq
(Note that the trunkid and seq may be missing after the previous edit) to this:
prefix,match pattern,prepend
Visually inspect the lines following the header to make sure you have three fields separated by exactly two commas. Also, and this applies to trunks only, if it is important that trunk dial patterns be in a particular order then you will want to check to make sure they are in the correct order in the CSV file, since the “seq” column is not preserved. In many situations this is not an issue but in certain special cases the order of trunk dial patterns can make a difference in how they are processed.
Step 5: Save the modified file to a file with the .csv extension (if using a CSV editor make sure you are saving in comma-delimited format). Again, try to make sure your editor doesn’t change the line endings – I don’t know for certain that it would make a difference, but it might.
Step 6: Now you can create a new route or trunk, and in the “Dial patterns wizards” or “Dial Rules Wizards” dropdown select “Upload from CSV” and select your file to upload. Note that if you are using a beta version of FreePBX 2.9, it may complain if you try to submit an outbound route with no patterns, even if you are uploading a CSV file. In that case, just put a single “X” in the “match pattern” field. After you submit changes, be sure to scroll through the patterns to make sure they appear to be correct. In particular, make sure that all values are in the correct fields.
There are probably other ways to accomplish this, and maybe eventually the FreePBX developers will add an export function on the route and trunk pages (obviously, it would probably not be a good idea for me to request it, and besides, it appears that someone already has).
EDIT: Here is a way to export the data using Webmin or phpMyAdmin (replacing steps 1 and 2 above). Use Webmin if you have it, because it produces cleaner output:
If using Webmin, from the main Webmin page, go to “Servers”, then “MySQL Database Server.” Under “MySQL Databases”, click on “asterisk”, and it should take you to the “Edit Database” page (be very careful from here on out because if you do the wrong thing you could really mess up your system). In the “Edit table” dropdown, select either outbound_route_patterns or trunk_dialpatterns, depending on which you want to work with. That should take you to an “Edit table” page, but at the bottom of that page you should see a button labeled “Export as CSV.” Click on that button, and it will take you to a “CSV export options” page. You want to select the following:
Then click the “Export Now” button. If you exported to a browser window, use Ctrl-A to select all the lines, and Ctrl-C to copy them (⌘A and ⌘C on a Mac), then proceed with Step 3 above (except use Ctrl-V or ⌘V to paste the lines into the text editor). Or, if you prefer to use a CSV editor, then in your browser simply go to File | Save Page As… and save the entire page as a plain text file with a .csv extension to your local machine, and then proceed with Step 3 above.
If using phpMyAdmin, from the main page select “asterisk” in the left-hand column, then (still in the left-hand column) either outbound_route_patterns or trunk_dialpatterns, depending on which you want to work with. Then click the “Export” tab in the main window, and then under “View dump (schema) of table”, in the “Export” section select CSV. The view should change to show an “Options” section, and there you want to change “Fields terminated by:” from a semicolon to a comma, and then check the “Put fields names in the first row” checkbox. Then click the “Go” button and it should display the data in a format that can be copied and pasted into a text editor. Sorry, that’s the best I can advise you with regard to phpMyAdmin, since I seldom use that program (and I may have an older version, so things might have changed slightly).
GiottoPress by Enrique Chavez
Recent Comments