Mysql admin

Developed by Sweden's MySQL AB, MySQL 4.0 is a free, open-source database that offers real advantages—and real limitations—compared with the commercial packages in our roundup. Aiming for reliability and speed instead of bells and whistles, MySQL can do the job, provided you don't mind putting some work into daily administration and database development. The server garnered fine results in our performance test, trailing Oracle9i by only a small margin.

MySQL's lean-and-mean philosophy eschews a number of features that are standard fare in the other database packages we evaluated. It does not support stored procedures and triggers, nor does it offer built-in support for XML, OLAP, or constraints. But it includes innovations, like the ability to cache queries.

Transactions are not a part of MySQL's default (ISAM) tables, but they are included in two newer table types—Berkeley DB (BDB) and InnoDB—which were developed by third parties. Since we absolutely needed transactions for our testing, we chose the InnoDB table.

Getting up-and-running with InnoDB proved tricky and required digging into the online help to find the right settings for MySQL's manually edited, text-based configuration files. Fortunately, we found that the online help was more clearly written than a lot of open-source documentation we've seen. (If you are serious about solving problems fast, though, MySQL AB offers a variety of tech support options at varying costs.)

As with earlier versions, the beauty of MySQL is its fundamental simplicity. You administer the database using over a dozen command-line utilities, the most important of which is the mysql utility, an interactive shell for controlling and querying a database. In our testing, we found the utilities to be efficient in the best spirit of Linux, the platform on which MySQL was first developed. We had no trouble importing and exporting millions of records to get started with our online store database. Besides running SQL scripts in batch mode, the utilities let you "dump" the contents of a database in text format.

Instead of using a separate backup and restore utility, MySQL lets you back up by archiving the raw data files. This is simple, but it makes incremental backups almost impossible. With custom scripts, administrators can make reliable backups, but not everyone wants to tackle this.

Setting up users and access rights for different tables is time-consuming, because the program has no separate user management tool. By adding users and privileges to MySQL's system tables, you can restrict access to tables and even columns, but this requires adding records using SQL interactively or through scripts. This process demands a lot more work than the other database programs.

You're not entirely restricted to the command line access in MySQL: A variety of open-source tools can help with common administration chores. We tested two utilities included with the MySQL Max download. First, MySqlManager 1.0 is a graphical query tool on a par with Sybase ASE's. A second tool, WinMySQL admin 1.3, is a console for managing the details of MySQL's configuration. This console also lets you view and tweak performance settings. We liked its ability to record a snapshot of exact MySQL settings—a must for remote troubleshooting.

While other database products are moving toward putting more business logic into stored procedures (or even into Java business objects) that reside inside the database tier, MySQL avoids this approach entirely, preferring to stay light. Though it lacks many features enterprise administrators and database developers have come to expect, MySQL has an energetic and loyal following, not to mention a hard-earned reputation for reliability. Though it won't fit every need, this cheap and fast alternative can be a great choice if transaction processing isn't paramount to your project.

Copyright ?? 2004 Ziff Davis Media Inc. All Rights Reserved. Originally appearing in PC Magazine.