Hands-On MySQL Administration - Helion

ebook

Autor: Arunjith Aravindan, Jeyaram Ayyalusamy
ISBN: 9781098155858
stron: 608, Format: ebook
Data wydania: 2024-06-28
Księgarnia: Helion

Cena książki: 212,71 zł (poprzednio: 276,25 zł)
Oszczędzasz: 23% (-63,54 zł)

Osoby, które kupiły tę książkę, wybierały także »

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

Osoby które kupowały "Hands-On MySQL Administration", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)
Od hierarchii do turkusu, czyli jak zarządzać w XXI wieku 58,64 zł, (12,90 zł -78%)

Spis treści

Hands-On MySQL Administration eBook -- spis treści

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
- Using Code Examples
- OReilly Online Learning
- How to Contact Us
- Acknowledgments
  - Special Acknowledgments:
    - Arunjith Aravindan:
    - Jeyaram Ayyalusamy:
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
  - Downloading Percona MySQL 8 Repository Package
  - Installing Percona MySQL Repo Locally
  - Enabling Repo for Percona MySQL 8
  - Installing MySQL Server
  - Enabling MySQL Service to Auto-start on Reboot
  - Starting MySQL Service
  - Checking Status of MySQL Service
  - Verification
- Configuring MySQL Server
  - Configuring the MySQL Servers Default Character Set and Collation
  - Configuring the MySQL Servers Maximum Allowed Packet Size
  - What Led to the Removal of the Query Cache Size in MySQL 8.0?
- Configuring the MySQL Servers Log Files
  - Basics of Securing MySQL Server During Installation
  - Changing the Default Root Password
  - Restricting Remote Access to the MySQL Server
  - Creating Separate MySQL Users for Different Applications or Users
- Monitoring MySQL Server
  - Using the MySQL Servers Built-in Performance Schema and Information Schema
  - Using Third-Party Monitoring Tools Like PMM
- Troubleshooting MySQL Server issues
  - Connection Errors
  - Performance Issues
    - Step 1: Monitor server resources
    - Step 2: Monitor query performance
    - Step 3: Optimize query performance
    - Step 4: A third-party tool can be used for gathering data or identifying performance issues on any MySQL server
  - Installing a Percona Toolkit
    - Configure Percona repositories
  - Percona Monitoring Management
  - MySQL Crashes
  - How to Check System Resources for MySQL Host
    - Step 1: Check Disk Space and status
    - Step 2: Check Memory Usage
    - Step 3: Check CPU Usage
    - Step 4: Summarizes the status and configuration of a server
    - Step 5: Restore the backups
- Conclusion
3. Transactional Data Dictionary and InnoDB Architecture
- Transactional Data Dictionary
  - Storing Information About Database Objects
  - Directory Structure Overview of MySQL Data Directory
- 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
  - Tables: Unveiling InnoDBs Data Storage Foundation
  - Indexes: Optimizing Queries with InnoDBs Indexing
  - Tablespaces: Efficient Data and Index Storage 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
- An Overview of InnoDB Indexes
  - Clustered index
  - 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 Tablespace AUTOEXTEND_SIZE
    - Understanding AUTOEXTEND_SIZE
    - Key considerations
    - Example configuration code
  - Understanding InnoDB Doublewrite Buffer
- Introduction to InnoDB Redo Log
  - Configuration of InnoDB Redo Log
- InnoDB Locking and Transaction Model
- InnoDB Locking Transaction Model
  - 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
- 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
  - Persist the Global System Variables
  - Monitor the Variables
- Conclusion
4. Backup and Recovery
- The Factors to Consider When Choosing a Backup Strategy
- Difference Between Logical and Physical Backups
  - Physical Backups
    - MySQL Enterprise Backup
  - Restoring a Database
    - Percona XtraBackup Backup
  - Restoring a Database
    - Incremental backups
  - How to Prepare Incremental Backups
  - Logical Backup
    - MySQL Shell dump utilities
    - mysqldump
  - Setting Up a Backup Schedule
  - Selecting Backup Options
  - Monitoring the Backup Process
  - Setting Up the Restore Environment
- Point-in-Time Recovery
  - Instance Level Recovery
  - Table Level Recovery
