book

Understanding MySQL Internals

Name: Understanding MySQL Internals
Author: Sasha Pachev
ISBN: 9780596009571

by Sasha Pachev

April 2007

Intermediate to advanced

254 pages

8h 9m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Dedication
A Note Regarding Supplemental Files
Preface
How This Book Is OrganizedWho This Book Is ForConventions Used in This BookUsing Code ExamplesComments and QuestionsSafari® EnabledAcknowledgments
1. MySQL History and Architecturel
1.1. MySQL History1.2. MySQL Architecture1.2.1. Core Modules1.2.2. Interaction of the Core Modules1.2.3. Detailed Look at the Core Modules1.2.3.1. Server Initialization Module1.2.3.2. Connection Manager1.2.3.3. Thread Manager1.2.3.4. Connection Thread1.2.3.5. User Authentication Module1.2.3.6. Access Control Module1.2.3.7. Parser1.2.3.8. Command Dispatcher1.2.3.9. Query Cache Module1.2.3.10. Optimizer1.2.3.11. Table Manager1.2.3.12. Table Modification Modules1.2.3.13. Table Maintenance Module1.2.3.14. Status Reporting Module1.2.3.15. Abstracted Storage Engine Interface (Table Handler)1.2.3.16. Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)1.2.3.17. Logging Module1.2.3.18. Replication Master Module1.2.3.19. Replication Slave Module1.2.3.20. Client/Server Protocol API1.2.3.21. Low-Level Network I/O API1.2.3.22. Core API
2. Nuts and Bolts of Working with the MySQL Source Code
2.1. Unix Shell2.2. BitKeeper2.3. Preparing the System to Build MySQL from BitKeeper Tree2.4. Building MySQL from BitKeeper Tree2.5. Building from Source Distribution2.6. Installing MySQL into a System Directory2.7. Source Code Directory Layout2.8. Preparing the System to Run MySQL in a Debugger2.9. Debugger-Guided Source Tour2.10. Basics of Working with gdb2.11. Finding Things in the Source2.12. Interesting Breakpoints and Variables2.13. Making a Source Modification2.14. Coding Guidelines2.14.1. Stability2.14.2. Portability2.14.3. Performance2.14.4. Style and Ease of Integration2.15. Keeping Your BitKeeper Repository Up to Date2.16. Submitting a Patch
3. Core Classes, Structures, Variables, and APIs
3.1. THD3.2. NET3.3. TABLE3.4. Field3.5. Utility API Calls3.6. Preprocessor Macros3.7. Global Variables
4. Client/Server Communication
4.1. Protocol Overview4.2. Packet Format4.3. Relationship Between MySQL Protocol and OS Layer4.4. Authenticating Handshake4.4.1. Authentication Protocol Security4.4.2. Protocol Capabilities Bit Mask4.5. Command Packet4.6. Server Responses4.6.1. Data Field4.6.2. OK Packet4.6.3. Error Packet4.6.4. EOF Packet4.6.5. Result Set Packets
5. Configuration Variables
5.1. Configuration Variables Tutorial5.1.1. Configuration File and Command-Line Options5.1.2. Internals of the Configuration Option Parsing5.1.3. Example of Adding a New Configuration Option5.2. Interesting Aspects of Specific Configuration Variables5.2.1. big-tables5.2.2. concurrent-insert5.2.3. core-file5.2.4. default-storage-engine5.2.5. delay-key-write5.2.6. ft_stopword_file5.2.7. innodb_buffer_pool_size5.2.8. innodb_flush_log_at_trx_commit5.2.9. innodb_file_per_table5.2.10. innodb_lock_wait_timeout5.2.11. innodb_force_recovery5.2.12. init-file5.2.13. key_buffer_size5.2.14. language5.2.15. log5.2.16. log-bin5.2.17. log-isam5.2.18. log-slow-queries5.2.19. max_allowed_packet5.2.20. max_connections5.2.21. max_heap_table_size5.2.22. max_join_size5.2.23. max_sort_length5.2.24. myisam-recover5.2.25. query_cache_type5.2.26. read_buffer_size5.2.27. relay-log5.2.28. server-id5.2.29. skip-grant-tables5.2.30. skip-stack-trace5.2.31. slave-skip-errors5.2.32. sort_buffer_size5.2.33. sql-mode5.2.34. table_cache5.2.35. temp-pool5.2.36. transaction-isolation
6. Thread-Based Request Handling
6.1. Threads Versus Processes6.1.1. Advantages of Using Threads6.1.2. Disadvantages of Using Threads6.1.3. Advantages of Using Forked Processes6.1.4. Disadvantages of Using Forked Processes6.2. Implementation of Request Handling6.2.1. Structures, Variables, Classes, and API6.2.2. Execution Walk-Through6.3. Thread Programming Issues6.3.1. Standard C Library Calls6.3.2. Mutually Exclusive Locks (Mutexes)6.3.3. Read-Write Locks6.3.4. Synchronization6.3.5. Preemption
7. The Storage Engine Interface
7.1. The handler Class7.1.1. handlerton7.2. Adding a Custom Storage Engine to MySQL7.2.1. Integration Instructions for Version 4.17.2.2. Integration Instructions for Version 5.1

8. Concurrent Access and Locking
8.1. Table Lock Manager8.1.1. Read Lock Request8.1.2. Write Lock Request8.1.2.1. Storage engine interaction with the table lock manager8.1.3. InnoDB Locking8.1.3.1. Lock types8.1.3.2. Record locking8.1.3.4. Dealing with deadlocks
9. Parser and Optimizer
9.1. Parser9.1.1. Lexical Scanner9.1.2. Grammar Rules Module9.1.3. Parse Tree9.2. Optimizer9.2.1. Basics of the Optimizer Algorithm9.2.2. Using EXPLAIN to Understand the Optimizer9.2.2.1. Understanding the output of EXPLAIN9.2.2.2. Select types9.2.2.3. Record access types9.2.2.4. Extra field9.2.3. Range Optimizer9.2.3.1. Range9.2.3.2. Index_merge9.2.3.3. Range_desc9.2.3.4. Fulltext9.2.3.5. ROR_intersect9.2.3.6. ROR_union9.2.3.7. Group_min_max9.2.4. Subquery Optimization9.2.5. Core Optimizer Classes and Structures9.2.5.1. JOIN9.2.5.2. JOIN_TAB9.2.5.3. select_result9.2.6. SELECT Parse Tree9.2.6.1. Execution of a SELECT on the code level
10. Storage Engines
10.1. Shared Aspects of Architecture10.1.1. MyISAM10.1.2. MyISAM Architecture10.1.2.1. Datafile10.1.2.2. Index file10.1.3. MyISAM Key Types10.1.3.1. B-tree keys10.1.3.2. Full-text keys10.1.3.4. Spatial keys10.2. InnoDB10.3. Memory (Heap)10.4. MyISAM Merge10.5. NDB10.6. Archive10.7. Federated
11. Transactions
11.1. Overview of Transactional Storage Engine Implementation11.2. Implementing the handler Subclass11.3. Defining the handlerton11.4. Working with the Query Cache11.5. Working with the Replication Binary Log11.6. Avoiding Deadlocks
12. Replication
12.1. Overview12.2. Statement-Based Versus Row-Based Replication12.3. Two-Threaded Slave12.4. Multi-Master12.5. SQL Commands to Help Understand Replication12.6. Binary Log Format12.7. Creating a Custom Replication Utility
About the Author
Colophon
Copyright

Content preview from Understanding MySQL Internals

Chapter 4. Client/Server Communication

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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596009577Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design