Hands–On MySQL Administration

Preface
Why We Wrote This Book
Who This Book Is For
By the End of This Book, You Will Understand
How This Book Is Organized
Conventions Used in This Book
O’Reilly Online Learning
How to Contact Us
Acknowledgments

1. Introduction to MySQL 8
Community Edition
Enterprise Edition
Percona Server for MySQL
MySQL Configuration File
Significant Updates in MySQL 8
Conclusion

2. Installing and Configuring MySQL Server
Installing MySQL Server on Windows
Installing MySQL Community Edition on Linux
Installing Percona Server for MySQL 8
Configuring MySQL Server
Configuring the Default Character Set and Collation
Configuring the Maximum Allowed Packet Size
What Led to the Removal of the Query Cache Size in MySQL 8?
Configuring MySQL’s Logfiles
Optimizing MySQL Performance
Securing MySQL Server During Installation
Changing the Default Root Password
Restricting Remote Access to MySQL
Creating Separate MySQL Users for Different Applications or Users
Monitoring MySQL
Using the Built-in Performance and Information Schema
Using Third-Party Monitoring Tools Like PMM
Percona Monitoring and Management
Troubleshooting MySQL Server Issues
Connection Errors
Performance Issues
Installing the Percona Toolkit
MySQL Crashes
How to Check System Resources for the MySQL Host
Conclusion

3. Transactional Data Dictionary and InnoDB Architecture
Transactional Data Dictionary
Storing Information About Database Objects
Understanding the Structure of the Data Directory
The InnoDB Storage Engine
Optimizing Database Performance with InnoDB Memory Structures
The InnoDB Buffer Pool
The InnoDB Change Buffer
The InnoDB Adaptive Hash Index
The InnoDB Redo Log Buffer
InnoDB On-Disk Structures
Tables: Unveiling InnoDB’s Data Storage Foundation
Indexes: Optimizing Queries with InnoDB’s Indexing
Tablespaces: Efficiently Storing Data and Indexes in InnoDB
Doublewrite Buffer: Ensuring Data Consistency in InnoDB
Redo Log: Safeguarding Data Changes
Undo Logs: Ensuring Transactional Consistency
Creating InnoDB Tables
Importing Externally Created Tables into Database
Converting Tables from MyISAM to InnoDB
Persistence of Auto-Increment Counters
An Overview of InnoDB Indexes
Clustered Index
Secondary Indexes
Full-Text Indexes
A Table Without a Primary
Generated Invisible Primary Keys
What About Duplicate, Redundant, and Invisible Indexes?
Identifying Duplicate Indexes in MySQL
What About Unused MySQL Indexes?
InnoDB Tablespaces
Moving InnoDB System Tablespace Files While the Server Is Offline
Disabling Tablespace Path Validation
Optimizing Tablespace Space Allocation on Linux
Configuring Tablespaces with AUTOEXTEND_SIZE
Understanding the InnoDB Doublewrite Buffer
Working with the InnoDB Redo Log
InnoDB Locking and Transaction Model
Transaction Isolation Levels
Locking Reads
Locks Set by Different SQL Statements in InnoDB
Hot Rows with NOWAIT and SKIP LOCKED
innodb_lock_wait_timeout and SELECT FOR UPDATE
Important InnoDB Configuration Variables
Configuring innodb_buffer_pool_size
Configuring innodb_buffer_pool_instances
Configuring Thread Concurrency
Configuring the Number of Background I/O Threads
Using Asynchronous I/O on Linux
Configuring I/O Capacity
innodb_spin_wait_pause_multiplier
innodb_lru_scan_depth
Purge Configuration
Enabling Automatic Configuration for a Dedicated MySQL Server
Configuring Read-Only Operation
MySQL 8 Persisted System Variables
Persist the Global System Variables
Monitor the Variables
Conclusion

4. Backup and Recovery
Factors to Consider When Choosing a Backup Strategy
Difference Between Physical and Logical Backups
Physical Backups
MySQL Enterprise Backup
Percona XtraBackup
Logical Backups
The MySQL Shell Dump Utility
mysqldump
Point-in-Time Recovery
Instance-Level Recovery
Table-Level Recovery
Management of Binary Logs
Enabling Binary Logging
Configuring Binary Logging
Purging Binary Logs
Best Practices for Backup and Recovery
Encryption to Protect Your Backups
XtraBackup Encryption
How to Decrypt Encrypted Backups
How to Prepare the Decrypted Backups
mysqldump Encryption
Conclusion

