Code Architect

In this chapter we will examine client server systems and the relationship between the server and the client programs that give us access to it. First let’s define a bit clearly what the server is and what the clients are. The MySQL server is a command line program named “mysqld”. It is the only process in our database management system that directly touches and manipulates the tables in a database. The MySQL server is a single process that runs in the background waiting to receive a request from a client program. The server is multi-threaded, so each connection established by a client is realized as a new thread in the ram space occupied by the server program on its host machine. Using a multi-threaded server is more time efficient then setting up an entirely new process for every client connection. The MySQL server was designed this way because of the environment in which it was intended to operate, as an add junk program to PHP for managing dynamically build websites. Since PHP was not able to maintain a prescience connection to the database in its early days, each user action on a website would require connecting to the serve. So finding the most efficient way of reconnecting and retrieving data as quickly as possible was a major design priority and what continues to make MySQL so popular in this environment today. MySQL server also offers many choices of storage engine which are assigned on a table by table basis, this allows different tables to be accessed in the way most appropriate to their use. Each storage engine offers additional features, some of which can be used to enhance or tune the performance of the table. We will talk about storage engines in detail a bit later. The MySQL server also offers many additional features for special purposes.

There are many programs in MySQL eco system that we call client programs. The basic purpose of a client program is to act as our intermediary with the MySQL Server, which is we interact directly with the client and then the client communicate our request to the server. It then wait patiently for the server to act on our request returning any results to us. The client program the formats those results and displays them for us. The various client program from MySQL are design for different needs, in fact we can build our own client programs for specific tasks as well. But there are number of clients that we install along with the server. I suggest everyone to use, very general interactive command line client named “mysql”, spelled with all lower case letters. I suggest this client for two basic reasons, one it can transmit any command that the server can react to, and two it returns results to us in the rawest possible form that a human can interpret easily. This will be helpful in real-world, and we will gain a deeper understanding of the system by using the mysql client. Which will serve us well as we perform more complex tasks with “mysql”. Finally the client programs and the server with which they communicate can be running on the same host machine or they can be running on completely separate host located half way around the world from each other. Depending on the operating system involved there are number of communication protocol for us to choose from. For local access that where the client and server are running on the same host, we have the most choice. Although some choices are suited to our needs than others depending upon the situation we find ourselves in.

server host

Here we see a diagram that represents this, the mysqld servers as drawn here as having access to a database presumably held on some drive of the host computer. There also two client processes running on the same host and connected to the server. If the host is running a windows operating system the default intra process communicating protocol is TCP/IP. But we could also chose from either named pipe or shared memory if a special need required one of them. On a Linux, MAC OS or any other UNIX based operating system the default intra process communicating protocol is a Unix socket, although TCP/IP is still available for special circumstances. But if we need to communicate with the mysql server from the remote host our only choice is TCP/IP. The operating system on the server host and on the client host don’t matter, we can mix them any way we like. But TCP/IP is the only protocol that we can use between processes running on separate machines.

Here is a list of commonly used command line clients, they are installed along with the mysql server.

• mysql
• mysqladmin
• mysqldump
• mysqlimport
• mysqlcheck

There are many other clients, many with graphical user interfaces, either from mysql or from third parties in addition with the ones we see here. MySQL offers its workbench product for example, and name like TOAD, phpmyadmin are familiar to mysql users. And we can build our own application using a variety of programming languages as well.