MySQL Database Management Software

This article will take a look at some of the MySQL Database Management software available. It will concentrate on two excellent ones that that are freely available, phpMyAdmin, and MySQL Administrator from MySQL AB, the company that owns and develops MySQL.

Let's have a look at phpMyAdmin first. It can be downloaded here. Simply unzip the files in your web directory and fill out a few lines in phpMyAdmin's configuration file to connect to your MySQL server and you're on your way.

This program is written in PHP, probably the language most commonly used in conjunction with MySQL . It states on phpMyAdmin website, “Currently it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges, export data into various formats and is available in 54 languages.”

This is a fairly impressive list, but in fact it can do more than that. phpMyadmin is web based, so it can be displayed in any browser. Here is a screen shot of phpMyAdmin showing the structure of a table named balance_5.

From this view it is possible to alter the structure of any row in the table. You can add or drop a primary key or index, set a column to auto-increment, drop an individual column, or add a column.

By clicking on the Browse button you can see and edit each data entry in the table. You can drop or empty a table, export a table via a dumpfile in several formats by clicking on export.

Below are some of the options available in the “Operations” section. As you can see you can see you can rename tables here, copy them with or without data, and run maintenance and optimization routines.

If you want to insert data using phpMyAdmin that is easy as well. Below is the view after clicking on the “Insert” tab. You probably will not want to insert huge amounts of data in this manner, but it is convenient for adding a few rows for testing purposes.

For basic MySQL administrative tasks, phpMyAdmin is more than adequate. It is easy to install, easy to use, and provides a handy browser interface for a long list of tasks that otherwise would have to be performed from the command line.

Let's take a look at MySQL Administrator, probably the easiest MySQL Database Management software system. MySQL Administrator provides a GUI interface for many of the administrative tasks that phpMyadmin lacks, providing more access to server configuration and monitoring. It is free for download from here. It comes bundled with the MySQL Workbench and Query Browser, two more useful tools worth investigating. Here is the interface, showing the server information view:

This view provides some general information about server parameters and the operating system and hardware.

The view below starts to get a little more into the nitty gritty of some server configuration. From this view shown you can change the port that MySQL is connecting on, the location of the data directory, and just at the bottom of the shot there is an option to change the size of key buffers in the MySQL configuration file.

This is useful in setting the buffer sizes to correspond with your hardware. You can also look at log files and set parameters for the different storage engines.

The User administration view provides a graphical interface for establishing new users, and has a tab for setting the privileges the user will have. It also allows you to set a limit for the amount of system resources a user can consume.

The Server Connections view shows a list of all the current connections to the server, and what user is connected. The Server Logs view allows a convenient way to view logs and manage log rotation and scheduling.

The Backup and Restore Backup views provide an interface to schedule backups and also to restore databases in the event of a problem. In the Backup view there is Schedule Backup tab for scheduling backups as cron jobs. The Advanced tab allows you to set some parameters for how locking will be done during the backup procedure.

In the database column below is a list of all the databases available to the user. To choose a database to back up, simply click on it.

As you can see, each table in the database may be displayed and tables can be selected individually if you do not need all the tables backed up. Each backup configuration you create must have a unique name and is saved as a project.

You can also import projects saved from another computer, or export a project to another computer over a network or to a removable device. Backups can be started manually from the “Start Backup” link at the bottom, or you can click on the “Schedule Backup” tab at the top to schedule a backup using crontab.

Below is a view of the Advanced Options section. As you can see, the program is able to handle backups of both MyISAM and InnoDB tables. When backing up MyISAM files, the “Lock All Tables” option is probably the best choice. It locks all the tables of the database simultaneously. This ensures that the tables are consistent with one another.

The “Normal Backup” option locks each table individually.

This method is likely to cause fewer delays to anyone using the system at the time of the backup, but this convenience is generally not worth the chance of having backups in which tables may contain data which is not concurrent.

If you have replication enabled on your server, you can monitor it via the Replication Status view.

The combination of phpMyAdmin and MySQL Administrator can provide a rather comprehensive and intuitive graphical environment from which to accomplish even advanced MySQL Database Management administrative tasks.

For those who are not comfortable with the command line, these tools can be a big asset in maintaining and developing your database applications.

Return to MySQL DBA