Learning MySQL - Second Edition Get a Handle on Your Data (Vinicius M. Grippa, Sergey Kuzmichev) (Z-Library)

📄 File Format: PDF

💾 File Size: 15.5 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

Vinicius M. Grippa & Sergey Kuzmichev Learning MySQL Get a Handle on Your Data Second Edition

📄 Page 2

(This page has no text content)

📄 Page 3

Praise for Learning MySQL, Second Edition It’s been a long time since a good book covering MySQL and its ecosystem has been published, and many changes have occurred since then. Many topics are clearly covered with examples, from installation and database design to maintenance and architecture for HA and cloud. Many third-party tools are also covered, like dbdeployer and ProxySQL, which are MySQL DBA’s very good friends but are often not covered in the literature. Very nice job from Vinicius and Sergey. Don’t miss the last chapter—it’s very interesting! —Frederic Descamps, MySQL evangelist at Oracle First of all, I want to thank Vinicius and Sergey for making possible my dream book for all beginners of MySQL while I work on the developer edition. This book offers the most comprehensive details on MySQL, not only how to get started but also for complex topics like high availability and load balancing. It’s a smooth read with well-organized content befitting the quality of O’Reilly publishing. I highly recommend this book to all readers, from developers to operations. —Alkin Tezuysal, senior technical manager at PlanetScale This book is a terrific resource, whether you’re installing MySQL for the first time, learning load balancing, or migrating your database to the cloud. I highly recommend it. —Brett Holleman, software engineer This book is essential for anyone who wants to dive into the MySQL ecosystem. With clear and objective communication, it covers topics from basic to advanced. Simply an indispensable book to increase MySQL knowledge. —Diego Hellas, CEO, PerformanceDB

📄 Page 4

Walks the reader through all the important MySQL concepts, from the foundation of SQL and data modeling to advanced topics like high availability and cloud, using clear, concise, direct language. —Charly Batista, Percona

📄 Page 5

Vinicius M. Grippa and Sergey Kuzmichev Learning MySQL Get a Handle on Your Data SECOND EDITION Boston Farnham Sebastopol TokyoBeijing

📄 Page 6

978-1-492-08592-8 [LSI] Learning MySQL by Vinicius M. Grippa and Sergey Kuzmichev Copyright © 2021 Vinicius M. Grippa and Sergey Kuzmichev. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editor: Corbin Collins Production Editor: Beth Kelly Copyeditor: Rachel Head Proofreader: Kim Wimpsett Indexer: Sue Klefstad Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea November 2006: First Edition September 2021: Second Edition Revision History for the Second Edition 2021-09-09: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781492085928 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Learning MySQL, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the authors, and do not represent the publisher’s views. While the publisher and the authors have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights.

📄 Page 7

Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii 1. Installing MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 MySQL Forks 2 MySQL Community Edition 2 Percona Server for MySQL 2 MariaDB Server 2 MySQL Enterprise Edition 2 Installation Choices and Platforms 3 1. Download the Distribution that You Want to Install 4 2. Install the Distribution 4 3. Perform Any Necessary Post-Installation Setup 4 4. Run Benchmarks 4 Installing MySQL on Linux 5 Installing MySQL on CentOS 7 5 Installing MySQL on CentOS 8 12 Installing MySQL on Ubuntu 20.04 LTS (Focal Fossa) 19 Installing MySQL on macOS Big Sur 27 Installing MySQL on Windows 10 33 The Contents of the MySQL Directory 40 MySQL 5.7 Default Files 41 MySQL 8.0 Default Files 44 Using the Command-Line Interface 44 Using Docker 45 Installing Docker 45 Using Sandboxes 50 Installing DBdeployer 51 v

📄 Page 8

Using DBdeployer 51 Upgrading MySQL Server 54 2. Modeling and Designing Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 How Not to Develop a Database 59 The Database Design Process 62 The Entity Relationship Model 62 Representing Entities 63 Representing Relationships 66 Partial and Total Participation 67 Entity or Attribute? 68 Entity or Relationship? 70 Intermediate Entities 71 Weak and Strong Entities 72 Database Normalization 74 Normalizing an Example Table 76 First Normal Form: No Repeating Groups 76 Second Normal Form: Eliminate Redundant Data 76 Third Normal Form: Eliminate Data Not Dependent on Key 77 Entity Relationship Modeling Examples 77 Using the Entity Relationship Model 83 Mapping Entities and Relationships to Database Tables 83 Creating a Bank Database ER Model 84 Converting the EER to a MySQL Database Using Workbench 85 3. Basic SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Using the sakila Database 90 The SELECT Statement and Basic Querying Techniques 92 Single-Table SELECTs 93 Choosing Columns 94 Selecting Rows with the WHERE Clause 96 The ORDER BY Clause 105 The LIMIT Clause 107 Joining Two Tables 109 The INSERT Statement 111 INSERT Basics 111 Alternative Syntaxes 114 The DELETE Statement 117 DELETE Basics 117 Using WHERE, ORDER BY, and LIMIT 118 Removing All Rows with TRUNCATE 119 vi | Table of Contents

