Server Administration

  Home arrow Server Administration arrow Client/Server Communication

Client/Server Communication
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 3 stars3 stars3 stars3 stars3 stars / 2

    Table of Contents:
  • Client/Server Communication
  • Relationship Between MySQL Protocol and OS Layer
  • Authentication Protocol Security
  • Protocol Capabilities Bit Mask



    Client/Server Communication

    (Page 1 of 4 )

    If you want to get a better handle on client/server communication, look no further. This three-part article series will help you understand the details of the MySQL client/server protocol, and more. It is excerpted from chapter four of the book Understanding MySQL Internals, written by Sasha Pachev (O'Reilly, 2007; ISBN: 0596009577). Copyright © 2007 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

    In this chapter we will discuss the details of the client/server communication in MySQL. The goal is to give you the ability to look at a binary dump of the client/server communication and be able to understand what happened. This chapter can also be helpful if you are trying to write a MySQL proxy server, a security application to audit MySQL traffic on your network, or some other program that for some reason needs to understand the low-level details of the MySQL client/server protocol.

    Protocol Overview

    The server listens for connections on a TCP/IP port or a local socket. When a client connects, a handshake and authentication are performed. If successful, the session begins. The client sends a command, and the server responds with a data set or a message appropriate for the type of command that was sent. When the client is finished, it sends a special command telling the server it is done, and the session is terminated.

    The basic unit of communication is the application-layer packet. Commands consist of one packet. Responses may include several.

    Packet Format

    There are two types of packets: compressed and noncompressed. The decision on which one will be used for the session is made during the handshake stage, and depends on the capabilities and settings of both the client and the server.

    Additionally, regardless of the compression option, the packets are divided into two categories: commands sent by the client, and responses returned by the server.

    Server response packets are divided into four categories: data packets, end-of-data-stream packets, success report (OK) packets, and error message packets.

    All packets share the common 4-byte header, documented in Table4-1 .

    Table 4-1. Common 4-byte header for uncompressed packets

    Offset Length Description
    0 3 Packet body length stored with the low byte first.
    3 1

    Packet sequence number. The sequence numbers are reset with each new command.


    While the correct packet sequencing is ensured by the underlying transmission protocol, this field is used for the sanity checks of the application logic.


    A compressed packet will have an additional 3-byte field, low byte first, containing the length of the compressed packet body part that follows. An uncompressed packet will have the body immediately after the header.

    The compression is done with the use of ZLIB (see The body of the compressed packet is exactly what a call tocompress()with the uncompressed body as argument would return. It is, however, possible for the body to be stored without compression when the compressed body would turn out no smaller than the uncompressed one, or whencompress()fails for some reason—e.g., due to the lack of available memory. If this happens, the uncompressed length field will contain 0.

    It is important to remember, though, that even in that case, the compressed format is still used, which unfortunately results in the waste of 3 bytes per packet. Therefore, a session that predominately uses small or poorly compressible packets goes faster if the compression is turned off.

    As you may have noticed, the 3-byte field would limit the body length to 16 MB. What if you need to send a bigger packet? In version 3.23 and earlier, it is not possible. Version 4.0 added a compatible improvement to the protocol that overcame this limitation. If the length of the packet is greater than the value ofMAX_PACKET_LENGTH, which is defined to be 224–1 in sql/, the packet gets split into smaller packets with bodies ofMAX_PACKET_LENGTHplus the last packet with a body that is shorter thanMAX_PACKET_LENGTH. The last short packet will always be present even if it must have a zero-length body. It serves as an indicator that there are no more packet parts left in the stream for this large packet.

    More Server Administration Articles
    More By O'Reilly Media

    blog comments powered by Disqus


    - SSH Case Studies: Gateway Hosts
    - SSH Case Studies: More on Pine and SSH
    - SSH Case Studies: Pine and IMAP
    - SSH Case Studies: More on the Passive Mode
    - SSH Case Studies: Network Address Translation
    - SSH Case Studies: The Passive Mode
    - SSH Case Studies: The FTP Protocol
    - SSH Case Studies: Batch Jobs, FTP and SSH
    - SSH Case Studies: Agents and Authentication
    - SSH Case Studies
    - Server Responses to Client Communication
    - Authentication in Client/Server Communication
    - Client/Server Communication
    - Understanding Awk in the UNIX Shell
    - Stream Editor in the UNIX Shell

    Developer Shed Affiliates


    © 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap