MySQL Database Architecture

There are two flavors of Database Management System (DBMS) known as shared-file and client-server. A shared file based DBMS consists of a database access application which interacts directly with the underlying database files. These types of database are typically designed for less demanding data storage needs and are used almost exclusively on desktop computers. Microsoft Access is a typical example of this category of DBMS. Such database systems are never used in distributed or enterprise level environments.

MySQL falls into the client-server DBMS category. A client-server DBMS is split into two components. The server component typically resides on the same physical computer as the database files and is responsible for all interactions with the database. The second component is the client. The client sends all database requests to the server which in turn processes the request and returns the results of the request back to the client.

There are a couple of key advantages to the client-server architecture DBMS. Firstly, there is no need for the client to be running on the same computer system as the server. Instead, requests can be sent by the client over a network or internet connections to the server on a remote host. The fact that the server resides on a remote computer is invisible to the client user. This makes the database available to greater numbers of users than a shared-file DBMS offers. In large scale enterprise environments this also allows high levels of fault tolerance and load balancing to be implemented.

Secondly, separating the client from the server allows a wider range of client types to be used to access the database. Valid clients can be the MySQL tools, applications written in other programming languages such as C, C++ or Java, or web based applications developed using languages such as PHP or JSP).

Whilst the advantages of the client-server architecture are clear, it is important to appreciate that there is nothing to prevent both the client and server from running on the same physical computer. The majority of this book will assume that the reader is running both the MySQL client and server components on the same computer system.