- Managing Binary Logs
  - Enabling binary logging
  - Configuring Binary Logging
  - Purging Binary Logs
  - Best Practices for Backup and Recovery
  - Use Encryption to Protect Your Backups
  - xtrabackup Encryption
  - How to Decrypt Encrypted Backups
  - How to Prepare the Decrypted Backups
  - mysqldump encryption
- Conclusion
5. MySQL Security
- Types of Security Threats
- Importance of MySQL Security
- Authentication and Authorization in MySQL
  - Creating Secure Passwords
  - Install the dnf
  - Install the pwgen
  - MySQL Authorization
  - User Privileges and Permissions
  - MySQL Authentication Plug-ins
- Securing MySQL Communication
  - SSL/TLS Encryption
  - Firewall Rules
  - User Account Locking
- Managing MySQL Roles
  - Creating and Managing MySQL Roles
  - Assigning Roles to Users
- What Is InnoDB Data-at-Rest Encryption and How Does It Work?
  - Installing Keyring Components for Encryption
  - The Default Table Encryption Setting
  - Checking Encryption Status
  - General tablespaces Encryption
  - Doublewrite File Encryption
  - MySQL System Tablespace Encryption
  - Redo Log Encryption
  - Undo Log Encryption
  - Master Key Rotation
  - Safeguarding Encryption Key
- Securing MySQL Replication
  - Securing MySQL Replication
  - Replication Security Suggestions
  - MySQL Security Auditing
- Conclusion
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
    - Prerequisites
    - Step 1: Configure the source instance
    - Step 2: Configure the# Replica Instance
    - Step 3: Set up 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 Group Replication Components
    - Start Group Replication
  - Configure MySQL GTID replication
    - GTID replication using Percona XtraBackup
- Configure Replication using Clone Plugin
  - The installation process for the Clone Plugin:
- Monitoring Replication Status
  - Monitoring replication status using MySQL commands
  - Using third-party tools for monitoring replication
  - Setting up alerts for replication status changes
- Troubleshooting Replication Issues
  - Debugging replication issues using MySQL logs
  - Resolving replication conflicts
- Managing Replication Lag
  - What is replication lag?
  - Causes of replication lag
  - Techniques for reducing 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
  - Installing Orchestrator
  - Configuring Orchestrator
  - Starting Orchestrator and Checking Topology
  - Relocating Replica Nodes
  - Graceful Failover
- Clustering
  - Percona XtraDB Cluster (PXC)
  - MariaDB Galera Cluster
  - InnoDB Cluster
  - Understanding the clustering process
  - Configuring Percona XtraDB Cluster (PXC)
    - Hosts and Firewall Configuration for the nodes
  - Configuration process for setting up a three-node cluster
    - Bootstrapping the first node in Percona XtraDB Cluster.
    - Additional supporting considerations.
  - Setting Up Load Balancers with ProxySQL Installation
    - Configuring the ProxySQL Administrator Password
    - Adding database nodes as backend servers in ProxySQL server list:
  - 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
  - Customizing sysbench scripts
  - Using ProxySQL with 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
    - Explanation of Configuration Options
  - Configuring the Remaining Nodes
  - Opening the Firewall on Every Server
  - Starting the Cluster
  - Install and deploy MySQL 8 InnoDB Cluster with 3 nodes
    - SELinux Configuration
    - Firewall Configuration
    - Setup MySQL access for linux user
  - Create the MySQL Cluster.
  - Install and configure the rest two cluster nodes:
    - Install the MySQL shell
    - Set up the hosts file.
    - Set up the firewall.
    - Optimize the MySQL configuration:
    - Establish the root password:
    - Configure the MySQL instance
    - Add the nodes to the cluster
  - 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
    - Example Configuration Code
    - Example Configuration Code:
  - Network Latency
    - Impact of Network Latency on Database Performance
    - Best Practices for Minimizing 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
  - Configuring MySQL Performance Schema
  - Using MySQL Performance Schema to Identify Performance Issues
- Conclusion
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
  - Quick Installation PMM Server
  - Install PMM Client
- MySQL Enterprise Monitor
  - Installing and configuring MySQL Enterprise Monitor
  - Installing MySQL Enterprise Service Manager
    - Minimum MySQL Version Requirements for External Repositories
  - 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
  - Installing and configuring MySQL Workbench
  - MySQL Workbench Performance Monitoring Tools
  - Database Creation and Management with MySQL Workbench
  - Efficient Backup and Recovery Solutions with MySQL Workbench
  - Effective Performance Diagnosis Tools in MySQL Workbench
- MySQL Command Line Tools
  - Basic MySQL command line tools and their uses
- Managing MySQL Logs
  - Types of MySQL logs
  - Configuring MySQL logs
- Conclusion
10. How to facilitate Major MySQL Upgrades
- Understanding the Significance of Upgrading MySQL Major Versions
- Server side testing (MySQL Shell Upgrade Checker)
- Application side query testing using pt-upgrade tool
  - Requirements and steps for testing application queries using pt-upgrade
  - High-level plan of the steps involved in pt-upgrade testing.
  - Readonly Test using pt-upgrade tool.
  - Read Write Test using pt-upgrade tool.
- Production Upgrade Strategy
  - In-Place Upgrade (Async)
  - Stand up New Environment & Cutover
- Conclusion
11. MySQL on Cloud: AWS RDS
- Overview of Amazon RDS MySQL architecture
  - Storage options
  - Replication
- Benefits of using Amazon RDS MySQL
  - Simplified management
  - Scalability
  - Security
  - Simplified AWS RDS Advantages
- Creating an Amazon RDS MySQL Instance
- Choosing the Right Instance Class and Storage Type
  - Instance Classes:
  - Choosing the right storage type
- Setting up VPC and Security Groups
- Configuring Advanced Settings: Backup Retention and Maintenance Window
- Connecting to Amazon RDS MySQL using MySQL clients
  - Creating Users and Managing Permissions
    - Enabling and Disabling IAM Database Authentication for Amazon RDS MySQL
    - Enabling IAM Database Authentication for an Existing DB Instance
    - Using IAM Authentication with MySQL
- Configuring Amazon RDS MySQL for Optimal Performance
  - Configuring parameter groups for optimal performance
    - Understanding Parameter Groups
    - Creating Custom Parameter Groups
  - Securing access to Amazon RDS MySQL
  - Using the AWS Management Console
  - Using the AWS CLI
  - Using the AWS Management Console
    - Using the AWS CLI
    - Backup and Recovery in Amazon RDS MySQL
- Configuring automatic backups
  - Enabling Automated Backups
  - Restoring from automated backups
    - Using the AWS CLI
  - Creating a DB Snapshot in Amazon RDS MySQL
  - Restoring Backups and Snapshots
    - Parameter group considerations
    - Security group considerations
  - To restore a DB instance from a DB snapshot
  - Using the AWS CLI
  - Testing and Validating Backups and Restores
    - Using the AWS Management Console
  - Scaling Amazon RDS MySQL
    - Scaling up and down instance size and storage
  - Scaling up the storage
    - Using the AWS Management Console
    - Using the AWS CLI
  - 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
  - Right-sizing database instances:
  - Reserved instances
  - Amazon RDS Reserved Capacity
  - Amazon RDS database backups efficiently
- 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
  - 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 Aurora MySQL 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 Aurora MySQL 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 MySQL with Other AWS Services
  - Connecting to AWS Lambda for serverless computing
  - Integrating with Amazon S3 for storage and data transfer
  - Implementing Amazon API Gateway for RESTful APIs
  - Leveraging Amazon Kinesis for real-time data streaming
- Conclusion
13. MySQL on cloud: Microsoft Azure MySQL
- Getting Started with Azure Database for MySQL
  - Choosing the right MySQL option
  - Understanding supported MySQL versions
  - Creating an Azure free account
    - Connecting to your MySQL instance
- Securing your MySQL instance
  - Flexible Server
- Managing Read Replicas in Azure Database for MySQL - Flexible Server via Azure Portal
  - Configuring Azure Cloud Shell
  - Launching Cloud Shell
    - Creating an Azure Storage Account
  - Selecting Your Shell Environment
  - Registering Your Subscription with Azure Cloud Shell
    - Setting Output Language to English
  - Creating a server using CLI
- 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
    - Select tables:
  - Monitor the migration
  - Perform post-migration activities
  - Implement Best Practices for Performing a Migration
- Migrate large databases to Azure Database for MySQL using
  - Prerequisites
  - Create a backup
  - Restore your database
- Conclusion:
- Wrapping Up
Index