📄 Page 9

The UPDATE Statement 120 Examples 120 Using WHERE, ORDER BY, and LIMIT 121 Exploring Databases and Tables with SHOW and mysqlshow 121 4. Working with Database Structures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Creating and Using Databases 127 Creating Tables 130 Basics 130 Collation and Character Sets 133 Other Features 135 Column Types 138 Keys and Indexes 161 The AUTO_INCREMENT Feature 167 Altering Structures 170 Adding, Removing, and Changing Columns 170 Adding, Removing, and Changing Indexes 174 Renaming Tables and Altering Other Structures 176 Deleting Structures 177 Dropping Databases 177 Removing Tables 178 5. Advanced Querying. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Aliases 179 Column Aliases 180 Table Aliases 182 Aggregating Data 185 The DISTINCT Clause 185 The GROUP BY Clause 187 The HAVING Clause 195 Advanced Joins 197 The Inner Join 197 The Union 200 The Left and Right Joins 206 The Natural Join 211 Constant Expressions in Joins 212 Nested Queries 215 Nested Query Basics 215 The ANY, SOME, ALL, IN, and NOT IN Clauses 218 The EXISTS and NOT EXISTS Clauses 225 Nested Queries in the FROM Clause 231 Table of Contents | vii

📄 Page 10

Nested Queries in JOINs 233 User Variables 234 6. Transactions and Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 Isolation Levels 241 REPEATABLE READ 242 READ COMMITTED 243 READ UNCOMMITTED 244 SERIALIZABLE 245 Locking 248 Metadata Locks 249 Row Locks 254 Deadlocks 257 MySQL Parameters Related to Isolation and Locks 259 7. Doing More with MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261 Inserting Data Using Queries 261 Loading Data from Comma-Delimited Files 267 Writing Data into Comma-Delimited Files 274 Creating Tables with Queries 277 Performing Updates and Deletes with Multiple Tables 281 Deletion 281 Updates 286 Replacing Data 288 The EXPLAIN Statement 292 Alternative Storage Engines 297 InnoDB 300 MyISAM and Aria 301 MyRocks and TokuDB 302 Other Table Types 304 8. Managing Users and Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307 Understanding Users and Privileges 307 The root User 309 Creating and Using New Users 310 Grant Tables 317 User Management Commands and Logging 319 Modifying and Dropping Users 321 Modifying a User 321 Dropping a User 325 Privileges 328 viii | Table of Contents

📄 Page 11

Static Versus Dynamic Privileges 330 The SUPER Privilege 331 Privilege Management Commands 332 Checking Privileges 335 The GRANT OPTION Privilege 337 Roles 341 Changing root’s Password and Insecure Startup 347 Some Ideas for Secure Setup 349 9. Using Option Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 Structure of the Option File 351 Scope of Options 356 Search Order for Option Files 359 Special Option Files 360 Login Path Configuration File 360 Persistent System Variables Configuration File 362 Determining the Options in Effect 364 10. Backups and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 Physical and Logical Backups 370 Logical Backups 370 Physical Backups 372 Overview of Logical and Physical Backups 373 Replication as a Backup Tool 374 Infrastructure Failure 375 Deployment Bug 375 The mysqldump Program 376 Bootstrapping Replication with mysqldump 382 Loading Data from a SQL Dump File 382 mysqlpump 383 mydumper and myloader 385 Cold Backup and Filesystem Snapshots 387 Percona XtraBackup 388 Backing Up and Recovering 390 Advanced Features 393 Incremental Backups with XtraBackup 394 Other Physical Backup Tools 396 MySQL Enterprise Backup 396 mariabackup 396 Point-in-Time Recovery 397 Technical Background on Binary Logs 398 Table of Contents | ix

📄 Page 12

