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
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.

You'll learn how to:

  • Design and implement a scalable and reliable database infrastructure using MySQL 8.0 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 such as 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. Using Code Examples
    7. O’Reilly Online Learning
    8. How to Contact Us
    9. Acknowledgments
      1. Special 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
      1. Downloading Percona MySQL 8 Repository Package
      2. Installing Percona MySQL Repo Locally
      3. Enabling Repo for Percona MySQL 8
      4. Installing MySQL Server
      5. Enabling MySQL Service to Auto-start on Reboot
      6. Starting MySQL Service
      7. Checking Status of MySQL Service
      8. Verification
    4. Configuring MySQL Server
      1. Configuring the MySQL Server’s Default Character Set and Collation
      2. Configuring the MySQL Server’s Maximum Allowed Packet Size
      3. What Led to the Removal of the Query Cache Size in MySQL 8.0?
    5. Configuring the MySQL Server’s Log Files
      1. Basics of Securing MySQL Server During Installation
      2. Changing the Default Root Password
      3. Restricting Remote Access to the MySQL Server
      4. Creating Separate MySQL Users for Different Applications or Users
    6. Monitoring MySQL Server
      1. Using the MySQL Server’s Built-in Performance Schema and Information Schema
      2. Using Third-Party Monitoring Tools Like PMM
    7. Troubleshooting MySQL Server issues
      1. Connection Errors
      2. Performance Issues
      3. Installing a Percona Toolkit
      4. Percona Monitoring Management
      5. MySQL Crashes
      6. How to Check System Resources for MySQL Host
    8. Conclusion
  4. 3. Transactional Data Dictionary and InnoDB Architecture
    1. Transactional Data Dictionary
      1. Storing Information About Database Objects
      2. Directory Structure Overview of MySQL Data Directory
    2. Exploring the InnoDB Storage Engine
    3. Optimizing Database Performance with the InnoDB Buffer Pool
    4. Optimizing Database Performance with the InnoDB Change Buffer
    5. Optimizing Database Performance with InnoDB Adaptive Hash Index
    6. Enhancing Database Performance with InnoDB Redo Log Buffer Optimization
    7. InnoDB On-Disk Structures
      1. Tables: Unveiling InnoDB’s Data Storage Foundation
      2. Indexes: Optimizing Queries with InnoDB’s Indexing
      3. Tablespaces: Efficient Data and Index Storage in InnoDB
      4. Doublewrite Buffer: Ensuring Data Consistency in InnoDB
      5. Redo Log: Safeguarding Data Changes
      6. Undo Logs: Ensuring transactional Consistency
    8. Creating InnoDB Tables
      1. Importing Externally Created Tables into Database
      2. Converting Tables from MyISAM to InnoDB
    9. An Overview of InnoDB Indexes
      1. Clustered index
      2. Generated Invisible Primary Keys
      3. What About Duplicate, Redundant, and Invisible Indexes?
      4. Identifying Duplicate Indexes in MySQL
      5. What About Unused MySQL Indexes?
    10. 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 Tablespace AUTOEXTEND_SIZE
      5. Understanding InnoDB Doublewrite Buffer
    11. Introduction to InnoDB Redo Log
      1. Configuration of InnoDB Redo Log
    12. InnoDB Locking and Transaction Model
    13. InnoDB Locking Transaction Model
      1. Locking Reads
    14. Locks Set by Different SQL Statements in InnoDB
    15. Hot Rows with NOWAIT and SKIP LOCKED
      1. Innodb_lock_wait_timeout and SELECT FOR UPDATE
    16. InnoDB Configuration Guide
      1. InnoDB Startup Configuration
      2. Configuring InnoDB for Read-Only Operation
      3. Configuring Thread Concurrency for InnoDB
      4. Configuring the Number of Background InnoDB I/O Threads
      5. Using Asynchronous I/O on Linux
      6. Configuring InnoDB 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
    17. MySQL 8 Persisted System Variables
      1. Persist the Global System Variables
      2. Monitor the Variables
    18. Conclusion
  5. 4. Backup and Recovery
    1. The Factors to Consider When Choosing a Backup Strategy
    2. Difference Between Logical and Physical Backups
      1. Physical Backups
      2. Restoring a Database
      3. Restoring a Database
      4. How to Prepare Incremental Backups
      5. Logical Backup
      6. Setting Up a Backup Schedule
      7. Selecting Backup Options
      8. Monitoring the Backup Process
      9. Setting Up the Restore Environment
    3. Point-in-Time Recovery
      1. Instance Level Recovery
      2. Table Level Recovery
    4. Managing Binary Logs
      1. Enabling binary logging
      2. Configuring Binary Logging
      3. Purging Binary Logs
      4. Best Practices for Backup and Recovery
      5. Use Encryption to Protect Your Backups
      6. xtrabackup Encryption
      7. How to Decrypt Encrypted Backups
      8. How to Prepare the Decrypted Backups
      9. mysqldump encryption
    5. Conclusion
  6. 5. MySQL Security
    1. Types of Security Threats
    2. Importance of MySQL Security
    3. Authentication and Authorization in MySQL
      1. Creating Secure Passwords
      2. Install the dnf
      3. Install the pwgen
      4. MySQL Authorization
      5. User Privileges and Permissions
      6. MySQL Authentication Plug-ins
    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. What Is InnoDB Data-at-Rest Encryption and How Does It Work?
      1. Installing Keyring Components for Encryption
      2. The Default Table Encryption Setting
      3. Checking Encryption Status
      4. General tablespaces Encryption
      5. Doublewrite File Encryption
      6. MySQL System Tablespace Encryption
      7. Redo Log Encryption
      8. Undo Log Encryption
      9. Master Key Rotation
      10. Safeguarding Encryption Key
    7. Securing MySQL Replication
      1. Securing MySQL Replication
      2. Replication Security Suggestions
      3. MySQL Security Auditing
    8. Conclusion
  7. 6. MySQL Replication
    1. How Does MySQL Replication Work?
    2. Setting Up MySQL Replication
      1. Prerequisites for Setting Up MySQL Replication
      2. Types of Replication in MySQL
      3. Configure MySQL Source-Replica Replication
      4. Configure MySQL Source-Replica using Xtrabackup
      5. Make a backup on the Source
      6. Configure Replication using backup from a replica
      7. Configure MySQL Master-Master Replication
      8. Configure MySQL Group replication
      9. Configure MySQL GTID replication
    3. Configure Replication using Clone Plugin
    4. Monitoring Replication Status
      1. Monitoring replication status using MySQL commands
      2. Using third-party tools for monitoring replication
      3. Setting up alerts for replication status changes
    5. Troubleshooting Replication Issues
      1. Debugging replication issues using MySQL logs
      2. Resolving replication conflicts
    6. Managing Replication Lag
      1. What is replication lag?
      2. Causes of replication lag
      3. Techniques for reducing replication lag
    7. Tools for Monitoring and Managing MySQL Replication
    8. Conclusion
  8. 7. High Availability and Scalability
    1. Understanding High Availability and Scalability Concepts
    2. Topology management using Orchestrator
      1. Installing Orchestrator
      2. Configuring Orchestrator
      3. Starting Orchestrator and Checking Topology
      4. Relocating Replica Nodes
      5. Graceful Failover
    3. Clustering
      1. Percona XtraDB Cluster (PXC)
      2. MariaDB Galera Cluster
      3. InnoDB Cluster
      4. Understanding the clustering process
      5. Configuring Percona XtraDB Cluster (PXC)
      6. Configuration process for setting up a three-node cluster
      7. Setting Up Load Balancers with ProxySQL Installation
      8. MySQL Users
      9. Set up monitoring for MySQL
      10. Configuring Monitoring in ProxySQL
      11. Health check for backend servers.
      12. Validating the ProxySQL configuration and automatic failover.
      13. Query performance statistics
    4. Load test using sysbench
      1. Customizing sysbench scripts
      2. Using ProxySQL with sysbench
    5. MySQL 8.0: Sysbench Usage
      1. Pre-requisites
      2. Installation of Sysbench
      3. Using Sysbench to Evaluate MySQL 8.0 Performance
      4. Sysbench Commands
      5. Sysbench Tests
      6. MariaDB Galera Cluster Setup
      7. Install Required Packages
      8. Add MariaDB Repository
      9. Installing MariaDB on All Servers
      10. Configuring the First Node
      11. Configuring the Remaining Nodes
      12. Opening the Firewall on Every Server
      13. Starting the Cluster
      14. Install and deploy MySQL 8 InnoDB Cluster with 3 nodes
      15. Create the MySQL Cluster.
      16. Install and configure the rest two cluster nodes:
      17. Setting Up and Executing MySQL Router
      18. Monitoring cluster status
      19. Troubleshooting cluster issues
    6. Conclusion:
  9. 8. MySQL Performance Tuning
    1. Understanding MySQL Performance Tuning Concepts
      1. Hardware Resources
      2. CPU Configuration:
      3. Memory Configuration
      4. Jemalloc and Transparent Huge Pages (THP)
      5. Disk I/O Configuration
      6. Database Design
      7. Table Structure
      8. Indexing
      9. Query Optimization
      10. Network Latency
      11. Indexing
      12. Workload
      13. pt-query-digest
      14. Understanding MySQL Query Execution Process
      15. Tuning MySQL InnoDB Buffer Pool
      16. Configuring InnoDB Buffer Pool Size
      17. Monitoring InnoDB Buffer Pool Usage
      18. Tuning Innodb Thread Concurrency
      19. Overview of Innodb Thread Concurrency
    2. Using MySQL Performance Schema
      1. Configuring MySQL Performance Schema
      2. Using MySQL Performance Schema to Identify Performance Issues
    3. Conclusion
  10. 9. MySQL Monitoring and Management
    1. Why MySQL monitoring and management are important
    2. Key performance indicators (KPIs) for MySQL databases
    3. Monitoring MySQL for performance and security
    4. The role of monitoring in capacity planning
    5. Overview of MySQL management tasks
    6. Installing PMM Server and Client on Docker-Compatible nix Systems
      1. Quick Installation PMM Server
      2. Install PMM Client
    7. MySQL Enterprise Monitor
      1. Installing and configuring MySQL Enterprise Monitor
      2. Installing MySQL Enterprise Service Manager
      3. Installation Log File for MySQL Enterprise Service Manager
      4. Using MySQL Enterprise Monitor to monitor MySQL instances
      5. Using MySQL Enterprise Monitor to diagnose performance issues
      6. Using MySQL Enterprise Monitor to manage MySQL backups
      7. Using MySQL Enterprise Monitor to manage MySQL upgrades
    8. Using MySQL Workbench
      1. Installing and configuring MySQL Workbench
      2. MySQL Workbench Performance Monitoring Tools
      3. Database Creation and Management with MySQL Workbench
      4. Efficient Backup and Recovery Solutions with MySQL Workbench
      5. Effective Performance Diagnosis Tools in MySQL Workbench
    9. MySQL Command Line Tools
      1. Basic MySQL command line tools and their uses
    10. Managing MySQL Logs
      1. Types of MySQL logs
      2. Configuring MySQL logs
    11. Conclusion
  11. 10. How to facilitate Major MySQL Upgrades
    1. Understanding the Significance of Upgrading MySQL Major Versions
    2. Server side testing (MySQL Shell Upgrade Checker)
    3. Application side query testing using pt-upgrade tool
      1. Requirements and steps for testing application queries using pt-upgrade
      2. High-level plan of the steps involved in pt-upgrade testing.
      3. Readonly Test using pt-upgrade tool.
      4. Read Write Test using pt-upgrade tool.
    4. Production Upgrade Strategy
      1. In-Place Upgrade (Async)
      2. Stand up New Environment & Cutover
    5. Conclusion
  12. 11. MySQL on Cloud: AWS RDS
    1. Overview of Amazon RDS MySQL architecture
      1. Storage options
      2. Replication
    2. Benefits of using Amazon RDS MySQL
      1. Simplified management
      2. Scalability
      3. Security
      4. Simplified AWS RDS Advantages
    3. Creating an Amazon RDS MySQL Instance
    4. Choosing the Right Instance Class and Storage Type
      1. Instance Classes:
      2. Choosing the right storage type
    5. Setting up VPC and Security Groups
    6. Configuring Advanced Settings: Backup Retention and Maintenance Window
    7. Connecting to Amazon RDS MySQL using MySQL clients
      1. Creating Users and Managing Permissions
    8. Configuring Amazon RDS MySQL for Optimal Performance
      1. Configuring parameter groups for optimal performance
      2. Securing access to Amazon RDS MySQL
      3. Using the AWS Management Console
      4. Using the AWS CLI
      5. Using the AWS Management Console
    9. Configuring automatic backups
      1. Enabling Automated Backups
      2. Restoring from automated backups
      3. Creating a DB Snapshot in Amazon RDS MySQL
      4. Restoring Backups and Snapshots
      5. To restore a DB instance from a DB snapshot
      6. Using the AWS CLI
      7. Testing and Validating Backups and Restores
      8. Scaling Amazon RDS MySQL
      9. Scaling up the storage
      10. Adding read replicas for improved scalability
      11. Overview of Amazon RDS read replicas
      12. Configuring CloudWatch Metrics and Alarms
      13. Understanding Key CloudWatch Metrics for RDS MySQL
      14. Creating CloudWatch Alarms
      15. Enabling Enhanced Monitoring
      16. Analyzing Database Logs for Troubleshooting
      17. Accessing the Logs, Events, and Streams for Your DB Instance
      18. Using Performance Insights to Identify Bottlenecks
      19. Troubleshooting Network Connectivity Issues
    10. Cost optimization best practices for Amazon RDS MySQL
      1. Right-sizing database instances:
      2. Reserved instances
      3. Amazon RDS Reserved Capacity
      4. Amazon RDS database backups efficiently
    11. Conclusion:
  13. 12. MySQL on Cloud: AWS Aurora
    1. Key features and benefits
    2. Aurora storage architecture
    3. Use cases for Aurora MySQL
    4. Creating an Aurora MySQL DB cluster
    5. Configuring DB instance settings
    6. Set up IAM roles:
    7. Enable automatic backups:
    8. Set up monitoring:
    9. Connecting to the Aurora MySQL database
    10. Configuring performance and memory settings
    11. Modifying an Amazon Aurora DB Cluster
    12. Optimizing queries and indexes
    13. Using Amazon RDS Performance Insights
    14. Autoscaling Aurora MySQL read replicas
    15. Implementing caching strategies
    16. 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
    17. High Availability and Failover Strategies
      1. Understanding Aurora MySQL replication
      2. Configuring multi-AZ deployments
      3. Implementing Aurora Global Database
      4. Testing and monitoring failover scenarios
      5. Designing for fault tolerance
    18. Backup, Recovery, and Point-in-Time Restore
      1. Creating and managing Aurora MySQL 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
    19. 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
    20. 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
    21. Integrating Amazon Aurora MySQL with Other AWS Services
      1. Connecting to AWS Lambda for serverless computing
      2. Integrating with Amazon S3 for storage and data transfer
      3. Implementing Amazon API Gateway for RESTful APIs
      4. Leveraging Amazon Kinesis for real-time data streaming
    22. Conclusion
  14. 13. MySQL on cloud: Microsoft Azure MySQL
    1. Getting Started with Azure Database for MySQL
      1. Choosing the right MySQL option
      2. Understanding supported MySQL versions
      3. Creating an Azure free account
    2. Securing your MySQL instance
      1. Flexible Server
    3. Managing Read Replicas in Azure Database for MySQL - Flexible Server via Azure Portal
      1. Launching Cloud Shell
      2. Selecting Your Shell Environment
      3. Registering Your Subscription with Azure Cloud Shell
      4. Creating a server using CLI
    4. Migrations
      1. Create and Configure a Target Flexible Server
      2. Create a DMS Instance
      3. Create a MySQL Migration Project in DMS
      4. Configure the migration project
      5. Select source settings
      6. Select target settings
      7. Select databases
      8. Select source databases
      9. Monitor the migration
      10. Perform post-migration activities
      11. Implement Best Practices for Performing a Migration
    5. Migrate large databases to Azure Database for MySQL using
      1. Prerequisites
      2. Create a backup
      3. Restore your database
    6. Conclusion:
    7. Wrapping Up
  15. Index
  16. About the Author(s)

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