Learning MySQL, 2nd Edition

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