M Y SQL F OUNDATIONS .1 Learning Objectives

This chapter provides the foundational knowledge to understand the basic components of MySQL for the development of fully dynamic and database-driven sites. At the completion of this chapter, you will exhibit knowledge of MySQL by being able to:

• Describe the General Architecture of MySQL

• Describe the SQL Parser and Storage Engine Tiers

• Interact with MySQL using the mysql client

• Interact with MySQL using the MySQL Query Browser 60

4.2 MySQL General Architecture

MySQL operates in a networked environment using a client/server architecture. In other words, a central program acts as a server, and various client programs connect to the server to make requests. A MySQL installation has the following major components: MySQL Server, Client programs and MySQL non-client utilities.

4.2.1 MySQL Server

MySQL Server, or mysqld, is the database server program. The server manages access to the actual database (schema) on disk and in memory. MySQL Server is multi-threaded and supports many simultaneous client connections. Clients can connect via several connection protocols. For managing database contents, the MySQL server features a modular architecture that supports multiple storage engines that handle different types of tables (for example, it supports both transactional and non-transactional tables).

Keep in mind the difference between a server and a host. The server is software (the MySQL server program mysqld). Server characteristics include its version number, whether certain features are included or excluded, and so forth. The host is the physical machine on which the server program runs. Host characteristics include its hardware configuration, the operating system running on the machine, its network addresses, and so forth.

4.2.2 Client Programs

These are programs that are used for communicating with the server to manipulate the databases that are managed by the server. MySQL AB provides several client programs. The following list describes a few of them:

o MySQL Query Browser and MySQL Administrator are graphical interfaces to the server.

o mysql is a command-line program that acts as a text-based front end for the server. It's used for issuing queries and viewing the results interactively from a terminal window.

o Other command-line clients include mysqlimport for importing data files, mysqldump for making backups, mysqladmin for server administration, and mysqlcheck for checking the integrity of the database files.

MySQL Client/Server Model 61

Instructor Notes: There are many commercial and open source applications available that are compatible with MySQL, but we do not support any others than discussed in this chapter.

MySQL runs on many varieties of Windows, Unix, and Linux, but client/server communication is not limited to environments where all computers run the same operating system. Client programs can connect to a server running on the same host or a different host, and the client and server host need not have the same operating system. For example, client programs can be used on Windows to connect to a server that is running on Linux.

Most of the concepts discussed here apply universally to any system on which MySQL runs. Platform-specific information is so indicated. Unless otherwise specified, “Unix” as used here includes Linux and other Unix-like operating systems.

4.3 SQL Parser and Storage Engine Tiers

A client retrieves data from tables or changes data in tables by sending requests to the server in the form of SQL statements. The server executes each statement using a two-tier processing model:

• The upper tier includes the SQL parser and optimizer.

• The lower tier comprises a set of storage engines.

For the most part, the SQL tier is free of dependencies on which the storage engine manages any given table. This means that clients normally need not be concerned about which engines are involved in processing SQL statements, and can access and manipulate tables using statements that are the same no matter which engine manages them. Exceptions to this engine-independence of SQL statements include the following:

• CREATE TABLE has an ENGINE option that enables you to specify which storage engine to use on a per-table basis. ALTER TABLE has an ENGINE option that enables you to convert a table to use a different storage engine.

• Some index types are available only for particular storage engines. For example, only the MyISAM engine supports full-text or spatial indexes.

• COMMIT and ROLLBACK have an effect only for tables managed by transactional storage engines such as InnoDB.

62

4.3.1 Storage Engine Breakdown

The following diagram represents a simplified view of the MySQL server and its interaction with the storage engines.

MySQL

Database Management Level

Pluggable Storage Engines

MyISAM InnoDB MySQL Cluster Cluster

Memory Other ...

Parse Optimize Retrieve

1

Store

1

The following properties are storage engine dependant:

• Storage Medium – Each table uses its own method of storing the data it contains.

• Transactional Capabilities – Certain storage engines handle transactional processing which ensures that integrity of a database is maintained during the processing of multiple SQL statements.

• Locking – Each storage engines handles the processes of the synchronization mechanism for enforcing limits on access to a resource in an environment where there are many threads of execution.

• Backup and Recovery – Based on the storage medium used, the backup of the table data and the recovery of that data can be distinct.

• Optimization – There are specific issues associated with each storage engine for the optimization of the storage of the data and retrieval of the data through the MySQL server.

• Special Features – There are a number of features that exist only in certain engine types to include full-text search, referential integrity and the ability to handle spatial data.

Most of the MySQL server operates in the same way no matter what storage engine is used: all the usual SQL commands are independent of the storage engine. Naturally, the optimizer may need to make different choices depending on the storage engine, but this is all handled through a standardized interface (API) which each storage engine supports.

63

4.4 The mysql Client

This section discusses mysql, a general-purpose client program for issuing queries and retrieving their results. It can be used interactively or in batch mode to read queries from a file.

4.4.1 Using mysql Interactively

The mysql client program enables the sending of queries to the MySQL server with any results being displayed in the same interface. It can be used interactively or it can read query input from a file in batch mode:

• Interactive mode is useful for day-to-day usage, for quick one-time queries, and for testing how queries work.

• Batch mode is useful for running queries that have been prewritten and stored in a file. It's especially valuable for issuing a complex series of queries that's difficult to enter manually, or queries that need to be run automatically by a job scheduler without user intervention.

MySQL statements such as the version query, shown below as executed within the mysql client, can also be run from the shell command prompt as part of the mysql client startup;

mysql> SELECT VERSION();

+---+

| VERSION() | +---+

| 5.0.41-log | +---+

You can execute a statement directly from the command line by using the -e or --execute option:

shell> mysql -u user_name -ppassword -e "SELECT VERSION()"

+---+

| VERSION() | +---+

| 5.0.41-log | +---+

No statement terminator is necessary unless the string following -e consists of multiple statements. In that case, separate the statements by semicolon characters.

64

4.4.2 Statement Terminators

There are numerous terminators that can be used in the mysql client to end a statement. Two terminators are the semicolon character (';') and the \g sequence. They're equivalent and may be used interchangeably:

mysql> SELECT VERSION(), DATABASE();

+---+---+

| VERSION() | DATABASE() | +---+---+

| 5.0.40-log | INFORMATION_SCHEMA | +---+---+

The \G sequence also terminates queries, but causes mysql to display query results in a vertical style that shows each output row with each column value on a separate line:

mysql> SELECT VERSION(), DATABASE()\G

************************** 1. row **************************

VERSION(): 5.0.40-log

DATABASE(): INFORMATION_SCHEMA

The \G terminator is especially useful if a query produces very wide output lines because vertical format can make the result much easier to read.

The mysql client allows a single query to be entered using multiple input lines. This makes it easier to issue a long query because you can enter it over the course of several lines. mysql will wait until it sees the statement terminator before sending the query to the server to be executed. For example:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE -> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_NAME LIKE 'E%';

In the preceding example, mysql changes the prompt from mysql> to -> to provide feedback that it's still waiting to see the end of the statement.

If a statement results in an error, mysql displays the following error message:

mysql> This is an invalid statement;

ERROR 1064 (42000): You have an error in your SQL syntax.

If it is necessary to cancel a statement once mysql has already received some of the input, enter \c and mysql will cancel the statement and return the mysql> prompt:

mysql> SELECT * FROM INFORMATION_SCHEMA > WHERE \c mysql>

To quit mysql, use \q, QUIT, or EXIT: mysql> \q 65

Instructor Notes: There are a few commands that have not been introduced yet; however, they are placed here to show other commands that can be used within MySQL. These commands will be expanded upon in future

Lab 4-A

This lab requires you to use the MySQL command line client in order to invoke the mysql client