Section 1.0. Introduction

1.0. Introduction

The MySQL database system uses a client-server architecture that centers around the server , mysqld. The server is the program that actually manipulates databases. Client programs don't do that directly. Instead, they communicate your intent to the server by means of statements written in Structured Query Language (SQL). Client programs are installed locally on the machine from which you want to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL statements to it to have database operations performed, and receiving the statement results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts you for a statement, sends it to the MySQL server for execution, and then displays the results. This capability makes mysql useful in its own right, but it's also a valuable tool to help you with your MySQL programming activities. It's often convenient to be able to quickly review the structure of a table that you're accessing from within a script, to try a statement before using it in a program to make sure that it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used noninteractively; for example, to read statements from a file or from other programs. This enables you to use mysql from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql's capabilities so that you can use it more effectively:

• Starting and stopping mysql

• Specifying connection parameters and using option files

• Setting your PATH variable so that your command interpreter can find mysql (and other MySQL programs)

• Issuing SQL statements interactively and using batch files

• Canceling and editing statements

• Controlling mysql output format

To use the examples shown in this book, you'll need a MySQL user account and a database to work with. The first two sections of the chapter describe how to use mysql to set these up. For demonstration purposes, the examples assume that you'll use MySQL as follows:

• The MySQL server is running on the local host

• Your MySQL username and password are cbuser and cbpass

• Your database is named cookbook

For your own experimentation, you can violate any of these assumptions. Your server need not be running locally, and you need not use the username , password, or database name that are used in this book. Naturally, if you use different default values on your system, you'll need to change the examples accordingly.

Even if you do not use cookbook as the name of your database, I recommend that you create a database to be dedicated specifically to trying the examples shown here, rather than trying them with a database that you're using currently for other purposes. Otherwise, the names of your existing tables may conflict with those used in the examples, and you'll have to make modifications to the examples that are unnecessary when you use a separate database.

If you have another favorite client program to use for issuing queries, some of the concepts covered in this chapter may not apply. For example, you might prefer the graphical MySQL Query Browser program, which provides a point-and-click interface to MySQL databases. In this case, some of the principles will be different, such as the way that you terminate SQL statements. In mysql, you terminate statements with semicolon (;) characters, whereas in MySQL Query Browser there is an Execute button for terminating statements. Another popular interface is phpMyAdmin, which enables you to access MySQL through your web browser.

Scripts that create the tables used in this chapter can be found in the tables directory of the recipes distribution. Other scripts are located in the mysql directory. For information about obtaining the recipes distribution, see Appendix A.