MySQL Tools

As just explained, MySQL is a client-server DBMS, and so to use MySQL you'll need a client, an application that you'd use to interact with MySQL (giving it commands to be executed).

There are lots of client application options, but when learning MySQL (and indeed, when writing and testing MySQL scripts) you are best off using a utility designed for just that purpose. And there are three tools in particular that warrant specific mention.

`mysql` Command-Line Utility

Every MySQL installation comes with a simple command-line utility called mysql . This utility does not have any drop-down menus , fancy user interfaces, mouse support, or anything like that.

Typing mysql at your operating system command prompt brings up a simple prompt that looks like this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.4-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Note

MySQL Options and Parameters If you just type mysql by itself, you might receive an error message. This will likely be because security credentials are needed or because MySQL is not running locally or on the default port. mysql accepts an array of command-line parameters you can (and might need to) use. For example, to specify a user login name of ben , you'd use mysql u ben . To specify a username, host name, port, and be prompted for a password, you'd use mysql u ben p h myserver P 9999 .

A complete list of command-line options and parameters can be obtained using mysql help .

Of course, your version and connection information might differ , but you'll be able to use this utility regardless. You'll note that

Commands are typed after the mysql> prompt.
Commands end with ; or \g ; in other words, just pressing Enter will not execute the command.
Type help or \h to obtain help. You can also provide additional text to obtain help on specific commands (for example, help select to obtain help on using the SELECT statement).
Type quit or exit to quit the command-line utility.

The mysql command-line utility is one of the most used and is invaluable for quick testing and executing scripts (such as the sample table creation and population scripts mentioned in the previous chapter and in Appendix B, "The Example Tables"). In fact, all of the output examples used in this book are grabs from the mysql command-line output.

Tip

Familiarize Yourself with the mysql Command-Line Utility Even if you opt to use one of the graphical tools described next , you should make sure to familiarize yourself with the mysql command-line utility, as this is the one client you can safely rely on to always be present (as it is part of the core MySQL installation).

MySQL Administrator

MySQL Administrator is a graphical interactive client designed to simplify the administration of MySQL servers.

Note

Obtaining MySQL Administrator MySQL Administrator is not installed as part of the core MySQL installation. Instead, it must be downloaded from http://dev.mysql.com/downloads/ (versions are available for Linux, Mac OS X, and Windows, and source code is downloadable, too).

MySQL Administrator prompts for server and login information (and allows you to save server definitions for future selection), and then displays icons that allow for the selection of different views. Amongst these are

Server Information displays status and version information about the connected server and client.
Service Control allows you to stop and start MySQL as well as specify server features.
User Administration is used to define MySQL users, logins, and privileges.
Catalogs lists available databases and allows for the creation of databases and tables.

Note

Create a Datasource for This Book You might want to use the Create New Schema option to create a datasource for the tables and chapters in this book. The examples use a datasource named crashcourse ; feel free to use the same name or one of your choice.

Tip

Quick Access to Other Tools The MySQL Administrator Tools menu contains options to launch the mysql command-line utility (described previously) and the MySQL Query Browser (described next).

In fact, the MySQL Query Browser also contains menu options to launch the mysql command-line utility and the MySQL Administrator.

MySQL Query Browser

MySQL Query Browser is a graphical interactive client used to write and execute MySQL commands.

Note

Obtaining MySQL Query Browser Like MySQL Administrator, MySQL Query Browser is not installed as part of the core MySQL installation. Instead, it too must be downloaded from http://dev.mysql.com/downloads/ (versions are available for Linux, Mac OS X, and Windows, and source code is downloadable, too).

MySQL Query Browser prompts for server and login information (saved definitions are shared between MySQL Query Browser and MySQL Administrator) and then displays the application interface. Note the following:

MySQL commands are typed into the window at the top of the screen. When the statement has been entered, click the Execute button to submit it to MySQL for processing.
Results (if there are any) are displayed in a grid in the large area to the left of the screen.
Multiple statements and results can be rendered in their own tabs to allow for rapid switching between them.
On the right of the screen is a tab that lists all available datasources (called schemata here), expand any datasource to see its tables, and expand any table to see its columns .
You can also select tables and columns to have MySQL Query Browser write MySQL statements for you.
To the right of the Schemata tab is a History tab that maintains a history of executed MySQL statements. This is very useful when you need to test different versions of MySQL statements.
Help on MySQL syntax, functions, and more is available at the bottom right of the screen.

Tip

Execute Saved Scripts You can use MySQL Query Browser to execute saved scriptsthe scripts used to create and populate the tables used in throughout this book, for example. To do this, select File, Open Script, select the script (which will be displayed in a new tab), and click the Execute button.