Manage MySQL remotely with phpMyAdmin

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 config.inc.php, 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.