This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new!

Feature: System Administration

Manage MySQL remotely with phpMyAdmin

By Federico Kereki on March 25, 2008 (8:00:00 AM)

Share    Print    Comments   

Odds are, if you design Web sites with a database back end, you've worked with MySQL. You can manage this database from the command line, but it's not very user-friendly. Using the graphical tool phpMyAdmin helps, but not all Web hosting providers offer it or allow you to install it on the server. Fortunately, you can install it on your own box and manage several MySQL databases remotely at the same time, without having to install anything anywhere else.

phpMyAdmin is open source software tool, written (obviously) in PHP. It allows you to perform all kinds of MySQL administrative tasks over the Web by means of an easy graphical interface. If you need to do something that isn't provided by the interface, phpMyAdmin allows you to fall back to direct MySQL commands, so you're always able to perform any task, no matter how complex. The software is available for most distributions in more than 50 languages, and is licensed under the GPL.

Installation and setup

You must have Apache installed before you set up phpMyAdmin. For this article, I'll assume that Apache HTML files reside on /srv/www/htdocs (another common possibility is /var/www/html) and that Apache runs as user webmin belonging to group www. The configuration details for the HTML files are in the default-server.conf file, and the details for the user and group are in uid.conf.

If your distribution repositories include phpMyAdmin, you can install it by using your favorite package manager; in my case, since I prefer Smart, I just ran smart install phpMyAdmin. If your repositories don't provide the latest version, go to the phpMyAdmin downloads page and pick the version that suits you. (For international users, I recommend the all-languages.tar.gz file; if English is enough for you, try english.tar.gz.) After downloading the file, working as root, type in the following command:

tar zxf thePackageYouDownloaded.tar.gz -C /srv/www/htdocs && chown wwwrun.www -R /srv/www/htdocs/phpMyAdmin*

To change phpMyAdmin configuration, you must edit the file, which doesn't happen to be very friendly. You can browse the online documentation for it, but you just need to change a few lines. Open the file and locate a group of lines all starting with $cfg['Servers'][$i]; edit the following lines (note: they might not be together or in the order shown here) to suit your configuration:

$i++; $cfg['Servers'][$i]['host'] = 'THE HOSTNAME OR IP ADDRESS OF THE MYSQL HOST'; $cfg['Servers'][$i]['port'] = ''; // MySQL port - leave blank for default port $cfg['Servers'][$i]['socket'] = ''; // Path to the socket - leave blank for default socket $cfg['Servers'][$i]['connect_type'] = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket') $cfg['Servers'][$i]['extension'] = 'mysql'; // The php MySQL extension to use ('mysql' or 'mysqli') $cfg['Servers'][$i]['compress'] = FALSE; // Use compressed protocol for the MySQL connection (requires PHP >= 4.3.0) $cfg['Servers'][$i]['auth_type'] = 'config'; $cfg['Servers'][$i]['user'] = 'THE MYSQL USER NAME'; $cfg['Servers'][$i]['password'] = 'THE MYSQL USER PASSWORD';

For each server you want to manage remotely, copy all the lines above, including $i++:, and paste them into the configuration file: you will end up having a similar group of lines for each server you will manage remotely. Note that whichever MySQL user you specify will need remote access rights to work with the corresponding database; these rights must be granted (for each database) by an administrator, but they should already be set.

phpMyAdmin allows for three authentication methods ('auth_type' in the configuration file). The "config" authentication method is the simplest -- it lets you connect to a server without entering anything; the user and password will be in the configuration file itself. (The other two methods, "cookie" and "http," force you to enter a username and password each time you want to connect to a database.) This may not be the safest, most secure, method, but since you will be accessing the remote server from your own machine, this isn't such a problem -- presumably, the server should already be secured. Still, you might want to protect your phpMyAdmin installation by using an appropriate .htaccess file.

Using phpMyAdmin

Once you set up phpMyAdmin, whenever you log in, you'll be able to pick the server you want to work with from a dropdown list. All the normal features of phpMyAdmin will be available, and the only difference you might note (compared to using phpMyAdmin with a local database) is a slight delay because of transmission times. You can perform all sorts of administrative tasks remotely, including database creation or modification, backups, and queries.

There's plenty of documentation available for using phpMyAdmin online -- just Google a bit - or get a copy of Mastering phpMyAdmin 2.8 for Effective MySQL Management from Packt Publishing.

Manipulating remote MySQL databases with phpMyAdmin is simple, and you don't even have to install any software on other servers. phpMyAdmin provides an easy graphical interface that's appropriate for most administrative tasks.

Federico Kereki is an Uruguayan systems engineer with more than 20 years' experience developing systems, doing consulting work, and teaching at universities.

Share    Print    Comments   


on Manage MySQL remotely with phpMyAdmin

Note: Comments are owned by the poster. We are not responsible for their content.

Manage MySQL remotely with phpMyAdmin

Posted by: Anonymous [ip:] on March 26, 2008 12:09 AM
I think this assumes that a user would exist for the IP that you're trying to connect from. I don't imagine most webhosts allow connections from anywhere but the local machine?


Re: Manage MySQL remotely with phpMyAdmin

Posted by: Anonymous [ip:] on March 28, 2008 09:21 PM
I think I have to agree with you on this one. I think any webhost worth what you pay them would rather you install phpMyAdmin on your hosted site vs adding to their sql setup, why? Simply you install phpMyAdmin, no work for them they didn't install it they don't have to maintain it.


Manage MySQL remotely with phpMyAdmin

Posted by: Anonymous [ip:] on March 26, 2008 02:16 PM
I often see scans for phpMyAdmin in my web logs. It seems to be a common avenue of exploitation. I would strongly recommend that anyone who installs it should change the default configuration rather drastically.


Manage MySQL remotely with phpMyAdmin

Posted by: on March 28, 2008 07:44 AM
I agree with Anon from above. I have also lots of scans for directories like "phpMyAdmin", "PMA", "phpMyA" etc. So the first thing to do after installation should be to rename the directory to something like "hd673gdth" and put an .htaccess with AuthRequired in there.


This story has been archived. Comments can no longer be posted.

Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya