Chapter 23. MySQL Architecture
23.1. Client/Server Overview
The MySQL database system operates using a client/server architecture. The server is the central program that manages database contents, and client programs connect to the server to retrieve or modify data. MySQL also includes non-client utility programs and scripts. Thus, a complete MySQL installation consists of three general categories of programs:
In addition to the types of programs just described, MySQL AB also makes available several interfaces that can be used by third-party client programs to access the server. These include the API that is provided in the form of a client library written in C that can be linked into other programs, and a family of MySQL Connectors. The connectors are drivers that act as bridges to the MySQL server for client programs that communicate using a particular protocol. Currently, MySQL AB provides MySQL Connector/OBDC, MySQL Connector/J, and MySQL Connector/NET, which are connectors for clients that use the ODBC, JDBC, or .NET protocols. The C client library is available as part of MySQL distributions. The connectors are available as separate packages. (See Chapter 4, "MySQL Connectors.")
The MySQL database system has several important characteristics that enable it to be used in many computing environments:
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.
23.2. Communication Protocols
A MySQL client program can connect to a server running on the same machine. This is a local connection. A client can also connect to a server running on another machine, which is a remote connection.
MySQL supports connections between clients and the server using several networking protocols, as shown in the following table.
Some protocols are applicable for connecting to either local or remote servers. Others can be used only for local servers. Some protocols are specific to a given operating system.
From the client perspective, a client run on the same host as the server can use any of the connection protocols that the server supports. If the client is run on a different host, connections always use TCP/IP.
To enable you to indicate which kind of connection to use and which server to connect to, MySQL client programs understand a standard set of command-line options. Section 1.2, "Invoking Client Programs," discusses the syntax for these options and how to use them when invoking client programs.
MySQL communication protocols are implemented by various libraries and program drivers. Client programs included with MySQL distributions (mysql, mysqladmin, and so forth) establish connections to the server using the native C client library. However, other interfaces are available, such as the MySQL Connectors mentioned in Section 23.1, "Client/Server Overview."
The different connection methods are not all equally efficient:
23.4. How MySQL Uses Disk Space
MySQL Server uses disk space in several ways, primarily for directories and files that are found under a single location known as the server's data directory. The server uses its data directory to store all the following:
Chapter 24, "Starting, Stopping, and Configuring MySQL," contains additional information about configuration-related aspects of the data directory, such as how to determine its location or set up logging. Chapter 29, "Storage Engines," discusses how storage engines manage table files under the data directory.
23.5. How MySQL Uses Memory
MySQL Server memory use includes data structures that the server sets up to manage communication with clients and to process the contents of databases. The server allocates memory for many kinds of information as it runs:
Several SHOW statements enable you to check the sizes of various memory-related parameters. SHOW VARIABLES displays server system variables so that you can see how the server is configured. SHOW STATUS displays server status variables. The status indicators enable you to check the runtime state of caches, which can be useful for assessing the effectiveness with which they are being used and for determining whether you would be better off using larger (or in some cases smaller) buffers.
Server memory use can be tuned by setting buffer sizes using command-line options or in an option file that the server reads at startup time. For more information, see Chapter 39, "Optimizing the Server."
Chapter 24. Starting, Stopping, and Configuring MySQL
This chapter discusses the issues involved in setting up and configuring MySQL. It covers the following exam topics:
This chapter covers general methods for starting and stopping MySQL Server. The material here assumes that MySQL has been installed already and does not go into detail about installation procedures. Those details can be found in the installation chapter of the MySQL Reference Manual. If you need to install MySQL, you can get any distribution files you need from the MySQL AB Web site (http://dev.mysql.com).
24.1. Types of MySQL Distributions
MySQL is available for several operating systems. Those covered in this chapter are Windows and Unix. Unless otherwise specified, "Unix" as used here includes Linux and other Unix-like operating systems.
You can install MySQL from a binary distribution that contains precompiled programs ready to run, or you can compile MySQL yourself from a source distribution. This section describes the various types of MySQL distributions from which you can choose.
24.1.1. MySQL Binary Distributions
On Windows, you can choose from these types of binary distributions:
The installer included with Essentials and Complete distributions allows you to choose where to install MySQL. By default, it installs MySQL 5.0 in %ProgramFiles%\MySQL\MySQL Server 5.0, where %ProgramFiles% has a value such as C:\Program Files. The No-install distribution is just a Zip archive. To install it, unpack it and move it to the desired installation location.