Open Source

Configure IT Quick: Manage MySQL data with phpMyAdmin

Get a look at how to install, configure, and use phpMyAdmin as a GUI to administer MySQL databases.


Dynamic Web sites are usually driven by a database in the background providing content—for the pages themselves or for isolated data in parts of the page. Some sites are entirely dynamic, while others use languages like Perl or PHP to interact with the database to make the site dynamic.

One of the most popular database systems is MySQL; it's fast, lightweight, comprehensive, open source, and free. A drawback to using MySQL is using the command-line client interface to manage data; it’s difficult for some and not very intuitive. However, when you're designing the database and writing PHP code to interact with it, sometimes you need to deal with the data directly. Your PHP code usually isn’t robust enough to edit, delete, and otherwise modify all of the data stored in the database.

You need a GUI tool like phpMyAdmin, one that doesn't require you to know much SQL syntax. While some GUI tools will let you manipulate data this way, they may only be able to handle local MySQL databases. And if they can connect to remote MySQL databases, you’re forced to open up another port in your firewall that shouldn’t be opened, since MySQL should really be completely firewalled off from the outside and only accessed locally via the Web server when dealing with a publicly accessible server.

The easiest way to get around this is to download and install phpMyAdmin, an administration interface to MySQL written entirely in PHP. It works just as well as any other MySQL administration client software—better, in fact. Here, I'll take a look at downloading, configuring, and using phpMyAdmin. Then I'll show you how to secure it to prevent unauthorized users from getting into and viewing or modifying your MySQL data.

Installing phpMyAdmin
The first step is to download phpMyAdmin. The latest version is 2.1.0, which was released in August of 2000. You can download one of two packages: One uses .php3 extensions and the other uses .php extensions. Choose whichever you feel is appropriate; users of the PHP version 3 can download either version, but users of the PHP version 4 should download the package with .php extensions. Here, I'll assume you’re using PHP4 and downloading the file phpMyAdmin_2.1.0php.tar.gz.

Save the file into your Web server's root directory, which will typically be /var/www or /home/httpd. Extract the file using the following:
tar xvzf phpMyAdmin_2.1.0php.tar.gz

This will create a subdirectory called phpMyAdmin/. Change to this directory. The next step is to edit the config.inc.php file, which contains the configuration information for phpMyAdmin. You’ll first see a section that details a server to connect to by defining the array $cfgServers[x], where x is the number of the server. For instance, to define a local MySQL server, you might use the following:
$cfgServers[1]['host'] = 'localhost';
$cfgServers[1]['port'] = '';
$cfgServers[1]['adv_auth'] = false;
$cfgServers[1]['stduser'] = 'root';
$cfgServers[1]['stdpass'] = '';
$cfgServers[1]['user'] = 'root';
$cfgServers[1]['password'] = 'secret';
$cfgServers[1]['only_db'] = '';
$cfgServers[1]['verbose'] = 'My Localhost';


Breaking it down
Let's take a quick look at what each part of the defined array means. The first directive defines the host to connect to, in this case localhost. You can also have phpMyAdmin connect to remote MySQL servers and manage them. If you define a remote server, you’ll need to use the fully qualified domain name or an IP address, and the remote site must have the MySQL port open to the public.

The second directive defines the port to use. If you have MySQL listening to a nonstandard port, define it here. Otherwise, leave it blank to use the default port 3306.

The third directive defines whether or not you’ll use advanced authentication, by defining false or true. Basic authentication means using the old-style MySQL authentication, storing the username and password in the configuration file. Advanced authentication allows you to log in as any valid MySQL user using HTTP-Auth. All you need to do to allow advanced authentication is to enable it and supply a valid stduser ($cfgServers[x]['stduser'], which we’ll look at in a moment) that is able to read the MySQL user/db table. This means that any user who has access to the MySQL database will be able to log in with his or her own username and password. Using advanced authentication is recommended for multiuser sites because it will ensure that users only have access to those databases to which they’re supposed to have access. Using basic authentication, the defined user in the configuration file (usually the root user) will be used regardless of who connects. In a single-user environment, or a multiuser environment where only one person will have access to phpMyAdmin, using basic authentication is fine if you secure it properly, a process we’ll also look at.

The fourth and fifth directives, stduser and stdpass, verify the real user/password pair with advanced authentication. This should be a user with sufficient access rights to read the user/db table, otherwise the user/password pair being requested cannot be validated. This pseudo-user is only used for the user/password lookup. If successful, the real user requesting access can then log into MySQL with the supplied user/password pair.

The sixth and seventh directives, user and password, are used to connect to the database when using basic authentication. Typically, this will be the root user and the root user's password.

The eighth directive defines a single database that will be accessed on the server; this is useful if you want to restrict all access to one single database. If you leave this string blank, the user will be able to select from any databases they have access to.

The final directive assigns a descriptive name to the server. If you leave this blank, the hostname will be used instead.

Further on in the file, you’ll see that you can define more than a single server. Beyond the server configuration entries in the file, you can define the default server. The $cfgServerDefault string determines the default server to log in to when you first connect to phpMyAdmin. If you have only one server defined, this must be set to 1. If you have more than one server, you can enter the number of the server that you want to connect to by default here, or you can enter 0 to have a list of the available servers presented so you can select one to log in to.

