MySQL Administrator September 2004
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.
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
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 email@example.com;
In this statement, the user russell is being given all security privileges on all databases and tables (i.e., *.*) from the host address of 18.104.22.168 (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 firstname.lastname@example.org;
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
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.
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.
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 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
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.
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
Questions or problems regarding this web site should be directed to email@example.com.
Copyright © 2008 Art Beckman. All rights reserved.
Last Modified: March 9, 2008