Learning MySQL : get a handle on your data

Get a comprehensive overview on how to set up and design an effective database with MySQL. This thoroughly updated edition covers MySQL's latest version, including its most important aspects. Whether you're deploying an environment, troubleshooting an issue, or engaging in disaster recovery, this practical guide provides the insights and tools necessary to take full advantage of this powerful RDBMS. Authors Vinicius Grippa and Sergey Kuzmichev from Percona show developers and DBAs methods for minimizing costs and maximizing availability and performance. You'll learn how to perform basic and advanced querying, monitoring and troubleshooting, database management and security, backup and recovery, and tuning for improved efficiency. This edition includes new chapters on high availability, load balancing, and using MySQL in the cloud. * Get started with MySQL and learn how to use it in production * Deploy MySQL databases on bare metal, on virtual machines, and in the cloud * Design database infrastructures * Code highly efficient queries * Monitor and troubleshoot MySQL databases * Execute efficient backup and restore operations * Optimize database costs in the cloud * Understand database concepts, especially those pertaining to MySQL Cover 1 Copyright 6 Table of Contents 7 Preface 15 Who This Book Is For 16 How This Book Is Organized 16 Starting with MySQL 16 Using MySQL 16 MySQL in Production 17 Miscellaneous Topics 18 Conventions Used in This Book 18 Using Code Examples 19 O’Reilly Online Learning 20 How to Contact Us 20 Acknowledgments 20 From Vinicius Grippa 20 From Sergey Kuzmichev 21 Chapter 1. Installing MySQL 23 MySQL Forks 24 MySQL Community Edition 24 Percona Server for MySQL 24 MariaDB Server 24 MySQL Enterprise Edition 24 Installation Choices and Platforms 25 1. Download the Distribution that You Want to Install 26 2. Install the Distribution 26 3. Perform Any Necessary Post-Installation Setup 26 4. Run Benchmarks 26 Installing MySQL on Linux 27 Installing MySQL on CentOS 7 27 Installing MySQL on CentOS 8 34 Installing MySQL on Ubuntu 20.04 LTS (Focal Fossa) 41 Installing MySQL on macOS Big Sur 49 Installing MySQL on Windows 10 55 The Contents of the MySQL Directory 62 MySQL 5.7 Default Files 63 MySQL 8.0 Default Files 66 Using the Command-Line Interface 66 Using Docker 67 Installing Docker 67 Using Sandboxes 72 Installing DBdeployer 73 Using DBdeployer 73 Upgrading MySQL Server 76 Chapter 2. Modeling and Designing Databases 81 How Not to Develop a Database 81 The Database Design Process 84 The Entity Relationship Model 84 Representing Entities 85 Representing Relationships 88 Partial and Total Participation 89 Entity or Attribute? 90 Entity or Relationship? 92 Intermediate Entities 93 Weak and Strong Entities 94 Database Normalization 96 Normalizing an Example Table 98 First Normal Form: No Repeating Groups 98 Second Normal Form: Eliminate Redundant Data 98 Third Normal Form: Eliminate Data Not Dependent on Key 99 Entity Relationship Modeling Examples 99 Using the Entity Relationship Model 105 Mapping Entities and Relationships to Database Tables 105 Creating a Bank Database ER Model 106 Converting the EER to a MySQL Database Using Workbench 107 Chapter 3. Basic SQL 111 Using the sakila Database 112 The SELECT Statement and Basic Querying Techniques 114 Single-Table SELECTs 115 Choosing Columns 116 Selecting Rows with the WHERE Clause 118 The ORDER BY Clause 127 The LIMIT Clause 129 Joining Two Tables 131 The INSERT Statement 133 INSERT Basics 133 Alternative Syntaxes 136 The DELETE Statement 139 DELETE Basics 139 Using WHERE, ORDER BY, and LIMIT 140 Removing All Rows with TRUNCATE 141 The UPDATE Statement 142 Examples 142 Using WHERE, ORDER BY, and LIMIT 143 Exploring Databases and Tables with SHOW and mysqlshow 143 Chapter 4. Working with Database Structures 149 Creating and Using Databases 149 Creating Tables 152 Basics 152 Collation and Character Sets 155 Other Features 157 Column Types 160 Keys and Indexes 183 The AUTO_INCREMENT Feature 189 Altering Structures 192 Adding, Removing, and Changing Columns 192 Adding, Removing, and Changing Indexes 196 Renaming Tables and Altering Other Structures 198 Deleting Structures 199 Dropping Databases 199 Removing Tables 200 Chapter 5. Advanced Querying 201 Aliases 201 Column Aliases 202 Table Aliases 204 Aggregating Data 207 The DISTINCT Clause 207 The GROUP BY Clause 209 The HAVING Clause 217 Advanced Joins 219 The Inner Join 219 The Union 222 The Left and Right Joins 228 The Natural Join 233 Constant Expressions in Joins 234 Nested Queries 237 Nested Query Basics 237 The ANY, SOME, ALL, IN, and NOT IN Clauses 240 The EXISTS and NOT EXISTS Clauses 247 Nested Queries in the FROM Clause 253 Nested Queries in JOINs 255 User Variables 256 Chapter 6. Transactions and Locking 261 Isolation Levels 263 REPEATABLE READ 264 READ COMMITTED 265 READ UNCOMMITTED 266 SERIALIZABLE 267 Locking 270 Metadata Locks 271 Row Locks 276 Deadlocks 279 MySQL Parameters Related to Isolation and Locks 281 Chapter 7. Doing More with MySQL 283 Inserting Data Using Queries 283 Loading Data from Comma-Delimited Files 289 Writing Data into Comma-Delimited Files 296 Creating Tables with Queries 299 Performing Updates and Deletes with Multiple Tables 303 Deletion 303 Updates 308 Replacing Data 310 The EXPLAIN Statement 314 Alternative Storage Engines 319 InnoDB 322 MyISAM and Aria 323 MyRocks and TokuDB 324 Other Table Types 326 Chapter 8. Managing Users and Privileges 329 Understanding Users and Privileges 329 The root User 331 Creating and Using New Users 332 Grant Tables 339 User Management Commands and Logging 341 Modifying and Dropping Users 343 Modifying a User 343 Dropping a User 347 Privileges 350 Static Versus Dynamic Privileges 352 The SUPER Privilege 353 Privilege Management Commands 354 Checking Privileges 357 The GRANT OPTION Privilege 359 Roles 363 Changing root’s Password and Insecure Startup 369 Some Ideas for Secure Setup 371 Chapter 9. Using Option Files 373 Structure of the Option File 373 Scope of Options 378 Search Order for Option Files 381 Special Option Files 382 Login Path Configuration File 382 Persistent System Variables Configuration File 384 Determining the Options in Effect 386 Chapter 10. Backups and Recovery 391 Physical and Logical Backups 392 Logical Backups 392 Physical Backups 394 Overview of Logical and Physical Backups 395 Replication as a Backup Tool 396 Infrastructure Failure 397 Deployment Bug 397 The mysqldump Program 398 Bootstrapping Replication with mysqldump 404 Loading Data from a SQL Dump File 404 mysqlpump 405 mydumper and myloader 407 Cold Backup and Filesystem Snapshots 409 Percona XtraBackup 410 Backing Up and Recovering 412 Advanced Features 415 Incremental Backups with XtraBackup 416 Other Physical Backup Tools 418 MySQL Enterprise Backup 418 mariabackup 418 Point-in-Time Recovery 419 Technical Background on Binary Logs 420 Preserving Binary Logs 421 Identifying a PITR Target 421 Point-in-Time-Recovery Example: XtraBackup 423 Point-in-Time-Recovery Example: mysqldump 424 Exporting and Importing InnoDB Tablespaces 424 Technical Background 425 Exporting a Tablespace 425 Importing a Tablespace 427 XtraBackup Single-Table Restore 428 Testing and Verifying Your Backups 429 Database Backup Strategy Primer 431 Chapter 11. Configuring and Tuning the Server 433 The MySQL Server Daemon 433 MySQL Server Variables 434 Checking Server Settings 434 Best Practices 435 Chapter 12. Monitoring MySQL Servers 449 Operating System Metrics 450 CPU 450 Disk 458 Memory 463 Network 468 MySQL Server Observability 472 Status Variables 473 Basic Monitoring Recipes 475 The Slow Query Log 489 InnoDB Engine Status Report 493 Investigation Methods 496 The USE Method 496 RED Method 498 MySQL Monitoring Tools 499 Incident/Diagnostic and Manual Data Collection 504 Gathering System Status Variable Values Periodically 505 Using pt-stalk to Collect MySQL and OS Metrics 506 Extended Manual Data Collection 507 Chapter 13. High Availability 511 Asynchronous Replication 512 Basic Parameters to Set on the Source and the Replica 514 Creating a Replica Using PerconaXtraBackup 515 Creating a Replica Using the Clone Plugin 517 Creating a Replica Using mysqldump 520 Creating a Replica Using mydumper and myloader 521 Group Replication 523 Synchronous Replication 529 Galera/PXC Cluster 531 Chapter 14. MySQL in the Cloud 535 Database-as-a-Service (DBaaS) 535 Amazon RDS for MySQL/MariaDB 536 Google Cloud SQL for MySQL 541 Azure SQL 545 Amazon Aurora 548 MySQL Cloud Instances 549 MySQL in Kubernetes 549 Deploying Percona XtraDB Cluster in Kubernetes 551 Chapter 15. Load Balancing MySQL 557 Load Balancing with Application Drivers 557 ProxySQL Load Balancer 558 Installing and Configuring ProxySQL 560 HAProxy Load Balancer 564 Installing and Configuring HAProxy 565 MySQL Router 569 Chapter 16. Miscellaneous Topics 575 MySQL Shell 575 Installing MySQL Shell 575 Installing MySQL Shell on Ubuntu 20.04 Focal Fossa 575 Installing MySQL Shell on CentOS 8 576 Deploying a Sandbox InnoDB Cluster with MySQL Shell 577 MySQL Shell Utilities 581 Flame Graphs 585 Building MySQL from Source 587 Building MySQL for Ubuntu Focal Fossa and ARM Processors 588 Analyzing a MySQL Crash 592 Index 599 About the Authors 630 Colophon 630 Get a comprehensive overview on how to set up and design an effective database with MySQL. This thoroughly updated edition covers MySQL's latest version, including its most important aspects. Whether you're deploying an environment, troubleshooting an issue, or engaging in disaster and recovery, this practical guide provides the insights and tools necessary to take full advantage of this powerful RDBMS. Authors Vinicius Grippa and Sergey Kuzmichev from Percona show developers and DBAs methods for minimizing costs and maximizing availability and performance. You'll learn how to perform basic and advanced querying, monitoring and troubleshooting, database management and security, backup and recovery, and tuning for improved efficiency. This edition includes new chapters on high availability, load balancing, and using MySQL in the cloud. Learning MySQL will help you: Deploy MySQL databases on bare metal, on virtual machines, and in the cloud Design database infrastructures Code highly efficient queries Monitor and troubleshoot MySQL databases Execute efficient backup and restore operations Optimize database costs in the cloud Understand database concepts, especially those pertaining to MySQL