The rest of the configuration file deals with cosmetics. You can change the colors of phpMyAdmin, determine whether or not to use persistent MySQL connections, and even determine the base directory for the MySQL manual.

Save the file and exit. PhpMyAdmin is configured and ready to use. You just need to make Apache aware of it.

Securing phpMyAdmin
Before you make phpMyAdmin public, you need to secure it. You must be able to prevent other users from viewing your configuration file, which stores sensitive password information, and you must also prevent the outside world from wandering through your MySQL database.

You’ll need to set up an alias pointing to your phpMyAdmin directory. If you’re defining a VirtualHost for the default site, you might use something like this:
<VirtualHost 192.168.1.20>
DocumentRoot /var/www/html
ServerName www.mysite.com
Alias /phpadmin/ /var/www/phpMyAdmin/
<Directory /var/www/phpMyAdmin>
AllowOverride All
</Directory>
</VirtualHost>


Now, if you were to go to www.mysite.com/phpadmin/, you would see the initial phpMyAdmin index page. But you're not done yet. If you can see it, so can the rest of the world. Before restarting Apache to put your changes into effect, change to /var/www/phpMyAdmin and use your favorite editor to create a new file called .htaccess with the following contents:
AuthType Basic
AuthName "restricted directory"
AuthUserFile /var/www/.htpasswd
AuthUserFile /dev/null
require user joe


Change the username joe to one that’s appropriate for your site. Then, create the .htpasswd file, which contains joe's username and password for authentication. Put .htpasswd in /var/www and not /var/www/phpMyAdmin because /var/www is not publicly accessible via your Web browser. This means that no one can try to use as their URL www.mysite.com/phpadmin/.htpasswd to gain your password file.

To create .htpasswd, you must use the htpasswd program, like this:
htpasswd -C /var/www/.htpasswd joe

This will create a new .htpasswd file and prompt you for joe's password. If the file already exists and you want to add another user, simply omit the -C switch.

Now you can restart Apache and rest assured that your phpMyAdmin system is secure via remote entry. You must now secure it from local access. To do this properly, you need to know which user Apache runs as; typically it will be "nobody" or "apache". To find out for sure, issue the following on the command line:
ps aux|grep httpd

You should get a listing similar to this. Actually, you should get a page full of entries like this, one for each process containing the string httpd. The first column is all you're interested in, however. In this example, the Web server is running as the user "apache".

Change to /var/www and issue the following commands:
chown -R apache.apache phpMyAdmin
chmod 711 phpMyAdmin
cd phpMyAdmin
chmod 600 *


This gives ownership of the directory phpMyAdmin/ and all subdirectories and files to the user apache and the group apache. You then change the mode of the directory to 711, which gives read, write, and execute rights to the owner (apache) and execute rights to all other users; all users can cd to that directory. In the directory itself, change the mode of all files to 600, which means read and write permission for the owner and no permissions for anyone else. The apache user, or the Web server, and root will be the only users who can access the files. This will prevent local users from viewing files like your config.inc.php file and obtaining sensitive password information.

Using phpMyAdmin
Using phpMyAdmin is completely straightforward. When you go to your phpMyAdmin page and log in, you’ll obtain in the left-hand frame a listing of the databases available. By clicking on the database name, you’ll get an expanded view of the tables stored in the database.

Clicking on a table from the list will bring a menu to the right-hand frame that allows you to modify columns inside the table, add new columns, and so forth. By clicking on the Browse link next to any table, you can view all of the data stored inside the table. Here, you can edit entries, insert new entries, or delete entries. Everything is done with simple links and forms, so you don’t need to know much SQL syntax at all to edit and modify your databases like a professional.

Tip for PostgreSQL users
For those of you who use PostgreSQL, be not dismayed. PhpPgAdmin is basically phpMyAdmin ported to PostgreSQL. It’s available from SourceForge for download. Development of phpPgAdmin has been a little more extensive than that for phpMyAdmin. The latest version, 2.3.1, was released in October 2001, so there’s a more recent version with some newer features than what’s available in phpMyAdmin. The two packages work similarly; the primary differences are those that make MySQL different from PostgreSQL in the first place.

Conclusion
If you want a powerful administrative client to deal with your MySQL servers, look no further. PhpMyAdmin is one of the most powerful clients I’ve come across, and it's also the simplest to use. Seriously consider setting up phpMyAdmin. If you use MySQL for any Web content, or any other content for that matter, phpMyAdmin is the ideal tool to manage that information beyond the limitations in your PHP code.

With phpMyAdmin, there are no limits. You can edit every piece of data and every aspect of your databases. Most Web applications that use MySQL allow you to modify data in a very limited way; usually whatever the author has decided is safe. That could be fine in some cases, but if you want to do some serious editing of data in your pages, phpMyAdmin is an ideal way to do it.

When I first started working with PHP and MySQL, phpMyAdmin was one of the first programs I downloaded, and I use it all the time in my Web and PHP project development. It's become an indispensable part of my "development suite," and I couldn’t accomplish what I have, in the time I've done it, without the help of phpMyAdmin. I have it available on every Web server system I run because it's so useful.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks

Free Newsletters, In your Inbox