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
Table of contents
- Preface
- 1. Introduction to MySQL 8
- 2. Installing and Configuring MySQL Server
-
3. Transactional Data Dictionary and
InnoDB Architecture
- Transactional Data Dictionary
- Exploring the InnoDB Storage Engine
- Optimizing Database Performance with the InnoDB Buffer Pool
- Optimizing Database Performance with the InnoDB Change Buffer
- Optimizing Database Performance with InnoDB Adaptive Hash Index
- Enhancing Database Performance with InnoDB Redo Log Buffer Optimization
- InnoDB On-Disk Structures
- Creating InnoDB Tables
- An Overview of InnoDB Indexes
- InnoDB Tablespaces
- Introduction to InnoDB Redo Log
- InnoDB Locking and Transaction Model
- InnoDB Locking Transaction Model
- Locks Set by Different SQL Statements in InnoDB
- Hot Rows with NOWAIT and SKIP LOCKED
-
InnoDB Configuration Guide
- InnoDB Startup Configuration
- Configuring InnoDB for Read-Only Operation
- Configuring Thread Concurrency for InnoDB
- Configuring the Number of Background InnoDB I/O Threads
- Using Asynchronous I/O on Linux
- Configuring InnoDB I/O Capacity
- Innodb_spin_wait_pause_multiplier
- innodb_lru_scan_depth
- Purge Configuration
- Enabling Automatic Configuration for a Dedicated MySQL Server
- MySQL 8 Persisted System Variables
- Conclusion
- 4. Backup and Recovery
- 5. MySQL Security
-
6. MySQL Replication
- How Does MySQL Replication Work?
-
Setting Up MySQL Replication
- Prerequisites for Setting Up MySQL Replication
- Types of Replication in MySQL
- Configure MySQL Source-Replica Replication
- Configure MySQL Source-Replica using Xtrabackup
- Make a backup on the Source
- Configure Replication using backup from a replica
- Configure MySQL Master-Master Replication
- Configure MySQL Group replication
- Configure MySQL GTID replication
- Configure Replication using Clone Plugin
- Monitoring Replication Status
- Troubleshooting Replication Issues
- Managing Replication Lag
- Tools for Monitoring and Managing MySQL Replication
- Conclusion
-
7. High Availability and Scalability
- Understanding High Availability and Scalability Concepts
- Topology management using Orchestrator
-
Clustering
- Percona XtraDB Cluster (PXC)
- MariaDB Galera Cluster
- InnoDB Cluster
- Understanding the clustering process
- Configuring Percona XtraDB Cluster (PXC)
- Configuration process for setting up a three-node cluster
- Setting Up Load Balancers with ProxySQL Installation
- MySQL Users
- Set up monitoring for MySQL
- Configuring Monitoring in ProxySQL
- Health check for backend servers.
- Validating the ProxySQL configuration and automatic failover.
- Query performance statistics
- Load test using sysbench
-
MySQL 8.0: Sysbench Usage
- Pre-requisites
- Installation of Sysbench
- Using Sysbench to Evaluate MySQL 8.0 Performance
- Sysbench Commands
- Sysbench Tests
- MariaDB Galera Cluster Setup
- Install Required Packages
- Add MariaDB Repository
- Installing MariaDB on All Servers
- Configuring the First Node
- Configuring the Remaining Nodes
- Opening the Firewall on Every Server
- Starting the Cluster
- Install and deploy MySQL 8 InnoDB Cluster with 3 nodes
- Create the MySQL Cluster.
- Install and configure the rest two cluster nodes:
- Setting Up and Executing MySQL Router
- Monitoring cluster status
- Troubleshooting cluster issues
- Conclusion:
-
8. MySQL Performance Tuning
-
Understanding MySQL Performance Tuning Concepts
- Hardware Resources
- CPU Configuration:
- Memory Configuration
- Jemalloc and Transparent Huge Pages (THP)
- Disk I/O Configuration
- Database Design
- Table Structure
- Indexing
- Query Optimization
- Network Latency
- Indexing
- Workload
- pt-query-digest
- Understanding MySQL Query Execution Process
- Tuning MySQL InnoDB Buffer Pool
- Configuring InnoDB Buffer Pool Size
- Monitoring InnoDB Buffer Pool Usage
- Tuning Innodb Thread Concurrency
- Overview of Innodb Thread Concurrency
- Using MySQL Performance Schema
- Conclusion
-
Understanding MySQL Performance Tuning Concepts
-
9. MySQL Monitoring and Management
- Why MySQL monitoring and management are important
- Key performance indicators (KPIs) for MySQL databases
- Monitoring MySQL for performance and security
- The role of monitoring in capacity planning
- Overview of MySQL management tasks
- Installing PMM Server and Client on Docker-Compatible nix Systems
-
MySQL Enterprise Monitor
- Installing and configuring MySQL Enterprise Monitor
- Installing MySQL Enterprise Service Manager
- Installation Log File for MySQL Enterprise Service Manager
- Using MySQL Enterprise Monitor to monitor MySQL instances
- Using MySQL Enterprise Monitor to diagnose performance issues
- Using MySQL Enterprise Monitor to manage MySQL backups
- Using MySQL Enterprise Monitor to manage MySQL upgrades
- Using MySQL Workbench
- MySQL Command Line Tools
- Managing MySQL Logs
- Conclusion
- 10. How to facilitate Major MySQL Upgrades
-
11. MySQL on Cloud: AWS RDS
- Overview of Amazon RDS MySQL architecture
- Benefits of using Amazon RDS MySQL
- Creating an Amazon RDS MySQL Instance
- Choosing the Right Instance Class and Storage Type
- Setting up VPC and Security Groups
- Configuring Advanced Settings: Backup Retention and Maintenance Window
- Connecting to Amazon RDS MySQL using MySQL clients
- Configuring Amazon RDS MySQL for Optimal Performance
-
Configuring automatic backups
- Enabling Automated Backups
- Restoring from automated backups
- Creating a DB Snapshot in Amazon RDS MySQL
- Restoring Backups and Snapshots
- To restore a DB instance from a DB snapshot
- Using the AWS CLI
- Testing and Validating Backups and Restores
- Scaling Amazon RDS MySQL
- Scaling up the storage
- Adding read replicas for improved scalability
- Overview of Amazon RDS read replicas
- Configuring CloudWatch Metrics and Alarms
- Understanding Key CloudWatch Metrics for RDS MySQL
- Creating CloudWatch Alarms
- Enabling Enhanced Monitoring
- Analyzing Database Logs for Troubleshooting
- Accessing the Logs, Events, and Streams for Your DB Instance
- Using Performance Insights to Identify Bottlenecks
- Troubleshooting Network Connectivity Issues
- Cost optimization best practices for Amazon RDS MySQL
- Conclusion:
-
12. MySQL on Cloud: AWS Aurora
- Key features and benefits
- Aurora storage architecture
- Use cases for Aurora MySQL
- Creating an Aurora MySQL DB cluster
- Configuring DB instance settings
- Set up IAM roles:
- Enable automatic backups:
- Set up monitoring:
- 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 Aurora MySQL read replicas
- Implementing caching strategies
- Monitoring and Troubleshooting
- High Availability and Failover Strategies
- Backup, Recovery, and Point-in-Time Restore
- Security and Compliance Best Practices
- Cost Optimization and Resource Management
- Integrating Amazon Aurora MySQL with Other AWS Services
- Conclusion
-
13. MySQL on cloud: Microsoft Azure MySQL
- Getting Started with Azure Database for MySQL
- Securing your MySQL instance
- Managing Read Replicas in Azure Database for MySQL - Flexible Server via Azure Portal
-
Migrations
- Create and Configure a Target Flexible Server
- Create a DMS Instance
- Create a MySQL Migration Project in DMS
- Configure the migration project
- Select source settings
- Select target settings
- Select databases
- Select source databases
- Monitor the migration
- Perform post-migration activities
- Implement Best Practices for Performing a Migration
- Migrate large databases to Azure Database for MySQL using
- Conclusion:
- Wrapping Up
- Index
- About the Author(s)
Product information
- Title: Hands-On MySQL Administration
- Author(s):
- Release date: June 2024
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9781098155896
You might also like
book
MySQL 8 Administrator's Guide
Step by step guide to monitor, manage, and secure your database engine About This Book Your …
book
Learning MySQL, 2nd Edition
Get a comprehensive overview on how to set up and design an effective database with MySQL. …
book
MySQL Crash Course, 2nd Edition
MySQL is one of the most popular database management systems available, powering everything from Internet powerhouses …
book
High Performance MySQL, 4th Edition
How can you realize MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for …