Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

by Vinicius M. Grippa, Sergey Kuzmichev
Released September 2021
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492085874

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Who This Book Is For
    2. How This Book Is Organized
      1. Starting with MySQL
      2. Using MySQL
      3. MySQL in Production
      4. Miscellaneous Topics
    3. Conventions Used in This Book
    4. Using Code Examples
    5. O’Reilly Online Learning
    6. How to Contact Us
    7. Acknowledgments
      1. From Vinicius Grippa
      2. From Sergey Kuzmichev
  2. 1. Installing MySQL
    1. MySQL Forks
      1. MySQL Community Edition
      2. Percona Server for MySQL
      3. MariaDB Server
      4. MySQL Enterprise Edition
    2. Installation Choices and Platforms
      1. 1. Download the Distribution that You Want to Install
      2. 2. Install the Distribution
      3. 3. Perform Any Necessary Post-Installation Setup
      4. 4. Run Benchmarks
    3. Installing MySQL on Linux
      1. Installing MySQL on CentOS 7
      2. Installing MySQL on CentOS 8
      3. Installing MySQL on Ubuntu 20.04 LTS (Focal Fossa)
    4. Installing MySQL on macOS Big Sur
    5. Installing MySQL on Windows 10
    6. The Contents of the MySQL Directory
      1. MySQL 5.7 Default Files
      2. MySQL 8.0 Default Files
    7. Using the Command-Line Interface
    8. Using Docker
      1. Installing Docker
    9. Using Sandboxes
      1. Installing DBdeployer
      2. Using DBdeployer
    10. Upgrading MySQL Server
  3. 2. Modeling and Designing Databases
    1. How Not to Develop a Database
    2. The Database Design Process
    3. The Entity Relationship Model
      1. Representing Entities
      2. Representing Relationships
      3. Partial and Total Participation
      4. Entity or Attribute?
      5. Entity or Relationship?
      6. Intermediate Entities
      7. Weak and Strong Entities
    4. Database Normalization
    5. Normalizing an Example Table
      1. First Normal Form: No Repeating Groups
      2. Second Normal Form: Eliminate Redundant Data
      3. Third Normal Form: Eliminate Data Not Dependent on Key
    6. Entity Relationship Modeling Examples
    7. Using the Entity Relationship Model
      1. Mapping Entities and Relationships to Database Tables
      2. Creating a Bank Database ER Model
      3. Converting the EER to a MySQL Database Using Workbench
  4. 3. Basic SQL
    1. Using the sakila Database
    2. The SELECT Statement and Basic Querying Techniques
      1. Single-Table SELECTs
      2. Choosing Columns
      3. Selecting Rows with the WHERE Clause
      4. The ORDER BY Clause
      5. The LIMIT Clause
      6. Joining Two Tables
    3. The INSERT Statement
      1. INSERT Basics
      2. Alternative Syntaxes
    4. The DELETE Statement
      1. DELETE Basics
      2. Using WHERE, ORDER BY, and LIMIT
      3. Removing All Rows with TRUNCATE
    5. The UPDATE Statement
      1. Examples
      2. Using WHERE, ORDER BY, and LIMIT
    6. Exploring Databases and Tables with SHOW and mysqlshow
  5. 4. Working with Database Structures
    1. Creating and Using Databases
    2. Creating Tables
      1. Basics
      2. Collation and Character Sets
      3. Other Features
      4. Column Types
      5. Keys and Indexes
      6. The AUTO_INCREMENT Feature
    3. Altering Structures
      1. Adding, Removing, and Changing Columns
      2. Adding, Removing, and Changing Indexes
      3. Renaming Tables and Altering Other Structures
    4. Deleting Structures
      1. Dropping Databases
      2. Removing Tables
  6. 5. Advanced Querying
    1. Aliases
      1. Column Aliases
      2. Table Aliases
    2. Aggregating Data
      1. The DISTINCT Clause
      2. The GROUP BY Clause
      3. The HAVING Clause
    3. Advanced Joins
      1. The Inner Join
      2. The Union
      3. The Left and Right Joins
      4. The Natural Join
      5. Constant Expressions in Joins
    4. Nested Queries
      1. Nested Query Basics
      2. The ANY, SOME, ALL, IN, and NOT IN Clauses
      3. The EXISTS and NOT EXISTS Clauses
      4. Nested Queries in the FROM Clause
      5. Nested Queries in JOINs
    5. User Variables
  7. 6. Transactions and Locking
    1. Isolation Levels
      1. REPEATABLE READ
      2. READ COMMITTED
      3. READ UNCOMMITTED
      4. SERIALIZABLE
    2. Locking
      1. Metadata Locks
      2. Row Locks
      3. Deadlocks
    3. MySQL Parameters Related to Isolation and Locks
  8. 7. Doing More with MySQL
    1. Inserting Data Using Queries
    2. Loading Data from Comma-Delimited Files
    3. Writing Data into Comma-Delimited Files
    4. Creating Tables with Queries
    5. Performing Updates and Deletes with Multiple Tables
      1. Deletion
      2. Updates
    6. Replacing Data
    7. The EXPLAIN Statement
    8. Alternative Storage Engines
      1. InnoDB
      2. MyISAM and Aria
      3. MyRocks and TokuDB
      4. Other Table Types
  9. 8. Managing Users and Privileges
    1. Understanding Users and Privileges
    2. The root User
    3. Creating and Using New Users
    4. Grant Tables
    5. User Management Commands and Logging
    6. Modifying and Dropping Users
      1. Modifying a User
      2. Dropping a User
    7. Privileges
      1. Static Versus Dynamic Privileges
      2. The SUPER Privilege
      3. Privilege Management Commands
      4. Checking Privileges
      5. The GRANT OPTION Privilege
    8. Roles
    9. Changing root’s Password and Insecure Startup
    10. Some Ideas for Secure Setup
  10. 9. Using Option Files
    1. Structure of the Option File
    2. Scope of Options
    3. Search Order for Option Files
    4. Special Option Files
      1. Login Path Configuration File
      2. Persistent System Variables Configuration File
    5. Determining the Options in Effect
  11. 10. Backups and Recovery
    1. Physical and Logical Backups
      1. Logical Backups
      2. Physical Backups
      3. Overview of Logical and Physical Backups
    2. Replication as a Backup Tool
      1. Infrastructure Failure
      2. Deployment Bug
    3. The mysqldump Program
      1. Bootstrapping Replication with mysqldump
    4. Loading Data from a SQL Dump File
    5. mysqlpump
    6. mydumper and myloader
    7. Cold Backup and Filesystem Snapshots
    8. Percona XtraBackup
      1. Backing Up and Recovering
      2. Advanced Features
      3. Incremental Backups with XtraBackup
    9. Other Physical Backup Tools
      1. MySQL Enterprise Backup
      2. mariabackup
    10. Point-in-Time Recovery
      1. Technical Background on Binary Logs
      2. Preserving Binary Logs
      3. Identifying a PITR Target
      4. Point-in-Time-Recovery Example: XtraBackup
      5. Point-in-Time-Recovery Example: mysqldump
    11. Exporting and Importing InnoDB Tablespaces
      1. Technical Background
      2. Exporting a Tablespace
      3. Importing a Tablespace
      4. XtraBackup Single-Table Restore
    12. Testing and Verifying Your Backups
    13. Database Backup Strategy Primer
  12. 11. Configuring and Tuning the Server
    1. The MySQL Server Daemon
    2. MySQL Server Variables
      1. Checking Server Settings
      2. Best Practices
  13. 12. Monitoring MySQL Servers
    1. Operating System Metrics
      1. CPU
      2. Disk
      3. Memory
      4. Network
    2. MySQL Server Observability
      1. Status Variables
      2. Basic Monitoring Recipes
      3. The Slow Query Log
      4. InnoDB Engine Status Report
    3. Investigation Methods
      1. The USE Method
      2. RED Method
    4. MySQL Monitoring Tools
    5. Incident/Diagnostic and Manual Data Collection
      1. Gathering System Status Variable Values Periodically
      2. Using pt-stalk to Collect MySQL and OS Metrics
      3. Extended Manual Data Collection
  14. 13. High Availability
    1. Asynchronous Replication
      1. Basic Parameters to Set on the Source and the Replica
      2. Creating a Replica Using PerconaXtraBackup
      3. Creating a Replica Using the Clone Plugin
      4. Creating a Replica Using mysqldump
      5. Creating a Replica Using mydumper and myloader
      6. Group Replication
    2. Synchronous Replication
      1. Galera/PXC Cluster
  15. 14. MySQL in the Cloud
    1. Database-as-a-Service (DBaaS)
      1. Amazon RDS for MySQL/MariaDB
      2. Google Cloud SQL for MySQL
      3. Azure SQL
    2. Amazon Aurora
    3. MySQL Cloud Instances
    4. MySQL in Kubernetes
      1. Deploying Percona XtraDB Cluster in Kubernetes
  16. 15. Load Balancing MySQL
    1. Load Balancing with Application Drivers
    2. ProxySQL Load Balancer
      1. Installing and Configuring ProxySQL
    3. HAProxy Load Balancer
      1. Installing and Configuring HAProxy
    4. MySQL Router
  17. 16. Miscellaneous Topics
    1. MySQL Shell
      1. Installing MySQL Shell
      2. Installing MySQL Shell on Ubuntu 20.04 Focal Fossa
      3. Installing MySQL Shell on CentOS 8
      4. Deploying a Sandbox InnoDB Cluster with MySQL Shell
      5. MySQL Shell Utilities
    2. Flame Graphs
    3. Building MySQL from Source
      1. Building MySQL for Ubuntu Focal Fossa and ARM Processors
    4. Analyzing a MySQL Crash
  18. Index

Product information

  • Title: Learning MySQL, 2nd Edition
  • Author(s): Vinicius M. Grippa, Sergey Kuzmichev
  • Release date: September 2021
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492085874