Preserving Binary Logs 399 Identifying a PITR Target 399 Point-in-Time-Recovery Example: XtraBackup 401 Point-in-Time-Recovery Example: mysqldump 402 Exporting and Importing InnoDB Tablespaces 402 Technical Background 403 Exporting a Tablespace 403 Importing a Tablespace 405 XtraBackup Single-Table Restore 406 Testing and Verifying Your Backups 407 Database Backup Strategy Primer 409 11. Configuring and Tuning the Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411 The MySQL Server Daemon 411 MySQL Server Variables 412 Checking Server Settings 412 Best Practices 413 12. Monitoring MySQL Servers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 Operating System Metrics 428 CPU 428 Disk 436 Memory 441 Network 446 MySQL Server Observability 450 Status Variables 451 Basic Monitoring Recipes 453 The Slow Query Log 467 InnoDB Engine Status Report 471 Investigation Methods 474 The USE Method 474 RED Method 476 MySQL Monitoring Tools 477 Incident/Diagnostic and Manual Data Collection 482 Gathering System Status Variable Values Periodically 483 Using pt-stalk to Collect MySQL and OS Metrics 484 Extended Manual Data Collection 485 13. High Availability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 489 Asynchronous Replication 490 Basic Parameters to Set on the Source and the Replica 492 x | Table of Contents

📄 Page 13

Creating a Replica Using PerconaXtraBackup 493 Creating a Replica Using the Clone Plugin 495 Creating a Replica Using mysqldump 498 Creating a Replica Using mydumper and myloader 499 Group Replication 501 Synchronous Replication 507 Galera/PXC Cluster 509 14. MySQL in the Cloud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513 Database-as-a-Service (DBaaS) 513 Amazon RDS for MySQL/MariaDB 514 Google Cloud SQL for MySQL 519 Azure SQL 523 Amazon Aurora 526 MySQL Cloud Instances 527 MySQL in Kubernetes 527 Deploying Percona XtraDB Cluster in Kubernetes 529 15. Load Balancing MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535 Load Balancing with Application Drivers 535 ProxySQL Load Balancer 536 Installing and Configuring ProxySQL 538 HAProxy Load Balancer 542 Installing and Configuring HAProxy 543 MySQL Router 547 16. Miscellaneous Topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553 MySQL Shell 553 Installing MySQL Shell 553 Installing MySQL Shell on Ubuntu 20.04 Focal Fossa 553 Installing MySQL Shell on CentOS 8 554 Deploying a Sandbox InnoDB Cluster with MySQL Shell 555 MySQL Shell Utilities 559 Flame Graphs 563 Building MySQL from Source 565 Building MySQL for Ubuntu Focal Fossa and ARM Processors 566 Analyzing a MySQL Crash 570 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 Table of Contents | xi

📄 Page 14

(This page has no text content)

📄 Page 15

Preface Database management systems are part of the core of many companies. Even if a business is not technology-focused, it needs to store, access, and manipulate data in a fast, secure, and reliable way. Because of the COVID-19 pandemic, many areas that had traditionally resisted digital transformation, like the judiciary systems in many countries, are now being integrated through technology due to travel and meeting restrictions, and online shopping and working from home are more popular than ever before. But it’s not just disasters that have propelled such far-reaching changes. With the advent of 5G, we will soon have many more machines connected to the internet than humans. Vast amounts of data are already being harvested, stored, and used to train machine learning models, artificial intelligence, and much more. We are living at the beginning of the next revolution. Several database types have emerged to help with the mission of storing more data— especially unstructured data—including NoSQL databases like MongoDB, Cassandra, and Redis. However, traditional SQL databases remain popular, and there is no sign that they will vanish in the near future. And in the SQL world, undoubtedly the most popular open source solution is MySQL. Both of the authors of this book have worked with many customers from all parts of the world. Along the way, we have learned lots of lessons and experienced a vast number of use cases, ranging from mission-critical monolith applications to simpler microservices applications. This book is full of the tips and advice we think most readers will find helpful for their daily activities. xiii

📄 Page 16

Who This Book Is For This book is primarily for people using MySQL for the first time or learning it as a second database. If you are entering the database arena for the first time, the first chapters will introduce you to the database design concepts and show you how to deploy MySQL into different operating systems and in the cloud. For those coming from another ecosystem, like Postgres, Oracle, or SQL Server, the book covers backup, high availability, and disaster recovery strategies. We hope all readers will also find this book to be a good companion for learning or reviewing fundamentals, from the architecture to advice for the production environment. How This Book Is Organized We introduce many topics, from the basic installation process, database design, back‐ ups, and recovery to CPU performance analysis and bug investigation. We’ve divided the book into four main parts: 1. Starting with MySQL 2. Using MySQL 3. MySQL in Production 4. Miscellaneous Topics Let’s look at how we’ve organized the chapters. Starting with MySQL Chapter 1, Installing MySQL explains how to install and configure the MySQL soft‐ ware on different operating systems. This chapter provides far more detail than most books do. We know that those initiating their career with MySQL are often unfamiliar with various Linux distributions and installation options, and running the MySQL “hello world” requires far more steps than compiling a hello world in any program‐ ming language does. You will see how to set up MySQL on Linux, Windows, macOS, and Docker, and how to deploy instances quickly for testing. Using MySQL Before we dive into creating and using databases, we look at proper database design in Chapter 2, Modeling and Designing Databases. You will learn how to access your database’s features and see how the information items in your database relate to each other. You will see that lousy database designs are challenging to change and can lead to performance problems. We will introduce the concept of strong and weak entities xiv | Preface

