MySQL Administration Made Easy

MySQL is great for building database-driven Web sites of all shapes and sizes. It's fast, easy to configure, and incredibly reliable. But MySQL lacks a mature, easy-to-use GUI administration tool. Yes, you can use the mysql command-line, but that's rather tedious and you don't get a good overall picture of your server without doing a lot of typing.

MySQL is great for building database-driven Web sites of all shapes and sizes. It’s fast, easy to configure, and incredibly reliable. But MySQL lacks a mature, easy-to-use GUI administration tool. Yes, you can use the mysql command-line, but that’s rather tedious and you don’t get a good overall picture of your server without doing a lot of typing.

If you’ve been using MySQL for a while, you’ve probably heard about GUI tools that make administration easier. MySQL AB, the creators of MySQL, have two GUI tools: MyCC (http://www.mysql.com/downloads/gui-mycc.html) and MySQL GUI (http://www.mysql.com/downloads/gui-mysqlgui.html).

But there are problems with both of these tools. MyCC and MySQL GUI are traditional “fat client” applications that need to be installed on every computer you use. In some environments, this can be a real hassle. Consider the case of a Web hosting company that offers MySQL and PHP to its customers. Each user would need to download, configure, and learn to use the software. Also, neither tool is very mature or well known. MyCC is simply too new and MySQL GUI hasn’t received a lot of attention from its developers recently.

A better way to administer a MySQL setup would be over the Web. That way, support software would only need to be installed once, and every user could access it from a Web browser. This type of tool is available and it’s called phpMyAdmin (http://www.phpwizard.net/projects/phpMyAdmin/).

A Killer App

What makes phpMyAdmin a killer application for anyone using MySQL? Several things:


  • It’s pure PHP code, and therefore very portable.

  • It runs on the server, so you only need a browser to use it.

  • It’s easy to configure and use.

  • It’s free.

  • It’s internationalized (over 30 languages supported).

  • It’s centralized. A single install can be used by many diffe ent users to control different databases and/or servers.

  • It’s mature. Users around the world are submitting code to fix bugs and introduce new features.








pma-main
Figure Two: phpMyAdmin’s main page








pma-db1
Figure One: Logging in to phpMyAdmin

A Quick Tour

Let’s take a look at phpMyAdmin in action. When you first connect to phpMyAdmin, you’ll have to provide a valid MySQL username and password, just as you’d use when connecting to MySQL with any other tool (see Figure One).

Once logged in, you’ll see the welcome page (or home page) for the phpMyAdmin installation (Figure Two). The home page always lists the version information for MySQL as well as your username and the hostname of the MySQL server. In this example, we see the string MySQL 3.23.49-log running on localhost as root@localhost, which indicates that MySQL and Apache are running on the same computer.








pma-login
Figure Three: Database view of a PostNuke database

The left side of the home page lets you switch between databases on the server. Once a database is selected, phpMyAdmin displays the database view. Figure Three shows the database view for a PostNuke database (it’s actually the one we created in last month’s column). In this example, the list of tables is shown in the left frame; the right frame contains a list of all the tables in the database along with links to perform various actions on those tables (browse, select, insert, etc.).

If you scroll down past the table names, you’ll see a variety of other operations that you can perform on this database and its tables: run ad-hoc queries, dump table structures, create new tables, and so on.

You can drill down even deeper by clicking on a table name, such as nuke_autolinks, in the left frame, which displays the structure of the table (Figure Four). Or, click the “Browse” link next to the table’s name in the right frame to examine the data in that table.








pma-db-stats
Figure Four: Viewing a table’s structure

There are many other actions you can perform on the data or table structures. You can easily modify data, add/drop/modify columns, change indexes, and so on. With phpMyAdmin at your fingertips, there’s a good chance you’ll rarely need to touch command-line tools.

Two of the most useful links on the home page are “Database statistics,” which displays a summary of all the databases on your server (Figure Five), and “Users,” which leads to the account administration functions (Figure Six,. The latter is a useful interface for adding and removing users as well as adjusting individual account privileges.








pma-users
Figure Six: Account administration with phpMyAdmin








pma-tbl-struct
Figure Five: A summary of database statistics

Finally, if you have sufficient privileges, you can also access and modify server-specific information using other links on the main page. These links will run certain queries, such as SHOW STATUS, SHOW VARIABLES, and SHOW PROCESSLIST, and display the results in a readable format.

Installation

For all the power that phpMyAdmin provides, installation is remarkably simple. We’ll assume you already have a version of Apache that handles PHP (see “Scripting the Web with PHP” in the July 2001 issue for more on building and configuring PHP to work with Apache; the article is also available online at http://www.linux-mag.com/2001-07/php_01.html). Download the latest version, extract the files, and place them in a top-level directory on your Web server underneath the document root called phpmyadmin:


# tar -zxvf phpMyAdmin-2.2.5-php.tar.gz
# cp -r phpMyAdmin-2.2.5 /www/phpmyadmin

Next, you’ll need to make a few adjustments to the configuration file config.inc.php (it’s in the phpmyadmin directory) that controls how phpMyAdmin works (make sure that Apache can read this file, but not the rest of the world). The file contains a number of PHP variables that you can adjust to customize phpMyAdmin’s features and behavior. To get started, let’s make the following adjustments:


$cfgServers[1]['controluser'] = ‘pma’;
$cfgServers[1]['controlpass'] = ‘Pa55word’;
$cfgServers[1]['auth_type'] = ‘cookie’;

The first two variables tell phpMyAdmin what initial username and password to use. Once you actually login to phpMyAdmin, it will use the username and password you supply in the Web form. The auth_type controls the type of authentication phpMyAdmin will use. The recommended method is to use cookie-based authentication. Others are described in the phpMyAdmin documentation.

The last thing to do is setup the pma MySQL account with the minimal privileges necessary to authenticate incoming users. Connect to your MySQL server with root access and issue the commands in Figure Seven.




Figure Seven: Configuring MySQL for phpMyAdmin


GRANT USAGE ON mysql.* TO ‘pma’@'localhost’ IDENTIFIED BY ‘Pa55word’;
GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv,
Process_priv, File_priv, Grant_priv, References_priv, Index_priv,
Alter_priv) ON mysql.user TO ‘pma’@'localhost’;
GRANT SELECT ON mysql.db TO ‘pma’@'localhost’;
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
ON mysql.tables_priv TO ‘pma’@'localhost’;

If your Web server is on a different host than your database server, change localhost to the hostname or IP address of your Web server. From MySQL’s perspective, all phpMyAdmin connections originate from the host on which Apache and phpMyAdmin are running.

That’s all it takes to get started. Point your browser at the phpmyadmin directory (via the URL http://localhost/phpmyadmin/), login, and try it out.

Access & Security

If you’re providing access to MySQL via phpMyAdmin over the Internet, there are several security measures to consider. First, make sure that Apache won’t allow access to your phpMyAdmin directory unless the request is secured (via SSL). Otherwise, all traffic between the Web browser and Web server, including database usernames, passwords, and other information, is traveling in the clear. Even an inexperienced Bad Guy can use a packet sniffer to get that data.

If your Apache server isn’t configured with SSL, you can consult the Apache mod_ssl Web site (http://www.modssl.org) for instructions. Alternatively, if you have another instance of Apache (or another Web server) that has SSL enabled and is listening on the HTTPS port, you can redirect normal HTTP requests to that port. Create a .htaccess file in the phpmyadmin directory with the following contents, changing localhost to the correct hostname.


<IfModule mod_ssl.c>
SSLRequireSSL
</IfModule>
<IfModule !mod_ssl.c>
Redirect /phpmyadmin

https://localhost/phpmyadmin

</IfModule>

You might want to create this file anyway, as it will provide greater flexibility. If Apache has been configured with SSL, then it will make sure the request is being made across an SSL connection. If it isn’t, then the request will be redirected to the secure HTTPS connection.

It’s also a good idea to only allow “known” hosts to connect to phpMyAdmin. This means explicitly listing the hostnames, domain names, or IP addresses of hosts in your Apache configuration (or firewall if you’re lucky enough to have one). By opening phpMyAdmin to the world, you’ve provided an easy way for someone to indirectly run a brute force attack against your database server in an attempt to guess usernames and passwords.

What’s Missing?

The only feature that isn’t in the current release of phpMyAdmin is replication management. Since most sites aren’t using MySQL replication, this isn’t a significant omission. But being able to see the replication status on a slave or binary logging information on a master is helpful if you have one or more MySQL servers replicating from a master.

As you start working with phpMyAdmin, you’ll find that it’s often faster and easier than typing out commands on the command line. Soon you’ll be wondering how you ever got along without it.



Jeremy Zawodny uses Open Source tools in Yahoo! Finance by day and is is writing a MySQL book for O’Reilly & Associates by night. He can be reached at Jeremy@Zawodny.com.

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62