Introducing the MySQL 8 Document Store (eBook)

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 loving wife. He received his Doctor of Philosophy in Engineering from 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.   

Table of Contents 5
About the Author 12
About the Technical Reviewer 13
Acknowledgments 14
Introduction 15
Chapter 1: Introducing MySQL 8—A New Beginning 20
Old Features New Again 22
JSON Data Type 23
MySQL Shell 27
X Plugin, X Protocol, and X DevAPI 31
InnoDB Improvements 33
New Features 35
Data Dictionary 36
Account Management 37
Removed Options, Variables, and Features 38
Paradigm Shifting Features 39
Group Replication 40
InnoDB Cluster 44
Summary 53
Chapter 2: Getting Started with MySQL 55
Getting to Know MySQL 55
How Do I Connect to MySQL? 57
How to Get and Install MySQL 60
Configuring and Managing Access to MySQL 66
Configuration Files 66
Creating Users and Granting Access 69
Configuring the Document Store 70
A MySQL Primer 72
Creating Databases and Tables 72
Searching for Data 75
Creating Data 79
Updating Data 80
Deleting Data 81
Using Indexes 82
Views 83
Triggers 83
Simple Joins 84
Stored Routines 87
Summary 88
Chapter 3: JSON Documents 90
Concepts and Technologies: Jargon Explained 91
Origins: Key, Value Mechanisms 91
JSON 93
Application Programming Interface 93
NoSQL Interface 94
Document Store 95
Introducing JSON Documents 96
JSON Format Rules 96
Using JSON in MySQL 98
Path Expressions 103
JSON Functions 111
Creating JSON Data 113
Modifying JSON Data 119
Searching JSON Data 122
Utility Functions 132
Combining SQL and JSON - Indexing JSON Data 142
Summary 150
Chapter 4: The MySQL Shell 151
Getting Started 152
Features 153
Shell Commands 155
Options 157
Sessions and Modes 160
Session Objects 160
Modes Supported 162
Connections 162
Using a URI 163
Using Individual Options 164
Using Connections in Scripts 165
Using SSL Connections 166
Set Up and Install 166
Install the MySQL Shell 167
Setup the X Plugin 171
Enable the X Plugin Using the MySQL Shell 172
Enable the X Plugin Using the MySQL Client 172
Tutorial: MySQL Shell by Example 174
Installing the Sample Database 175
SQL 178
JavaScript 185
Python 188
Summary 190
Chapter 5: X Developer API 191
Overview 192
Clients 193
Target Language Conformity 194
MySQL X Module 196
Classes and Methods 197
Session Class 201
Schema Methods 201
Transaction Methods 203
Connection Methods 205
Miscellaneous Methods 206
CRUD Operations 207
Schema Class 207
Collection Class 211
Table Class 224
Example Data Used in this Chapter 235
Working with Data Sets 236
Document Identifiers 242
Auto Increment 244
Accessing Data in Data Sets 244
Accessing Metadata in Results 246
Column Metadata 248
Expressions 249
Expression Strings 250
Boolean Expression Strings 251
Warnings and Errors 252
Warnings from the Server 252
Errors from the X DevAPI 254
Additional Features 260
Parameter Binding 260
Method Chaining 264
CRUD Prepared Statements 265
Asynchronous Execution 267
For More Information 268
Summary 269
Chapter 6: X Plugin 270
Overview 270
Features 271
Secure Socket Layer (SSL) Connections 272
Changing the Default Port 274
Going Deeper—Journey into the Source Code 275
Options and Variables 278
How to View Values of Variables 278
How to Set Values of Variables 281
System Variables and Startup Options 284
Status Variables 286
Monitoring the X Plugin 286
Communication 288
Connections 289
CRUD Operations 290
Errors and Warnings 291
Sessions 292
SSL 293
Statements 295
Worker Threads 296
Summary 297
Chapter 7: X Protocol 299
Overview 299
Goals for the X Protocol 302
Extensibility 303
Security 303
Performance 304
X Protocol and Protocol Buffers 304
Installing the Protobuf Compiler 306
Protobuf Example 307
X Protocol: Under the Hood 313
Protobuf Implementation 313
X Protocol Examples 318
Example 1: Authentication 318
Example 2: Simple Inserts 320
X Protocol Walkthrough 323
Example 1: Authentication 323
Example 2: Simple Inserts 326
Creating X Clients 329
Setup for the Examples 331
Document Store Example 333
Relational Data Example 339
Summary 343
Chapter 8: Library Application: User Interface 345
Getting Started 346
Library Application 346
Setup Your Environment 347
Installing Flask 349
Installing Flask-Script 350
Installing Flask-Bootstrap 351
Installing Flask-WTF 352
Installing WTForms 352
Flask Primer 355
Terminology 357
Initialization and the Application Instance 359
Flask-Script 359
Flask-Bootstrap 362
WTForms 363
Flask-WTF 363
Form Classes 363
CSRF Protection 367
Message Flashing 367
HTML Files and Templates 368
Jinja2 Templates Overview 369
Template Language Constructs 374
Comments 374
Include 374
Macros 375
Import 375
Extend (Inherit) 376
Blocks 376
Loops 376
Conditionals 377
Variables and Variable Filters 378
HTML Files Using Templates 379
Error Handlers 382
Not Found (404) Errors 383
Application (500) Errors 383
Redirects 384
Additional Features 385
Flask Review: Sample Application 386
Library Application User Interface Design 388
Preparing the Directory Structure 389
User Interface Features 390
Form Classes 394
Author Form Class 394
Publisher Form Class 395
Book Form Class 395
List Form Class 398
View Functions 398
Templates 401
Author Template 402
Publisher Template 403
Book Template 404
List Template 406
Other Templates 407
Application Code 407
Summary 411
Chapter 9: Library Application: Database Implementations 412
Version 1: Relational Database 413
Database Design 413
Database Code 417
SQL Strings 417
Author Class 419
Publisher Class 421
Book Class 423
Library Class 427
Application Code 430
Setup and Initialization 430
List View Function 431
Author View Function 433
Publisher View Function 437
Book View Function 439
Templates 444
Executing the Code 444
Observations 446
Version 2: Relational Database + JSON Fields (Hybrid) 447
Database Design 447
Database Code 450
Code Deleted 451
SQL Strings 451
Library Class 453
Publisher Class 456
Book Class 458
Application Code 463
Setup and Initialization 464
Form Classes 464
View Functions 465
Templates 469
Executing the Code 471
Observations 474
Version 3: Document Store 474
Database Design 475
Database Code 476
Code Deleted 477
Setup and Initialization 477
Books Class 478
Class Declaration 478
Create Function 479
Read Function 480
Update Function 480
Delete Function 482
Utility Functions 482
Completed Code 484
Application Code 490
Setup and Initialization 491
Form Classes 491
Book View Function 493
Templates 497
Executing the Code 499
Observations 502
Challenges 502
Summary 503
Chapter 10: Planning for MySQL 8 and the Document Store 504
Upgrading from MySQL 5.7 and Earlier 505
Types of Upgrades 505
Revision Upgrade 506
Minor Upgrade 507
Major Upgrade 507
Incompatible Upgrades 507
Upgrade Practices 509
Check Prerequisites 509
Plan the Upgrade 511
Consider Parallel Deployment 512
Test, Test, Test! 512
Production Deployment Strategies 513
Reasons for Upgrading 514
Considerations for Upgrading to MySQL 8 515
Migrating to Schemaless Documents 517
Normalization vs. Denormalization 518
Formal Rules vs. Heuristics 518
View Data as Code 520
Take Storage for Granted 520
Embed or Separate? 520
Strategies for Migrating to a Document Store 521
Migrating to a Hybrid Solution 526
Converting to a Document Store 531
Document Store Tips and Tricks 535
Summary 537
Index 538