Home
Interests
Photos
Favorites

MySQL Administrator September 2004
by Russell J.T. Dyer

For many MySQL developers, database administration is something that they know little about and, as a result, they avoid. To make MySQL administrative tasks easier, MySQL AB has developed a new graphical user interface to the MySQL server. It's called simply and appropriately, "MySQL Administrator". This application was first released to the public earlier this year, but as it had a few bugs that kept causing it to crash on my systems, I've waited to review it. Recently, though, MySQL AB has released a version that is fairly stable; there are still some things to be worked out, but it's now very usable and dependable.

MySQL Administrator allows an adminstrator to configure a MySQL server easily. You can manage users, as well as server connections and threads. You can check performance and server logs related to general database activities, slow queries, and errors. A couple of sections provide assistance in making backups of databases and restoring them if necessary. If you take advantage of replication, MySQL Administrator provides an interface for monitoring their status. Finally, you can examine table schema and make changes to the layout of tables. You can't perform queries, though. For a GUI application to run queries, use MySQL Control Center. The MySQL Administrator application focuses just on database administration and analysis. And it does a good job of it, too.

Installation

A copy of MySQL Administrator can be downloaded for free from one of MySQL AB's mirror sites. There are Linux- and Microsoft Windows-compatible versions available. However, MySQL Administrator only works on version 4.0 or higher of MySQL, so to use it you may need to upgrade. Since there's so much more that you can do with the newer versions of MySQL, I recommend that you look into it. Just do a little testing of your queries and API scripts before finalizing an upgrade.

My server uses Red Hat Linux, so I downloaded the glibc, full version. It's about 13.8 MB in size and it comes tarred and gzipped. After downloading the file, I moved it to my /usr/local/ directory and uncompressed it:

tar xvzf /usr/local/mysql-administrator-1.0.12-linux.tar.gz


Figure 1: Dialog box

You'll have to change the name of the file depending on which one you download, of course. Next, I created a launcher on my Gnome desktop so I could easily start the executable, mysql-administrator, located in the directory /usr/local/mysql-administrator/bin/. After that, I clicked on the icon on my desktop and started the program and was faced with a dialog box like the one in Figure 1.

As you can see, to start you must enter a MySQL user name, the user's password, and the host name or address of the MySQL server. On my server, I also had to specify the file path and name of the socket file. Because I'm running MySQL Administrator from my server, I was able to use the host of localhost. To run it remotely, though, not only would you need to use the server's address (or domain name), but the user would need to have the privilege of accessing MySQL from a remote address added to the table user in the mysql database. This can be done by entering the following from the mysql client as root:

GRANT ALL ON *.* TO russell@12.127.17.72;

In this statement, the user russell is being given all security privileges on all databases and tables (i.e., *.*) from the host address of 12.127.17.72 (that's not really my address, incidentally). If desired, you could limit the user to a specific database by replacing the first asterisk with the database's name. But for my purposes, this user needs to be able to administer all of the databases in MySQL. Since the MySQL Administrator can delete tables, you might want to revoke such privileges by entering something like this:

REVOKE DROP ON *.* FROM russell@12.127.17.72;

After the first SQL statement that granted all privileges to the user russell, this SQL statement takes the privilege of deleting tables away from the user.

Server Settings


Figure 2: Icons

After you clear the opening dialog box and connect to a MySQL server, the main window of the application will appear. In the left margin will be icons (see Figure 2) representing different administrative sections available. The Server Information choice provides a summary of the server's status and some static information about it (e.g., the MySQL version and the IP address), as well as data on the client that's running MySQL Administrator. The Server Control section shows the startup log for the server in the right-hand panel. It also comes with a button for stopping the MySQL service. That works pretty well, but it doesn't provide a method by which you can restart the service. You have to resort to the plain, old shell interface to do that.

The third choice in the left margin, Startup Variables, is pretty useful. It's particularly valuable if you don't know how to configure MySQL for logs or how to configure MySQL for optimal performance. The Startup Variables section presents a set of tabs from which to choose in the right-hand panel (see Figure 3) of the application. The first tab, General Parameters, provides a form for changing the location of the directory in which MySQL data files are kept. You can also change the maximum allowable amount of memory that's used for queries and the like, which can sometimes speed up queries. More optimization settings for memory are available under the Performance tab. Changes made in this section will affect the /etc/my.cnf file on the server. You'll have to restart the MySQL daemon for the new settings to take effect, though. Here's where a restart button would be handy.

Logs, Users, and Server Status


Figure 3: Tabs

The Log Files section (Figure 4 ) provides a form for specifying the path and names of log files. If you don't already have logging activated, MySQL Administrator will do it for you by making the necessary additions or changes to the my.cnf file, under the [mysqld] section. One minor irritation (if you're an overly tidy person) is that the application adds comments above each entry when it first writes them to the configuration file. The wording is awkward and the comments are unnecessary. However, if you delete the comment lines using a text editor like vi, MySQL Administrator won't put the comments back if you make more changes with it.

To view logs, MySQL Administrator has another section called Server Logs. It doesn't quite catch on to any changes made in the Startup Variables section, even with a good swift restart of both the daemon and the application. I had to use the browser button equivalent (i.e., Select File) to point to the right log file. If you set up a special directory to handle logs, be sure to give the user mysql permission to write to it with the chown and chmod commands. Otherwise, MySQL won't be able to generate the log files or update them.


