We will assume in this discussion that you have installed MySQL in the default location on your operating system. If you have installed it elsewhere, the basic directory structure will still be the same, but the base locations will be different.

If your system came with a vendor-supplied version of MySQL, you may have a different structure. For example, if you have Red Hat 9, you will find that it has reorganized the files to fit into its overall filing system. If you have installed MySQL from a MySQL-supplied RPM, the files should be in the locations documented here.

You should also note that the directory structure will vary slightly depending on which optional items you installed and whether you installed a binary distribution or a source distribution. In this book, we assume that you installed a binary distribution as recommended in Chapter 1, “Installing MySQL.”

If you have a Unix-like operating system, the default location for the MySQL files is in /usr/local/mysql. If you have Windows, the default location is C:mysql.

At that location, you will find a set of directories, including the following (assuming that you installed from a binary distribution):

There are also a couple of other directories that the average user will rarely go into: include (containing header files), lib (containing libraries used by MySQL), share (containing MySQL's error messages), and examples (only in Windows, containing examples of using the libmysql DLL). You may need to know where these directories are if you install other software (such as PHP) that links to MySQL, but you are unlikely to interact with them directly.

The MySQL executables are in the bin and scripts directories. Let's begin by looking in the bin directory.

You will find the mysqld executable and any variations for your operating system in here. This is the MySQL server program, the one you set up to run automatically when you started your system in Chapter 1.

You will also find mysql, the MySQL monitor, here. This is the basic command-line client.

As well as these two main programs, you will find several others. We will cover the usage of many of these through the course of this book. Some particularly important programs are listed here:

We will use mysqld and mysql throughout the course of this book, and we'll revisit these other programs in Part V, “Administering MySQL.”

Now, look in the scripts directory. The programs in this directory are interpreted Perl scripts, rather than compiled programs as in the bin directory. Again, we will cover the usage of some of these as you work your way through this book. The main one we will use in this book is mysqlhotcopy, which is used for backing up databases. (This is covered in Chapter 14, “Backup and Disaster Recovery.”)

You have various options as to the user interface or client to MySQL that you choose to use. The three most popular user interfaces are the command-line interface mysql (also known as the MySQL monitor), MySQL Control Center (MySQLCC for short), and phpMyAdmin.

The MySQL monitor comes with your basic installation. It is a command-line interface. This is always available as an option, it is simple to use, and it works on all platforms.

The MySQL Control Center (MySQLCC) is a graphical user interface. It is written using the Qt windowing toolkit, which is cross-platform. At the time of writing, MySQLCC was available for Unix and Windows, and it is planned to be available for OS X in the future.

phpMyAdmin is a Web-based interface for using MySQL. It is very popular with ISPs that supply MySQL for use in developing Web applications.

If you have MySQL installed, you already have the MySQL monitor. MySQLCC is an official MySQL product, but depending on which MySQL version you have, it may be a separate download. You can get it from

www.mysql.com/downloads/mysqlcc.html

MySQLCC is extremely simple to install, and you can find instructions for this at

www.mysql.com/products/mysqlcc/install.html

phpMyAdmin can be downloaded from

www.phpmyadmin.net/

If you want to install phpMyAdmin yourself, you will first need a working Web server and PHP installation.

For the purposes of this book, it does not matter which user interface you choose to use. The functionality is basically the same for all of them. You enter queries as text, and the results are displayed as text, regardless of which user interface you use.

The examples given were usually tested using the MySQL monitor, but you can use whatever you like—mysql, MySQLCC, phpMyAdmin, or any other front end you find on the Net. For some tasks, you will need to exit from your user interface and use a command line.

We will now cover the basic use of the MySQL monitor. We covered logging in to mysql in Chapter 1. Just as a reminder, you can connect to MySQL using

The client program has some other switches you may find useful. If you are connecting to a MySQL database on another machine, you can use the -h switch to specify the host; for example:

A really useful option to mysql is the --i-am-a-dummy option. You can also invoke this option in a less pejorative way using --safe-updates. For example:

The effect of this switch is to limit the damage you can do with a single command. This is an especially useful option (available for the command-line tool only) while you are first learning to use MySQL. You might like to use it while working your way through this book. (Specifically, this switch stops you from updating or deleting rows unless you specify a key value for those rows. If you don't know what this means yet, don't worry. All will become clear in Part III, “Using MySQL.”)

After you're logged in, you can see what databases exist on the system by using the SHOW command:

For most of you, this will be a short list at this stage. You should see the database called mysql in the list. This is the system database that holds information about user accounts and privileges. We'll discuss it later in the book.

Notice that the command has a semicolon at the end of the line. Most commands you type in the monitor need to be terminated with a semicolon; otherwise, MySQL will not execute them. Try typing

and pressing Enter. MySQL just sits there and waits. You can now type a semicolon and press Enter, and the command is executed. This allows you to split complex commands over multiple lines for readability. You can also type g (backslash g) instead of the semicolon, but most people use the semicolon.

You can select a database from this list and type this:

(Substitute the name of the database you want to use.)

This tells MySQL that you want to work with a particular database. Pick one and do this. (You may not have sufficient privileges to select any of the databases. If you get a message to this effect, pick a different database and try again.)

After you have selected a database, you can see what tables are in it by typing

You can get information on a particular table by typing

You can log out of the monitor by typing

You will notice that this command does not end with a semicolon. There is a set of commands that begin with (backslash). None of them need a terminating semicolon. You can get a list of these commands by typing

(The h is for help.)

You can type commands and SQL statements directly into the monitor. However, you can also put these commands and statements into a file and run them all at once, like a script. We will do this later in the book, when creating databases in Chapter 4, “Creating Databases, Tables, and Indexes,” for example.

If you are logged in to the MySQL monitor, you can run a file of commands by typing

If you are not logged into the monitor, you can execute a file of commands by using file redirection; for example:

You now know the basics of how to use the mysql client program.

In Chapter 3, “Database Design Crash Course,” we will look at general database concepts, terminology, and design principles including normalization. If you are not new to databases but are new to MySQL, you can skim this chapter.