Page 1
goupee.vn goupee.com Introducing the MySQL 8 Document Store Building schemaless database solutions — Charles Bell fb.com/goupee.vn www.allitebooks.com
Génération de l'aperçu...
goupee.vn goupee.com Introducing the MySQL 8 Document Store Building schemaless database solutions — Charles Bell fb.com/goupee.vn www.allitebooks.com
goupee.vn goupee.com Introducing the MySQL 8 Document Store Charles Bell fb.com/goupee.vn www.allitebooks.com
goupee.vn goupee.com Introducing the MySQL 8 Document Store Charles Bell Warsaw, Virginia, USA ISBN-13 (pbk): 978-1-4842-2724-4 ISBN-13 (electronic): 978-1-4842-2725-1 https://doi.org/10.1007/978-1-4842-2725-1 Library of Congress Control Number: 2018945864 Copyright © 2018 by Charles Bell This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover designed by eStudioCalamar Cover image designed by Freepik (www.freepik.com) Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer- sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail rights@apress.com, or visit http://www.apress.com/ rights-permissions. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the book’s product page, located at www.apress.com/9781484227244. For more detailed information, please visit http://www.apress.com/source-code. Printed on acid-free paper fb.com/goupee.vn www.allitebooks.com
goupee.vn goupee.com I dedicate this book to the open source enthusiasts of the world who make the MySQL ecosystem strong. Live long and prosper, Sakila! fb.com/goupee.vn www.allitebooks.com
goupee.vn goupee.com Table of Contents About the Author ��������������������������������������������������������������������������������������������������� xiii About the Technical Reviewer ���������������������������������������������������������������������������������xv Acknowledgments �������������������������������������������������������������������������������������������������xvii Introduction ������������������������������������������������������������������������������������������������������������xix Chapter 1: Introducing MySQL 8—A New Beginning������������������������������������������������ 1 Old Features New Again................................................................................................................ 3 JSON Data Type ....................................................................................................................... 4 MySQL Shell ............................................................................................................................ 8 X Plugin, X Protocol, and X DevAPI ........................................................................................ 12 InnoDB Improvements ........................................................................................................... 14 New Features.............................................................................................................................. 16 Data Dictionary...................................................................................................................... 17 Account Management............................................................................................................ 18 Removed Options, Variables, and Features................................................................................. 19 Paradigm Shifting Features ........................................................................................................ 20 Group Replication .................................................................................................................. 21 InnoDB Cluster....................................................................................................................... 25 Summary..................................................................................................................................... 34 Chapter 2: Getting Started with MySQL ������������������������������������������������������������������ 37 Getting to Know MySQL .............................................................................................................. 37 How Do I Connect to MySQL? ................................................................................................ 39 How to Get and Install MySQL ............................................................................................... 42 Configuring and Managing Access to MySQL........................................................................ 48 v fb.com/goupee.vn www.allitebooks.com
goupee.vn goupee.com TABLE OF CONTENTS A MySQL Primer .......................................................................................................................... 54 Creating Databases and Tables ............................................................................................. 54 Searching for Data................................................................................................................. 57 Creating Data ........................................................................................................................ 61 Updating Data........................................................................................................................ 62 Deleting Data......................................................................................................................... 63 Using Indexes ........................................................................................................................ 64 Views..................................................................................................................................... 65 Triggers.................................................................................................................................. 65 Simple Joins.......................................................................................................................... 66 Stored Routines ..................................................................................................................... 69 Summary..................................................................................................................................... 70 Chapter 3: JSON Documents����������������������������������������������������������������������������������� 73 Concepts and Technologies: Jargon Explained ........................................................................... 74 Origins: Key, Value Mechanisms ............................................................................................ 74 JSON...................................................................................................................................... 76 Application Programming Interface....................................................................................... 76 NoSQL Interface..................................................................................................................... 77 Document Store..................................................................................................................... 78 Introducing JSON Documents ..................................................................................................... 79 JSON Format Rules ............................................................................................................... 79 Using JSON in MySQL............................................................................................................ 81 Path Expressions ................................................................................................................... 86 JSON Functions ..................................................................................................................... 94 Combining SQL and JSON - Indexing JSON Data...................................................................... 125 Summary................................................................................................................................... 133 Chapter 4: The MySQL Shell���������������������������������������������������������������������������������� 135 Getting Started.......................................................................................................................... 136 Features............................................................................................................................... 137 Shell Commands.................................................................................................................. 139 Options ................................................................................................................................ 141 vi fb.com/goupee.vn
goupee.vn goupee.com TABLE OF CONTENTS Sessions and Modes............................................................................................................ 144 Connections......................................................................................................................... 146 Set Up and Install...................................................................................................................... 150 Install the MySQL Shell........................................................................................................ 151 Setup the X Plugin ............................................................................................................... 155 Tutorial: MySQL Shell by Example............................................................................................. 158 Installing the Sample Database........................................................................................... 159 SQL ...................................................................................................................................... 162 JavaScript............................................................................................................................ 169 Python ................................................................................................................................. 172 Summary................................................................................................................................... 174 Chapter 5: X Developer API����������������������������������������������������������������������������������� 175 Overview ................................................................................................................................... 176 Clients.................................................................................................................................. 177 Target Language Conformity ............................................................................................... 178 MySQL X Module ................................................................................................................. 180 Classes and Methods................................................................................................................ 181 Session Class ...................................................................................................................... 185 CRUD Operations ................................................................................................................. 191 Example Data Used in this Chapter ..................................................................................... 219 Working with Data Sets ....................................................................................................... 220 Expressions ......................................................................................................................... 233 Warnings and Errors............................................................................................................ 236 Additional Features ................................................................................................................... 244 Parameter Binding............................................................................................................... 244 Method Chaining ................................................................................................................. 248 CRUD Prepared Statements................................................................................................. 249 Asynchronous Execution ..................................................................................................... 251 For More Information ................................................................................................................ 252 Summary................................................................................................................................... 253 vii fb.com/goupee.vn
goupee.vn goupee.com TABLE OF CONTENTS Chapter 6: X Plugin����������������������������������������������������������������������������������������������� 255 Overview ................................................................................................................................... 255 Features............................................................................................................................... 256 Going Deeper—Journey into the Source Code.................................................................... 260 Options and Variables ............................................................................................................... 263 How to View Values of Variables ......................................................................................... 263 How to Set Values of Variables............................................................................................ 266 System Variables and Startup Options ................................................................................ 269 Status Variables................................................................................................................... 271 Monitoring the X Plugin............................................................................................................. 271 Communication ................................................................................................................... 273 Connections......................................................................................................................... 274 CRUD Operations ................................................................................................................. 275 Errors and Warnings............................................................................................................ 276 Sessions .............................................................................................................................. 277 SSL ...................................................................................................................................... 278 Statements .......................................................................................................................... 280 Worker Threads ................................................................................................................... 281 Summary................................................................................................................................... 282 Chapter 7: X Protocol�������������������������������������������������������������������������������������������� 285 Overview ................................................................................................................................... 285 Goals for the X Protocol....................................................................................................... 288 X Protocol and Protocol Buffers........................................................................................... 290 X Protocol: Under the Hood ....................................................................................................... 299 Protobuf Implementation..................................................................................................... 299 X Protocol Examples............................................................................................................ 304 X Protocol Walkthrough ....................................................................................................... 309 viii fb.com/goupee.vn
goupee.vn goupee.com TABLE OF CONTENTS Creating X Clients...................................................................................................................... 315 Setup for the Examples ....................................................................................................... 317 Document Store Example .................................................................................................... 319 Relational Data Example...................................................................................................... 325 Summary................................................................................................................................... 329 Chapter 8: Library Application: User Interface ����������������������������������������������������� 331 Getting Started.......................................................................................................................... 332 Library Application............................................................................................................... 332 Setup Your Environment ...................................................................................................... 333 Flask Primer.............................................................................................................................. 341 Terminology ......................................................................................................................... 343 Initialization and the Application Instance........................................................................... 345 HTML Files and Templates................................................................................................... 354 Error Handlers ..................................................................................................................... 368 Redirects ............................................................................................................................. 370 Additional Features.............................................................................................................. 371 Flask Review: Sample Application....................................................................................... 372 Library Application User Interface Design................................................................................. 374 Preparing the Directory Structure ....................................................................................... 375 User Interface Features ....................................................................................................... 376 Form Classes....................................................................................................................... 380 View Functions .................................................................................................................... 384 Templates ............................................................................................................................ 387 Application Code.................................................................................................................. 393 Summary................................................................................................................................... 397 Chapter 9: Library Application: Database Implementations��������������������������������� 399 Version 1: Relational Database ................................................................................................. 400 Database Design.................................................................................................................. 400 Database Code .................................................................................................................... 404 ix fb.com/goupee.vn
goupee.vn goupee.com TABLE OF CONTENTS Application Code.................................................................................................................. 417 Templates ............................................................................................................................ 431 Executing the Code.............................................................................................................. 431 Observations........................................................................................................................ 433 Version 2: Relational Database + JSON Fields (Hybrid) ............................................................ 434 Database Design.................................................................................................................. 434 Database Code .................................................................................................................... 437 Application Code.................................................................................................................. 450 Templates ............................................................................................................................ 456 Executing the Code.............................................................................................................. 458 Observations........................................................................................................................ 461 Version 3: Document Store........................................................................................................ 461 Database Design.................................................................................................................. 462 Database Code .................................................................................................................... 463 Application Code.................................................................................................................. 477 Templates ............................................................................................................................ 484 Executing the Code.............................................................................................................. 486 Observations........................................................................................................................ 489 Challenges ................................................................................................................................ 489 Summary................................................................................................................................... 490 Chapter 10: Planning for MySQL 8 and the Document Store �������������������������������� 491 Upgrading from MySQL 5.7 and Earlier..................................................................................... 492 Types of Upgrades ............................................................................................................... 492 Upgrade Practices ............................................................................................................... 496 Reasons for Upgrading ........................................................................................................ 501 Considerations for Upgrading to MySQL 8........................................................................... 502 Migrating to Schemaless Documents ....................................................................................... 504 Normalization vs. Denormalization...................................................................................... 505 Formal Rules vs. Heuristics................................................................................................. 505 x fb.com/goupee.vn
goupee.vn goupee.com TABLE OF CONTENTS Take Storage for Granted..................................................................................................... 507 Embed or Separate?............................................................................................................ 507 Strategies for Migrating to a Document Store..................................................................... 508 Document Store Tips and Tricks ............................................................................................... 522 Summary................................................................................................................................... 524 Index��������������������������������������������������������������������������������������������������������������������� 525 xi fb.com/goupee.vn
goupee.vn goupee.com About the Author Charles Bell conducts research in emerging technologies. He is a member of the Oracle MySQL Development team, and is a senior software developer for the MySQL Enterprise Backup team. He lives in a small town in rural Virginia with his wife. He received his doctor of philosophy in engineering from the Virginia Commonwealth University in 2005. Charles is an expert in the database field and has extensive knowledge and experience in software development and systems engineering. His research interests include 3D printers, microcontrollers, three-dimensional printing, database systems, software engineering, and sensor networks. He spends his limited free time as a practicing maker, focusing on microcontroller projects and refinement of three- dimensional printers. xiii fb.com/goupee.vn
goupee.vn goupee.com About the Technical Reviewer Paulo Jesus is currently a principal software developer at Oracle, in the MySQL Engineering Team. He obtained his PhD in distributed systems in 2012, from the MAP-i doctoral program in computer science by the Universities of Minho, Aveiro, and Porto (Portugal). He also has a MSc degree in mobile systems (2007). His research interests include distributed algorithms, fault tolerance, and mobile systems. xv fb.com/goupee.vn
goupee.vn goupee.com Acknowledgments I thank all the many talented and energetic professionals at Apress. I appreciate the understanding and patience of my acquisition editor, Jonathan Gennick, and coordinating editor, Jill Balzano. They were instrumental in the success of this project. I also thank the army of publishing professionals at Apress for making me look so good in print with a special thank you to the reviewers for their wise council and gentle nudges in the right direction. Thank you all very much! I also am indebted to the technical reviewer for his insight and guidance in making this book the best book on the new MySQL Document Store. Most important, I thank my wife, Annette, for her unending patience and understanding while I spent so much time with my laptop. xvii fb.com/goupee.vn
goupee.vn goupee.com Introduction NoSQL has been given a lot of hype in recent years. As with most new technologies, the underlying principles are rarely truly new, rather, it is the unique combination of known technologies that forms and transforms the whole to become more than the sum of its parts. This is especially true for MySQL 8 and the new MySQL Document Store. Never has MySQL offered so much for so many. Whether you want a traditional relational database solution with a strong foundation or you want the ultimate flexibility to store JSON documents in a document store—or anywhere in between, MySQL can do it. The trick then is learning how to migrate your applications using each of these technologies: whether you use traditional tables with fixed schemas or you have some JSON fields to allow some freedom from rigid structure or you employ the flexibility of a JSON-based document store. This book will give you the knowledge you seek to navigate the MySQL Document Store including how to migrate existing applications and best practices for using a document store solution. Intended Audience I wrote this book to share my passion for the new MySQL Document Store and Python. I especially wanted to show how anyone can write document store solutions without investing in learning a large, complex language and development environment. The intended audience therefore includes anyone interested in learning about the MySQL Document Store such as database administrators, developers, and information technology managers and strategic planners. How This Book Is Structured The book was written to guide the reader from a general knowledge of the new features in MySQL 8 to detailed explanations of the components that make up the MySQL Document Store. The first several chapters cover general topics including a xix fb.com/goupee.vn
goupee.vn goupee.com INTRODUCTION short introduction to MySQL 8, how to install MySQL 8, and how to configure the new document store components. Later chapters present more detailed coverage of the MySQL Document Store components including the MySQL Shell, X DevAPI, X Plugin, and more. Following those chapters is a pair of chapters that present an example application for storing information on books written as a relational database, a hybrid, and a document store. Thus, you can see how to migrate a single application through those variants. The book concludes with some notes about upgrading to MySQL 8 and best practices for using the MySQL Document Store including a recap of the migration process. The following is a brief overview of each chapter included in this book. • Chapter 1, “Introducing MySQL 8—A New Beginning”: This chapter explores some of the highlights of the new MySQL server version 8.0. You will discover those features originally introduced in earlier versions that have been adapted to the new paradigm that is version 8.0, features that are new, and those new features that are truly revolutionary such as the document store, Group Replication, and InnoDB Cluster. • Chapter 2, “Getting Started with MySQL”: This chapter presents a tutorial on MySQL discussing the power of using the MySQL database server in its traditional role using the SQL interface; how to issue commands for creating databases and tables for storing data as well as commands for retrieving that data. Although this chapter presents only a small primer on MySQL, you will learn how to get started with your own installation of MySQL. • Chapter 3, “JSON Documents”: This chapter explores the JSON data type in more detail. You will see examples of how to work with the JSON data in relational tables via the numerous built-in JSON functions provided in MySQL. The JSON data type is key to allowing users to develop hybrid solutions that span the gulf of SQL and NoSQL applications. • Chapter 4, “The MySQL Shell”: This chapter demonstrates how to use the MySQL Shell including a look at the startup options, shell commands, connections, sessions, and we even how to do a bit of interactive scripting in JavaScript and Python. This chapter xx fb.com/goupee.vn
goupee.vn goupee.com INTRODUCTION therefore is the key chapter for learning how to get started with the MySQL Shell and working with JSON and relational data. Although this chapter is not an exhaustive coverage of all the features of the MySQL Shell, it provides a broad tutorial for how to use it for the most common tasks. • Chapter 5, “X Developer API”: This chapter explores the X DevAPI and examines the major classes and methods available for connecting to the MySQL server, creating collections, working with results, and even how to work with relational data. Finally, you will see a set of quick references tables that you can use as the primary reference for developing document store applications. • Chapter 6, “X Plugin”: This chapter discusses the X Plugin and how it works. In particular, you will see how to configure the X Plugin such as changing the port and enabling secure connections via SSL that are separate from the server. The chapter also presents some of the other system variables as well as a lengthy list of status variables that you can use to monitor the X Plugin. • Chapter 7, “X Protocol”: This chapter examines the X Protocol starting with the motivations for why it was created, the chief tenets or goals of the design, and how it was implemented using protobuf as the foundation. You will see a walkthrough of how portions of the X Protocol work for simple use cases. The chapter also presents an example of how to use protobuf in our applications for moving data (messages) around in the code (on disk, over the wire, etc.), which illustrates the power of protobuf. • Chapter 8, “Library Application: User Interface”: This chapter presents a web application library for Python named Flask. You will learn how Flask is built as an extensible framework that is easily augmented with components to make your application more robust. The chapter also presents an introduction to the user interface for the library application built on the foundations of what we learned about Flask. xxi fb.com/goupee.vn
goupee.vn goupee.com INTRODUCTION • Chapter 9, “Library Application: Database Implementations”: This chapter explores the differences between a relational database solution and a relational database solution augmented with JSON fields, and finally a pure document store solution. This chapter demonstrates how to build applications for any of these solutions with complete code that demonstrates many of the tenets of the X DevAPI and the MySQL Document Store. • Chapter 10, “Planning for MySQL 8 and the Document Store”: This chapter presents some strategies for migrating to MySQL 8 including considerations and best practices for migrating applications to use the document store with another example of migrating existing database applications. The chapter concludes with some tips and tricks for working with MySQL 8. How to Use This Book This book is designed to guide you through learning more about MySQL 8, JSON, the MySQL Document Store, discovering the power of X DevAPI, and learning how to migrate existing and building new document store applications. If you are new to MySQL, you should spend some time going through the first four chapters including installing MySQL on your own system and learning how to use the MySQL Shell. If you are familiar with Python and have used the X DevAPI via the MySQL Shell, you may want to read the chapters on the X DevAPI skimming through the examples. On the other hand, if you have not used the X DevAPI or Python, you should attempt to reproduce all the code examples in the chapters. Once you are familiar with MySQL and the X DevAPI from reading the first seven chapters, you can work through Chapters 8 and 9 that present a complete solution that demonstrates how to build a relational database solution, the same solution as a hybrid relational table with JSON columns, and a migration to a full document store solution. Planners may find Chapter 10 especially helpful in planning to upgrade to MySQL 8 and to adapt the MySQL Document Store to your infrastructure. xxii fb.com/goupee.vn
goupee.vn goupee.com INTRODUCTION Downloading the Code The code for the examples shown in this book is available on the Apress web site, www.apress.com. You can find a link on the book’s information page on the Source Code/Downloads tab. This tab is in the Related Titles section of the page. Contacting the Author Should you have any questions or comments—or even spot a mistake you think I should know about—you can contact me at drcharlesbell@gmail.com. xxiii fb.com/goupee.vn
goupee.vn goupee.com CHAPTER 1 Introducing MySQL 8—A New Beginning It is a testament to the dedication of the Oracle MySQL engineers (and Oracle itself) that MySQL continues to improve with new features. The drive within the MySQL engineering division is to continue to develop disruptive database technologies for the Internet. Oracle has not only fostered this aggressiveness but has continued to live up to its promise to invest in and expand their MySQL business. The newest version, MySQL 8, proves conclusively that Oracle has fulfilled the promise to ensure MySQL will remain the world’s most popular open source database system. Previous versions of MySQL have added some new and interesting features since MySQL 5.01 making MySQL a better product. Although the features have been well received and used to solve a lot of problems, the changes were largely evolutionary improvements rather than revolutionary changes. This tendency is not unique to MySQL nor is it unusual in a stable, mature product. That doesn’t mean evolutionary development is bad—it isn’t. However, given that several competitive technologies have emerged, the MySQL engineers realized they must reach higher and further if they are to continue to dominate the industry. Thus, this new release of MySQL breaks many of the molds of previous versions adding new, revolutionary features that change the firmament of how some will use MySQL. Indeed, the version number alone has jumped from 5.x to 8.02 signifying the jump in technological sophistication and the break from continuous development of the 5.x codebase, which lasted for over 13 years. 1MySQL 5.0 was first released (alpha) in December 2003. 2Some would say the change in version number is not only welcome but also long overdue. 1 © Charles Bell 2018 C. Bell, Introducing the MySQL 8 Document Storefb.com/goupee.vn, https://doi.org/10.1007/978-1-4842-2725-1_1
Affichage des 20 premières pages. Téléchargez le document pour le voir en entier.
Aucun commentaire pour le moment. Soyez le premier !
Commentaires (0)
Aucun commentaire pour le moment. Soyez le premier !