Hands-On MySQL Administration

Hands-On MySQL Administration

by Arunjith Aravindan, Jeyaram Ayyalusamy
Released June 2024
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781098155896

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.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Geared to intermediate- to advanced-level DBAs and IT professionals looking to enhance their MySQL skills, this guide provides a comprehensive overview on how to manage and optimize MySQL databases. You'll learn how to create databases and implement backup and recovery, security configurations, high availability, scaling techniques, and performance tuning.

Using practical techniques, tips, and real-world examples, authors Arunjith Aravindan and Jeyaram Ayyalusamy show you how to deploy and manage MySQL, Amazon RDS, Amazon Aurora, and Azure MySQL. By the end of the book, you'll have the knowledge and skills necessary to administer, manage, and optimize MySQL databases effectively.

  • Design and implement a scalable and reliable database infrastructure using MySQL 8 on premises and cloud
  • Install and configure software, manage user accounts, and optimize database performance
  • Use backup and recovery strategies, security measures, and high availability solutions
  • Apply best practices for database schema design, indexing strategies, and replication techniques
  • Implement advanced database features and techniques such as replication, clustering, load balancing, and high availability
  • Troubleshoot common issues and errors, using diagnostic tools and techniques to identify and resolve problems quickly and efficiently
  • Facilitate major MySQL upgrades including MySQL 5.7 to MySQL 8

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Why We Wrote This Book
    2. Who This Book Is For
    3. By the End of This Book, You Will Understand
    4. How This Book Is Organized
    5. Conventions Used in This Book
    6. O’Reilly Online Learning
    7. How to Contact Us
    8. Acknowledgments
  2. 1. Introduction to MySQL 8
    1. Community Edition
    2. Enterprise Edition
    3. Percona Server for MySQL
    4. MySQL Configuration File
    5. Significant Updates in MySQL 8
    6. Conclusion
  3. 2. Installing and Configuring MySQL Server
    1. Installing MySQL Server on Windows
    2. Installing MySQL Community Edition on Linux
    3. Installing Percona Server for MySQL 8
    4. Configuring MySQL Server
      1. Configuring the Default Character Set and Collation
      2. Configuring the Maximum Allowed Packet Size
      3. What Led to the Removal of the Query Cache Size in MySQL 8?
      4. Configuring MySQL’s Logfiles
      5. Optimizing MySQL Performance
    5. Securing MySQL Server During Installation
      1. Changing the Default Root Password
      2. Restricting Remote Access to MySQL
      3. Creating Separate MySQL Users for Different Applications or Users
    6. Monitoring MySQL
      1. Using the Built-in Performance and Information Schema
      2. Using Third-Party Monitoring Tools Like PMM
      3. Percona Monitoring and Management
    7. Troubleshooting MySQL Server Issues
      1. Connection Errors
      2. Performance Issues
      3. Installing the Percona Toolkit
      4. MySQL Crashes
      5. How to Check System Resources for the MySQL Host
    8. Conclusion
  4. 3. Transactional Data Dictionary and InnoDB Architecture
    1. Transactional Data Dictionary
      1. Storing Information About Database Objects
      2. Understanding the Structure of the Data Directory
    2. The InnoDB Storage Engine
    3. Optimizing Database Performance with InnoDB Memory Structures
      1. The InnoDB Buffer Pool
      2. The InnoDB Change Buffer
      3. The InnoDB Adaptive Hash Index
      4. The InnoDB Redo Log Buffer
    4. InnoDB On-Disk Structures
      1. Tables: Unveiling InnoDB’s Data Storage Foundation
      2. Indexes: Optimizing Queries with InnoDB’s Indexing
      3. Tablespaces: Efficiently Storing Data and Indexes in InnoDB
      4. Doublewrite Buffer: Ensuring Data Consistency in InnoDB
      5. Redo Log: Safeguarding Data Changes
      6. Undo Logs: Ensuring Transactional Consistency
    5. Creating InnoDB Tables
      1. Importing Externally Created Tables into Database
      2. Converting Tables from MyISAM to InnoDB
    6. Persistence of Auto-Increment Counters
    7. An Overview of InnoDB Indexes
      1. Clustered Index
      2. Secondary Indexes
      3. Full-Text Indexes
      4. A Table Without a Primary
      5. Generated Invisible Primary Keys
      6. What About Duplicate, Redundant, and Invisible Indexes?
      7. Identifying Duplicate Indexes in MySQL
      8. What About Unused MySQL Indexes?
    8. InnoDB Tablespaces
      1. Moving InnoDB System Tablespace Files While the Server Is Offline
      2. Disabling Tablespace Path Validation
      3. Optimizing Tablespace Space Allocation on Linux
      4. Configuring Tablespaces with AUTOEXTEND_SIZE
      5. Understanding the InnoDB Doublewrite Buffer
    9. Working with the InnoDB Redo Log
    10. InnoDB Locking and Transaction Model
      1. Transaction Isolation Levels
      2. Locking Reads
      3. Locks Set by Different SQL Statements in InnoDB
    11. Hot Rows with NOWAIT and SKIP LOCKED
      1. innodb_lock_wait_timeout and SELECT FOR UPDATE
    12. Important InnoDB Configuration Variables
      1. Configuring innodb_buffer_pool_size
      2. Configuring innodb_buffer_pool_instances
      3. Configuring Thread Concurrency
      4. Configuring the Number of Background I/O Threads
      5. Using Asynchronous I/O on Linux
      6. Configuring I/O Capacity
      7. innodb_spin_wait_pause_multiplier
      8. innodb_lru_scan_depth
      9. Purge Configuration
      10. Enabling Automatic Configuration for a Dedicated MySQL Server
      11. Configuring Read-Only Operation
    13. MySQL 8 Persisted System Variables
      1. Persist the Global System Variables
      2. Monitor the Variables
    14. Conclusion
  5. 4. Backup and Recovery
    1. Factors to Consider When Choosing a Backup Strategy
    2. Difference Between Physical and Logical Backups
    3. Physical Backups
      1. MySQL Enterprise Backup
      2. Percona XtraBackup
    4. Logical Backups
      1. The MySQL Shell Dump Utility
      2. mysqldump
    5. Point-in-Time Recovery
      1. Instance-Level Recovery
      2. Table-Level Recovery
    6. Management of Binary Logs
      1. Enabling Binary Logging
      2. Configuring Binary Logging
      3. Purging Binary Logs
    7. Best Practices for Backup and Recovery
    8. Encryption to Protect Your Backups
      1. XtraBackup Encryption
      2. How to Decrypt Encrypted Backups
      3. How to Prepare the Decrypted Backups
      4. mysqldump Encryption
    9. Conclusion
  6. 5. MySQL Security
    1. Exploring Types of Security Threats
    2. Understanding the Importance of MySQL Security
    3. Performing Authentication and Authorization in MySQL
      1. Implementing a Password Policy
      2. Using MySQL Authorization
      3. Understanding User Privileges and Permissions
    4. Securing MySQL Communication
      1. SSL/TLS Encryption
      2. Firewall Rules
      3. User Account Locking
    5. Managing MySQL Roles
      1. Creating and Managing MySQL Roles
      2. Assigning Roles to Users
    6. Using InnoDB Data-at-Rest Encryption
      1. Installing Keyring Components for Encryption
      2. Setting the Default Table Encryption
      3. Checking Encryption Status
      4. Performing General Tablespaces Encryption
      5. Encrypting Doublewrites
      6. Encrypting the MySQL System Tablespace
      7. Encrypting Redo Logs
      8. Encrypting Undo Logs
      9. Rotating the Master Key
      10. Safeguarding the Encryption Key
    7. Securing MySQL Replication
      1. Replication Security Best Practices
      2. MySQL Security Auditing
    8. Conclusion
  7. 6. MySQL Replication
    1. Understanding How MySQL Replication Works
    2. Types of Replication in MySQL
      1. Configure MySQL Source-Replica Replication
      2. XtraBackup for Source-Replica Replication
      3. Master-Master Replication
      4. Group Replication
      5. GTID Replication
      6. Replication Using the Clone Plug-in
    3. Monitoring Replication Status
      1. Using MySQL Commands for Monitoring Replication
      2. Using Third-Party Tools for Monitoring Replication
      3. Setting Up Alerts for Replication Status Changes
    4. Troubleshooting Replication Issues
      1. Identifying Common Issues
      2. Using Logs to Troubleshoot
      3. Managing Replication Lag
    5. Conclusion
  8. 7. High Availability and Scalability
    1. Understanding High Availability and Scalability Concepts
    2. Using Orchestrator for Topology Management
      1. Installing Orchestrator
      2. Configuring Orchestrator
      3. Starting Orchestrator and Checking Topology
      4. Relocating Replica Nodes
      5. Performing Graceful Failover
    3. Clustering
      1. Understanding the Clustering Process
      2. Configuring Percona XtraDB Cluster
      3. Configuring Load Balancing with ProxySQL for PXC
      4. Load Testing ProxySQL with Sysbench for PXC
      5. MariaDB Galera Cluster Setup
      6. Install and Deploy MySQL 8 InnoDB Cluster with Three Nodes
    4. Conclusion
  9. 8. MySQL Performance Tuning
    1. Considering Hardware Resources
      1. CPU Configuration
      2. Memory Configuration
      3. Jemalloc and Transparent HugePages
      4. Disk I/O Configuration
    2. Planning the Database Design
      1. Table Structure
      2. Indexing
      3. Query Optimization
    3. Network Latency
      1. Impact of Network Latency on Database Performance
      2. Best Practices for Minimizing Network Latency
    4. Workload
    5. Understanding the MySQL Query Execution Process
    6. Tuning the MySQL InnoDB Buffer Pool
      1. Configuring InnoDB Buffer Pool Size
      2. Monitoring InnoDB Buffer Pool Usage
    7. Tuning InnoDB Thread Concurrency
    8. Using MySQL Performance Schema
      1. Configuring the Performance Schema
      2. Using the Performance Schema to Identify Performance Issues
    9. Conclusion
  10. 9. MySQL Monitoring and Management
    1. Understanding Essential Management Concepts
    2. Percona Monitoring and Management
      1. Quick PMM Server Installation
      2. PMM Client Installation
    3. MySQL Enterprise Monitor
      1. Installing and Configuring MySQL Enterprise Monitor
      2. Installing MySQL Enterprise Service Manager
      3. Accessing the Installation Logfile for MySQL Enterprise Service Manager
      4. Using MySQL Enterprise Monitor
    4. MySQL Workbench
      1. Installation and Configuration of MySQL Workbench
      2. MySQL Workbench Performance Monitoring Tools
      3. Database Creation and Management with MySQL Workbench
      4. Backup and Recovery Solutions with MySQL Workbench
      5. Performance Diagnosis Tools in MySQL Workbench
    5. MySQL Command-Line Tools
    6. Managing MySQL Logs
    7. Conclusion
  11. 10. How to Facilitate Major MySQL Upgrades
    1. The Significance of Upgrading MySQL Major Versions
    2. Server-Side Testing (MySQL Shell Upgrade Checker)
    3. Application-Side Query Testing
      1. Testing Application Queries Using pt-upgrade
      2. Read-only Testing Using pt-upgrade
      3. Read/Write Testing Using pt-upgrade
    4. Production Upgrade Strategy
      1. in-place Upgrade (Async)
      2. Stand Up New Environment and Cutover
    5. Conclusion
  12. 11. MySQL on the Cloud: Amazon RDS
    1. Exploring RDS for MySQL Architecture
    2. Understanding the Benefits of Using RDS for MySQL
    3. Creating an RDS for MySQL Instance
      1. Creating RDS Instance Using the AWS CLI
      2. Choosing the Right Instance Class and Storage Type
      3. Setting Up VPC and Security Groups
      4. Configuring Advanced Settings: Backup Retention and Maintenance Window
    4. Connecting to RDS for MySQL by Using MySQL Clients
      1. Creating Users and Managing Permissions
    5. Configuring RDS for MySQL for Optimal Performance
      1. Configuring Parameter Groups for Optimal Performance
      2. Modifying Parameter Settings
    6. Securing Access to RDS for MySQL
      1. Modifying Security Settings via the AWS Management Console
      2. Updating the KMS Key via the AWS Management Console
    7. Enabling Automatic Backups
    8. Restoring from Automated Backups
    9. Creating a Database Snapshot in RDS for MySQL
    10. Restoring Backups and Snapshots
      1. To Restore a Database Instance from a Database Snapshot
      2. Testing and Validating Backups and Restores
    11. Scaling RDS for MySQL
      1. Scaling Up the Storage
      2. Adding Read Replicas for Improved Scalability
    12. Configuring CloudWatch Metrics and Alarms
      1. Understanding Key CloudWatch Metrics for RDS MySQL
      2. Creating CloudWatch Alarms
      3. Enabling Enhanced Monitoring
    13. Analyzing Database Logs for Troubleshooting
    14. Using Performance Insights to Identify Bottlenecks
    15. Troubleshooting Network Connectivity Issues
    16. Cost Optimization Best Practices for RDS for MySQL
      1. Right-Sizing Database Instances
      2. Optimizing Amazon RDS Database Backup Costs Efficiently
    17. Conclusion
  13. 12. MySQL on the Cloud: Amazon Aurora
    1. Creating an Aurora MySQL DB Cluster
    2. Configuring DB Instance Settings
    3. Connecting to the Aurora MySQL Database
    4. Configuring Performance and Memory Settings
    5. Modifying an Amazon Aurora DB Cluster
    6. Optimizing Queries and Indexes
    7. Using Amazon RDS Performance Insights
    8. Autoscaling Amazon Aurora Read Replicas
    9. Implementing Caching Strategies
    10. Monitoring and Troubleshooting
      1. Monitoring Key Performance Metrics
      2. Setting Up Amazon CloudWatch Alarms
      3. Analyzing Slow Query Logs
      4. Detecting and Resolving Performance Bottlenecks
      5. Troubleshooting Common Issues
    11. High-Availability and Failover Strategies
      1. Understanding Amazon Aurora Replication
      2. Configuring Multi-AZ Deployments
      3. Implementing Aurora Global Database
      4. Testing and Monitoring Failover Scenarios
      5. Designing for Fault Tolerance
    12. Backup, Recovery, and Point-in-Time Restore
      1. Creating and Managing Amazon Aurora Backups
      2. Restoring from Backups and Snapshots
      3. Implementing Point-in-Time Recovery
      4. Best Practices for Backup Retention and Scheduling
      5. Disaster Recovery Planning
    13. Security and Compliance Best Practices
      1. Implementing Encryption at Rest and in Transit
      2. Configuring Network Security and Firewall Rules
      3. Managing Users, Roles, and Permissions
      4. Auditing and Logging Database Activity
    14. Cost Optimization and Resource Management
      1. Choosing the Right Instance Types and Storage Options
      2. Implementing Cost-Saving Strategies
      3. Monitoring and Controlling Resource Usage
      4. Using AWS Cost Explorer and Budgets
    15. Integrating Amazon Aurora with Other AWS Services
      1. Connecting to AWS Lambda for Serverless Computing
      2. Integrating with Amazon S3 for Storage and Data Transfer
      3. Using AWS App Runner for Containerized Applications
      4. Implementing Amazon API Gateway for RESTful APIs
      5. Leveraging Amazon Kinesis for Real-Time Data Streaming
    16. Conclusion
  14. 13. MySQL on the Cloud: Azure Database for MySQL
    1. Getting Started with Azure Database for MySQL
    2. Securing Your MySQL Instance
    3. Using Flexible Server
    4. Enhanced Restore Experience
    5. Managing Read Replicas in Azure Database for MySQL - Flexible Server via Azure Portal
      1. Creating Azure Database for MySQL Server Using Portal and CLI
      2. Launching Azure Cloud Shell
      3. Creating a Server by Using the CLI
    6. Migrations
      1. Azure Database for MySQL Migration Guide
      2. Create and Configure a Target Flexible Server
      3. Create a DMS Instance
      4. Create a MySQL Migration Project in DMS
      5. Configure the Migration Project
      6. Monitor the Migration
      7. Perform Post-Migration Activities
      8. Implement Best Practices for Performing a Migration
      9. Migrate Large Databases to Azure Database for MySQL
    7. Create a Backup
    8. Restore Your Database
    9. Conclusion
  15. Wrapping Up
  16. Index
  17. About the Authors

Product information

  • Title: Hands-On MySQL Administration
  • Author(s): Arunjith Aravindan, Jeyaram Ayyalusamy
  • Release date: June 2024
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781098155896