Figure 4: Log files

One potentially cool thing is that MySQL Administrator offers the ability to graphically review and modify user privileges. I received several error messages when I clicked on the User Administration tab, though (something about not being able to retrieve table schema from the server). After clicking OK about 10 times to acknowledge each error message, I eventually was given clear access to the user administration panel. I can endure that until the next release. The problem that made this feature unusable has to do with specifying the host for which a user's privileges are related. According to the help screens (which are fairly well written in parts, and minimal in others), a user is listed in a menu-tree in the left-hand margin, with a list of the hosts associated with the user under each user name. However, there didn't seem to be a way to fine-tune the user's privileges globally (for the wildcard host %). When I added new users or made changes to a host-limited possibility (e.g., based on a specific IP address), the application would either generate an error or crash when I applied changes. This may sound bad, but, because this is the only section that I couldn't use, I think MySQL Administrator is useful as it stands. The developers are still working on it diligently and should have the last of these problems resolved shortly.

The Server Connections section is useful for eyeing a list of users and threads that might be tying up a database. You can use it to see what users are doing at a given moment. You can then disconnect a user or just kill a particular thread. Simply highlight the thread that you need to clobber and click a button at the bottom right labeled Kill Thread. You can do the same for a user from the User Connections tab. If you terminate a user's connection, however, the user's client will be able to re-establish the connection when it attempts to run another SQL statement.

If you're into monitoring your server's activities and usage graphically, you'll enjoy the Health section. It provides a continuous line graph of usage and traffic flow on one panel. Another panel graphically shows how memory is being drained by queries. This section contains two more tabs or panels providing the values of server variables, as well as a long list of server status indicators. These can be very handy if you're administering a large database and you want to fine-tune its settings. You can't change any settings from this panel, though. For that, you must go back to the Startup Variables or one of the other sections.

Backups


Figure 5: Backups

If you want to make backups of your databases, the Backup section can help. By clicking on the New Project button on the Backup screen, you can create a new set of backup instructions. In the left margin of the right-hand panel (see Figure 5) you will see a list of databases on the server. Click on a database that you want to back up and then on the right arrowhead in the middle to add a database to the list to be backed up in the right-hand margin. Once you've chosen all of the databases to back up, you can click on the Save Project button to keep the backup list. To manually run the backup of the databases selected, click on the Start Backup button. A dialog box will open in which you can name the dump file (it uses mysqldump) that will be created and choose the directory in which to save it. The Schedule Backup tab presents a panel that allows you to schedule a backup to be run automatically and regularly by cron. Since the application is using mysqldump, just about all of the mysqldump options are available and can be set on the screen shown under the Advanced Options tab.

To restore a backup, go to the Restore Backup section. Initially, you probably won't see any backup files to be restored. Click the Change Path button located in the bottom left corner to tell MySQL Administrator where to look for dump files. There aren't many options to this feature that's the nature of a dump file; you just run it. Any options that you might want will need to be set before making the backup. MySQL Administrator doesn't give you the option of restoring data to a different directory or to a temporary table so that data isn't overwritten. For that, you must first edit the dump file using a text editor.

If you are using replication, MySQL Administrator provides the Replication Status section. It doesn't provide much information (its help page is blank), but it's a start. I suspect more features and information will be added to future releases.

Tables


Figure 6: Tables

The last section of MySQL Administrator is called Catalogs. This section is for examining and modifying the schema of tables within databases. After selecting a database in the left-hand margin (and ignoring some error messages), the right-hand panel will display a list of the tables belonging to the database (see Figure 6). This list under the Tables tab will give a variety of information on the tables: their types, the number of rows, row sizes, etc. Clicking on the tab labeled Indices will list the indices for all of the tables in the database.

While under the Tables tab of the Catalogs section, if you double-click on a table name (see Figure 7), a window will open in which you can change the structure of a table. You can change a column's data type, as well as add and remove columns. You can also meddle with an index or change a table type (e.g., from MyISAM to InnoDb) if you're so inclined. You can even delete or add whole tables from the interface. One thing you can't do, as I mentioned before, is run a query on data. That's not an administrative function.


Figure 7: Table editor

Overall Opinion

If MySQL is to compete with a general market that, for example, Microsoft Access supports, then MySQL will need good graphical user interfaces like MySQL Control Center and MySQL Administrator. Certainly there are some bugs to be worked out with MySQL Administrator, but despite the problems I experienced, more than 90% of it is stable. If it weren't for the different contortions on my particular server, it might have been totally error free. Of course, to compete with Microsoft, all problems will have to be eliminated regardless of the user's situation.

Overall, MySQL Administrator is ready for use and its developers have done a wonderful job, especially when one considers that they're still at the early stages of version 1. I recommend that anyone who prefers a graphical interface for administering their databases start using MySQL Administrator and report any bugs to MySQL AB.

Russell Dyer is a Perl programmer, a MySQL developer, and a Web designer living and working on a consulting basis in New Orleans. He can be reached at russell@dyerhouse.com.

Copyright 2003 UnixReview.com
 

Questions or problems regarding this web site should be directed to abeckman@outdoorssite.com.

Copyright 2008 Art Beckman. All rights reserved.

Last Modified: March 9, 2008