5. MySQL Security
Exploring Types of Security Threats
Understanding the Importance of MySQL Security
Performing Authentication and Authorization in MySQL
Implementing a Password Policy
Using MySQL Authorization
Understanding User Privileges and Permissions
Securing MySQL Communication
SSL/TLS Encryption
Firewall Rules
User Account Locking
Managing MySQL Roles
Creating and Managing MySQL Roles
Assigning Roles to Users
Using InnoDB Data-at-Rest Encryption
Installing Keyring Components for Encryption
Setting the Default Table Encryption
Checking Encryption Status
Performing General Tablespaces Encryption
Encrypting Doublewrites
Encrypting the MySQL System Tablespace
Encrypting Redo Logs
Encrypting Undo Logs
Rotating the Master Key
Safeguarding the Encryption Key
Securing MySQL Replication
Replication Security Best Practices
MySQL Security Auditing
Conclusion

6. MySQL Replication
Understanding How MySQL Replication Works
Types of Replication in MySQL
Configure MySQL Source-Replica Replication
XtraBackup for Source-Replica Replication
Master-Master Replication
Group Replication
GTID Replication
Replication Using the Clone Plug-in
Monitoring Replication Status
Using MySQL Commands for Monitoring Replication
Using Third-Party Tools for Monitoring Replication
Setting Up Alerts for Replication Status Changes
Troubleshooting Replication Issues
Identifying Common Issues
Using Logs to Troubleshoot
Managing Replication Lag
Conclusion

7. High Availability and Scalability
Understanding High Availability and Scalability Concepts
Using Orchestrator for Topology Management
Installing Orchestrator
Configuring Orchestrator
Starting Orchestrator and Checking Topology
Relocating Replica Nodes
Performing Graceful Failover
Clustering
Understanding the Clustering Process
Configuring Percona XtraDB Cluster
Configuring Load Balancing with ProxySQL for PXC
Load Testing ProxySQL with Sysbench for PXC
MariaDB Galera Cluster Setup
Install and Deploy MySQL 8 InnoDB Cluster with Three Nodes
Conclusion

8. MySQL Performance Tuning
Considering Hardware Resources
CPU Configuration
Memory Configuration
Jemalloc and Transparent HugePages
Disk I/O Configuration
Planning the Database Design
Table Structure
Indexing
Query Optimization
Network Latency
Impact of Network Latency on Database Performance
Best Practices for Minimizing Network Latency
Workload
Understanding the MySQL Query Execution Process
Tuning the MySQL InnoDB Buffer Pool
Configuring InnoDB Buffer Pool Size
Monitoring InnoDB Buffer Pool Usage
Tuning InnoDB Thread Concurrency
Using MySQL Performance Schema
Configuring the Performance Schema
Using the Performance Schema to Identify Performance Issues
Conclusion

9. MySQL Monitoring and Management
Understanding Essential Management Concepts
Percona Monitoring and Management
Quick PMM Server Installation
PMM Client Installation
MySQL Enterprise Monitor
Installing and Configuring MySQL Enterprise Monitor
Installing MySQL Enterprise Service Manager
Accessing the Installation Logfile for MySQL Enterprise Service Manager
Using MySQL Enterprise Monitor
MySQL Workbench
Installation and Configuration of MySQL Workbench
MySQL Workbench Performance Monitoring Tools
Database Creation and Management with MySQL Workbench
Backup and Recovery Solutions with MySQL Workbench
Performance Diagnosis Tools in MySQL Workbench
MySQL Command-Line Tools
Managing MySQL Logs
Conclusion

10. How to Facilitate Major MySQL Upgrades
The Significance of Upgrading MySQL Major Versions
Server-Side Testing (MySQL Shell Upgrade Checker)
Application-Side Query Testing
Testing Application Queries Using pt-upgrade
Read-only Testing Using pt-upgrade
Read/Write Testing Using pt-upgrade
Production Upgrade Strategy
in-place Upgrade (Async)
Stand Up New Environment and Cutover
Conclusion

