Learning MySQL: Get a Handle on Your Data

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