📄 Page 17

and their relationships (foreign keys) and explain the process of normalization. This chapter also shows how to download and configure database examples such as sakila, world, and employees. In Chapter 3, Basic SQL, we explore the famous SQL commands that are part of the CRUD (create, read, update, and delete) operations. You will see how to read data from an existing MySQL database, store data in it, and manipulate existing data. In Chapter 4, Working with Database Structures, we explain how to create a new MySQL database and create and modify tables, indexes, and other database struc‐ tures. Chapter 5, Advanced Querying covers more advanced operations such as using nested queries and using different MySQL database engines. This chapter will give you the ability to perform more complex queries. MySQL in Production Now that you know how to install MySQL and manipulate data, the next step is to understand how MySQL handles simultaneous access to the same data. The concepts of isolation, transaction, and deadlocks are explored in Chapter 6, Transactions and Locking. In Chapter 7, Doing More with MySQL, you will see more complex queries that you can perform in MySQL as well as how to observe the query plan to check whether the query is efficient or not. We’ll also explain the different engines available in MySQL (InnoDB and MyISAM are the most famous ones). In Chapter 8, Managing Users and Privileges, you will learn how to create and delete users in the database. This step is one of the most important in terms of security since users with more privileges than they need can cause considerable damage to the data‐ base and the company’s reputation. You will see how to establish security policies, give and remove privileges, and restrict access to specific network IPs. Chapter 9, Using Option Files covers the MySQL configuration files, or option files, which contain all the necessary parameters to start MySQL and optimize its perfor‐ mance. Those familiar with MySQL will recognize the /etc/my.cnf configuration file. You will also see that it is possible to configure user access using special option files. Databases without backup policies are headed for disaster sooner or later. In Chap‐ ter 10, Backups and Recovery we discuss the different types of backups (logical versus physical), the options available to execute this task, and the ones that are more appro‐ priate for large production databases. Chapter 11, Configuring and Tuning the Server discusses the essential parameters you need to pay attention to when setting up a new server. We provide formulas for that Preface | xv

📄 Page 18

and help you to identify whether a parameter value is the correct one for the database workload. Miscellaneous Topics With the essentials established, it is time to go beyond. Chapter 12, Monitoring MySQL Servers teaches you how to monitor your database and collect data from it. Since database workload behavior can change according to the volume of users, transactions, and data being manipulated, identifying which resource is saturated and what is causing the problem is crucial. Chapter 13, High Availability explains how to replicate servers to provide high availa‐ bility. We also introduce the cluster concept, highlighting two solutions: InnoDB Cluster and Galera/PXC Cluster. Chapter 14, MySQL in the Cloud expands the MySQL universe to the cloud. You will learn about the database-as-a-service (DBaaS) option and how to use the managed database services provided by the most prominent three cloud providers: Amazon Web Services (AWS), Google Cloud Platform (GCP), and Microsoft Azure. In Chapter 15, Load Balancing MySQL, we’ll show you the most commonly used tools to distribute queries among different MySQL servers to extract even more perfor‐ mance from MySQL. Finally, Chapter 16, Miscellaneous Topics introduces more advanced analysis methods and tools, and a bit of programming. In this chapter we’ll talk about MySQL Shell, flame graphs, and how to analyze bugs. Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. xvi | Preface

📄 Page 19

This element signifies a tip or suggestion. This element signifies a general note. This element indicates a warning or caution. Using Code Examples Code examples are available for download at https://github.com/learning-mysql-2nd/ learning-mysql-2nd. If you have a technical question or a problem using the code examples, please send email to bookquestions@oreilly.com. This book is here to help you get your job done. In general, if an example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Learning MySQL, 2nd ed., by Vinicius M. Grippa and Sergey Kuzmichev (O’Reilly). Copyright 2021 Vinicius M. Grippa and Sergey Kuzmichev, 978-1-492-08592-8.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. Preface | xvii

📄 Page 20

O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit http://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/learn-mysql-2e. Email bookquestions@oreilly.com to comment on or ask technical questions about this book. For news and information about our books and courses, visit http://oreilly.com. Find us on Facebook: http://facebook.com/oreilly Follow us on Twitter: http://twitter.com/oreillymedia Watch us on YouTube: http://youtube.com/oreillymedia Acknowledgments From Vinicius Grippa Thanks to the following people who helped improve this book: Corbin Collins, Charly Batista, Sami Ahlroos, and Brett Holleman. Without them, this book would not have achieved the excellence we strove for. xviii | Preface