11. MySQL on the Cloud: Amazon RDS
Exploring RDS for MySQL Architecture
Understanding the Benefits of Using RDS for MySQL
Creating an RDS for MySQL Instance
Creating RDS Instance Using the AWS CLI
Choosing the Right Instance Class and Storage Type
Setting Up VPC and Security Groups
Configuring Advanced Settings: Backup Retention and Maintenance Window
Connecting to RDS for MySQL by Using MySQL Clients
Creating Users and Managing Permissions
Configuring RDS for MySQL for Optimal Performance
Configuring Parameter Groups for Optimal Performance
Modifying Parameter Settings
Securing Access to RDS for MySQL
Modifying Security Settings via the AWS Management Console
Updating the KMS Key via the AWS Management Console
Enabling Automatic Backups
Restoring from Automated Backups
Creating a Database Snapshot in RDS for MySQL
Restoring Backups and Snapshots
To Restore a Database Instance from a Database Snapshot
Testing and Validating Backups and Restores
Scaling RDS for MySQL
Scaling Up the Storage
Adding Read Replicas for Improved Scalability
Configuring CloudWatch Metrics and Alarms
Understanding Key CloudWatch Metrics for RDS MySQL
Creating CloudWatch Alarms
Enabling Enhanced Monitoring
Analyzing Database Logs for Troubleshooting
Using Performance Insights to Identify Bottlenecks
Troubleshooting Network Connectivity Issues
Cost Optimization Best Practices for RDS for MySQL
Right-Sizing Database Instances
Optimizing Amazon RDS Database Backup Costs Efficiently
Conclusion

12. MySQL on the Cloud: Amazon Aurora
Creating an Aurora MySQL DB Cluster
Configuring DB Instance Settings
Connecting to the Aurora MySQL Database
Configuring Performance and Memory Settings
Modifying an Amazon Aurora DB Cluster
Optimizing Queries and Indexes
Using Amazon RDS Performance Insights
Autoscaling Amazon Aurora Read Replicas
Implementing Caching Strategies
Monitoring and Troubleshooting
Monitoring Key Performance Metrics
Setting Up Amazon CloudWatch Alarms
Analyzing Slow Query Logs
Detecting and Resolving Performance Bottlenecks
Troubleshooting Common Issues
High-Availability and Failover Strategies
Understanding Amazon Aurora Replication
Configuring Multi-AZ Deployments
Implementing Aurora Global Database
Testing and Monitoring Failover Scenarios
Designing for Fault Tolerance
Backup, Recovery, and Point-in-Time Restore
Creating and Managing Amazon Aurora Backups
Restoring from Backups and Snapshots
Implementing Point-in-Time Recovery
Best Practices for Backup Retention and Scheduling
Disaster Recovery Planning
Security and Compliance Best Practices
Implementing Encryption at Rest and in Transit
Configuring Network Security and Firewall Rules
Managing Users, Roles, and Permissions
Auditing and Logging Database Activity
Cost Optimization and Resource Management
Choosing the Right Instance Types and Storage Options
Implementing Cost-Saving Strategies
Monitoring and Controlling Resource Usage
Using AWS Cost Explorer and Budgets
Integrating Amazon Aurora with Other AWS Services
Connecting to AWS Lambda for Serverless Computing
Integrating with Amazon S3 for Storage and Data Transfer
Using AWS App Runner for Containerized Applications
Implementing Amazon API Gateway for RESTful APIs
Leveraging Amazon Kinesis for Real-Time Data Streaming
Conclusion

13. MySQL on the Cloud: Azure Database for MySQL
Getting Started with Azure Database for MySQL
Securing Your MySQL Instance
Using Flexible Server
Enhanced Restore Experience
Managing Read Replicas in Azure Database for MySQL - Flexible Server via Azure Portal
Creating Azure Database for MySQL Server Using Portal and CLI
Launching Azure Cloud Shell
Creating a Server by Using the CLI
Migrations
Azure Database for MySQL Migration Guide
Create and Configure a Target Flexible Server
Create a DMS Instance
Create a MySQL Migration Project in DMS
Configure the Migration Project
Monitor the Migration
Perform Post-Migration Activities
Implement Best Practices for Performing a Migration
Migrate Large Databases to Azure Database for MySQL
Create a Backup
Restore Your Database
Conclusion
Wrapping Up

Index
About the Authors