Additional Tools for MySQL

Chapter 2 - What is MySQL?
byGareth Downes-Powellet al.
Wrox Press 2003

In this section, we will review some of the most common tools to administer a MySQL database on the Windows platform. Both of these tools provide a convenient way to execute SQL statements so you don't need to install both of them.

The purpose of an administrative tool is to provide a convenient interface to let you perform administrative tasks quickly and easily. Both of the tools we present here have their strengths and weaknesses, but they complement each other well. That's why we suggest installing both, and depending on the tasks you have, use either one or the other.

MyCC

MyCC is an open-source MySQL administration tool co-developed by MySQL AB and Jorge del Conde. It is a control panel that makes administering MySQL very easy, and is available for Windows and Linux platforms.

It is available for download from the MySQL web-site: http://www.MySQL.com/. It is currently beta software. That doesn't mean that it doesn't work correctly, but it is not yet a finished product, and will be updated very often.

At the current stage of the development, MyCC is more oriented for administrative tasks, like management of the server, creation of user, but has a very good SELECT statement interface.

Installing MyCC

MyCC binary distribution comes with an installer. It will install MyCC on your Windows platform exactly like many other installers do.

Connecting To a Database

When first you launch MyCC, it will look like this:

Clicking on the Register Server button will launch the Database Connection Dialog:

On this dialog, enter the hostname where your MySQL server is running, the user you want to connect with and its password. You may leave the other default options as they are. When you click on Add, it will just register a new server in the server tree interface. You will need to right-click on the server, and select the menu item Connect. Once you are connected to your server, the tree will expand in the following items:

• Databases: Contains all the databases registered in your server

• Server Administration

• User Administration

The Server Administration allows you to perform some administration tasks, like reloading the server, shutting it down, and flushing different elements.

The User Administration will allow you to create and set up the user privileges.

List of Tables and Table Structure

To get the list of table, use the left-pane tree, and expand the Database item. Click on the Tables subitem. The right pane will show you a grid containing all the tables, the number of records the size in bytes, the creation date, and the table comment. The following picture shows you an example:

To analyze a table structure, use the left-pane tree. Expand the Database item and select your table. On the right panel you will see the structure of your table. The following picture shows you an example:

Executing a SQL Statement

To execute a SQL statement, you can use the SQL button. It will launch the Query in Database dialog box. The following picture shows you an example:

Enter your query in the upper panel, and click on the '/'button to run your query. Results will appear on the middle pane. If the result panel is not activated, click on the menu item Query -> Result Pane.

PHPMyAdmin

PHPMyAdmin is a very popular tool for administering a MySQL database. It is entirely written in PHP, so you need to have a working PHP server in order to run PHPMyAdmin. See Chapter 1 if you haven't set up a PHP server already.

PHPMyAdmin provides a very good interface to let you handle very specific records in a table, but will also let you configure the server and create users, though MyCC provides a better interface for that kind of task.

Obtaining PHPMyAdmin

The official PHPMyAdmin web site is http://www.phpmyadmin.net. On this page, you will find all the information to download PHPMyAdmin from Sourceforge, and you can find out about the latest version and about the changes made from previous versions. The version used in this book is 2.2.7-p11

Installing PHPMyAdmin

To install PHPMyAdmin, first you will need to decompress the archive you downloaded into a directory that is served by a PHP-aware HTTP server. If you want to test the PHP ability of a special directory, just write into a little .php file with the following PHP code: <?php echo phpinfo () ?>. If you get the sourcecode, then your server does not handle PHP. If you get the whole PHP information, then your directory should be able to run PHPMyAdmin.

Connecting to a MySQL Server

Once you have installed PHPMyAdmin, open the file name config.inc.php with your favorite text editor (Notepad should be fine). This file contains the configuration options of PHPMyAdmin. In this file you will find the declaration of the server array that will contain the information needed like the hostname, the user, and the password. This part of the config.inc.php file will look something like this:

     $cfgServers[$i]["host"]            = "localhost";     .     .     $cfgServers[$i]["user"]            = "dwmxphp";     $cfgServers[$i]["password"]        = "glasshaus"; 

Change these settings according to your server specification. The values used to describe the MySQL server are strings. Don't forget to leave the "characters.

You will also have to set the location of the PHPMyAdmin directory by altering the following lines:

     $cfgPmaAbsoluteUri = 'http://localhost/phpmyadmindir/'; 

Where localhost is the machine PHPMyAdmin is running on and phpmyadmindir is the directory in which you installed PHPMyAdmin.

Running PHPMyAdmin

To access your PHPMyAdmin enter the URL of the directory you decompressed PHPMyAdmin in. If you get an error message try to add /index.php to the URL.

The homepage of PHPMyAdmin looks like this:

List of Tables and Table Structure

As alway, when using MySQL, you will first need to select your working database. To do that with PHPMyAdmin, use the dropdown menu in the left frame.

To get the list of tables and the information related to each table, click on the database name on top of the tables list, in our example click on the item glassmedia.

The table list shows like this:

To get the information on a specific table, you can either click on the table name, on the left frame. Or if you are in the page showing database information, you can click on the Properties link with regards to your table name.

Executing an SQL Statement

To execute SQL statement with PHPMyAdmin, you must be on one of the two following pages:

• The Database structure

• The Table structure.

If you are on the Database structure page, you will have a text area where you can enter the query you want to run. Remember that you are not limited to the SELECT statement. You can execute every kind of SQL statement you wish.

If you are on the Table structure page, then you have almost the same text area, except that the content has been prepared for you. You will generally find an SQL query like: SELECT * FROM tablename WHERE 1.