Expert MySQL, Second Edition (2012)

Introduction

MySQL has been identified as the world’s most popular open-source database and the fastest-growing database system in the industry. This book presents some of the topics of advanced database systems, examines the architecture of MySQL, and provides an expert’s workbook for examining, integrating, and modifying the MySQL source code for use in enterprise environments. The book provides insight into how to modify the MySQL system to meet the unique needs of system integrators and educators alike.

Whom This Book Is For

I have written this book with a wide variety of readers in mind. Whether you have been working in database systems for years, or maybe have taken an introductory database-theory class, or have even just read a good Apress book on MySQL, you will get a lot out of this book. Best of all, you can even get your hands into the source code. If you ever wanted to know what makes a database system like MySQL tick, this is your book!

How This Book Is Structured

The material presented is divided into three parts followed by an appendix. Each part is designed to present a set of topics ranging from introductory material on MySQL and the open-source revolution to extending and customizing the MySQL system. There is even coverage on how to build an experimental query optimizer and execution engine as an alternative to the MySQL query engine.

Part One

The first part of the book introduces concepts in developing and modifying open-source systems. Part One provides the tools and resources necessary to begin exploring the more advanced database concepts presented in the rest of the book. Chapters include:

1. MySQL and the Open Source Revolution—This chapter is less technical and contains more narration than the rest of the book. It guides you through the benefits and responsibilities of an open-source system integrator. It highlights the rapid growth of MySQL and its importance in the open-source- and database-system markets. Additionally, it provides a clear perspective on the open-source revolution.

2. The Anatomy of a Database System—This chapter covers the basics of what a database system is and how it is constructed. The anatomy of the MySQL system is used to illustrate the key components of modern relational-database systems.

3. A Tour of the MySQL Source Code—A complete introduction to the MySQL source is presented in this chapter, along with how to obtain and build the system. You are introduced to the mechanics of the source code along with coding guidelines and best practices for how the code is maintained.

4. Test-driven MySQL Development—This chapter introduces a key element in generating high-quality extensions to the MySQL system. Software testing is presented along with the common practices of how to test large systems. Specific examples are used to illustrate the accepted practices of testing the MySQL system.

Part Two

This part of the book provides tools, using a hands-on approach to investigating the MySQL system. It introduces you to how the MySQL code can be modified and how the system can be used as an embedded-database system. Examples and projects illustrate how to debug the source code, how to modify the SQL commands to extend the language, and how to build a custom storage engine.

· 5. Debugging—This chapter provides you with debugging skills and techniques to help make development easier and less prone to failure. Several debugging techniques are presented, along with the pros and cons of each.

· 6. Embedded MySQL—This chapter presents you with a tutorial on how to embed the MySQL system in enterprise applications. Example projects assist you in applying the skills presented to your own integration needs.

· 7. Adding Functions and Command to MySQL—This chapter presents the most popular modification to the MySQL code. You are shown how to modify the SQL commands and how to build custom SQL commands. It presents examples of how to modify SQL commands to add new parameters, functions, and new commands.

· 8. Extending MySQL High Availability—This chapter provides an overview of the highavailability features of MySQL, including a tour of the replication source code and examples of how to extend the feature to meet your high-availability needs.

· 9. Developing MySQL Plugins—This chapter presents an introduction to the pluggable architecture in MySQL. You will discover how to build plugins and see a detailed example of an authentication plugin.

· 10. Building Your Own Storage Engine—This chapter demonstrates the pluggable-storageengine feature. The architecture is explored using examples and projects that permit you to build a sample storage engine.

Part Three

This part of the book takes a deeper look into the MySQL system and provides you with an insider’s look at what makes the system work. The section begins with an introduction into the more advanced database technologies. Theory and practices are presented in a no-nonsense manner to enable you to apply the knowledge gained to tackle the more complex topics of database systems. This section also presents examples of how to implement an internalquery representation, an alternative query optimizer, and an alternative query-execution mechanism. Examples and projects are discussed in detail. Chapters 12 through 14 provide the skills and techniques needed to alter the internal structure of the MySQL system to execute using alternative mechanisms. These chapters provide you with a unique insight into how large systems can be built and modified.

· 11. hapter is designed to present the more advanced database techniques by examining the MySQL architecture. Topics include query execution, multiuser concerns, and programmatic considerations.

· 12. Internal-query Representation—This chapter presents the MySQL internal-query representation. You are provided with an example alternative query representation. A discussion is included of how to alter the MySQL source code to implement an alternative query representation.

· 13. Query Optimization—This chapter presents the MySQL internal-query optimizer. You are provided with an example alternative query optimizer that uses the alternative query representation from the previous chapter. It discusses how to alter the MySQL source code to implement the alternative query optimizer.

· 14. Query Execution—This chapter combines the techniques from the previous chapters to provide you with instructions on how to modify the MySQL system to implement alternative query-processing-engine techniques.

Appendix

This section of the book provides a list of resources on MySQL, database systems, and open-source software.

Using the Book for Teaching Database-Systems Internals

Many excellent database texts offer coverage of relational theory and practice. Few, however, offer material suitable for a classroom or lab environment. There are even fewer resources available for students to explore the inner workings of database systems. This book offers an opportunity for instructors to augment their database classes with hands-on laboratories. This text can be used in a classroom setting in three ways:

1. The text can be used to add depth to an introductory undergraduate or graduate database course. Parts 1 and 2 can be used to provide in-depth coverage of special topics in database systems. Suggested topics for lectures include those presented in Chapters 2, 3, 4, and 6. These can be used in addition to more traditional database-theory or systems texts. Hands-on exercises or class projects can be drawn from Chapters 6 and 7.

2. An advanced database course for undergraduate or graduate students can be based on Parts 1 and 2; each chapter can be presented over the course of 8 to 12 weeks. The remainder of the lectures can discuss the implementation of physical storage layers and the notion of storage engines. Semester projects can be based on Chapter 10, with students building their own storage engines.

3. A special-topics course on database-systems internals for the senior undergraduate or graduate students can be based on the entire text, with lectures based on the first eleven chapters. Semester projects can be derived from Part 3 of the text, with students implementing the remaining features of the database experimental platform. These features include applications of language theory, query optimizers, and query-execution algorithms.