Advanced MySQL 8

Advanced MySQL 8

by Eric Vanier, Birju Shah, Tejaswi Malepati
Released January 2019
Publisher(s): Packt Publishing
ISBN: 9781788834445

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.

Start your free trial

Book description

Design cost-efficient database solutions, scale enterprise operations and reduce overhead business costs with MySQL

Key Features

  • Explore the new and advanced features of MySQL 8.0
  • Use advanced techniques to optimize MySQL performance
  • Create MySQL-based applications for your enterprise with the help of practical examples

Book Description

Advanced MySQL 8 teaches you to enhance your existing database infrastructure and build various tools to improve your enterprise applications and overall website performance. The book starts with the new and exciting MySQL 8.0 features and how to utilize them for maximum efficiency. As you make your way through the chapters, you will learn to optimize MySQL performance using indexes and advanced data query techniques for large queries. You will also discover MySQL Server 8.0 settings and work with the MySQL data dictionary to boost the performance of your database. In the concluding chapters, you will cover MySQL 8.0 Group Replication, which will enable you to create elastic, highly available, and fault-tolerant replication topologies. You will also explore backup and recovery techniques for your databases and understand important tips and tricks to help your critical data reach its full potential.

By the end of this book, you'll have learned about new MySQL 8.0 security features that allow a database administrator (DBA) to simplify user management and increase the security of their multi-user environments.

What you will learn

  • Explore new and exciting features of MySQL 8.0
  • Analyze and optimize large MySQL queries
  • Understand MySQL Server 8.0 settings
  • Master the deployment of Group Replication and use it in an InnoDB cluster
  • Monitor large distributed databases
  • Discover different types of backups and recovery methods for your databases
  • Explore tips to help your critical data reach its full potential

Who this book is for

Advanced MySQL 8 is for database administrators, data architects, and database developers who want to dive deeper into building advanced database applications in the MySQL environment.

Publisher resources

View/Submit Errata

Table of contents

  1. Title Page
  2. Copyright and Credits
    1. Advanced MySQL 8
  3. About Packt
    1. Why subscribe?
    2. Packt.com
  4. Contributors
    1. About the authors
    2. About the reviewer
    3. Packt is searching for authors like you
  5. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the color images
      2. Conventions used
    4. Get in touch
      1. Reviews
  6. Introduction
    1. Why MySQL 8?
    2. Why is MySQL 8.0 the next generation?
    3. Why it is so important to have a good MySQL architecture design
    4. Summary
  7. MySQL 8's New Features
    1. Global data dictionary
    2. MySQL 8's support roles and history
    3. MySQL 8 supports the creation and management of resource groups and permissions
    4. InnoDB enhancements
    5. JSON enhancements functionalities
    6. Invisible indexes from MySQL optimizer
    7. Features deprecated in MySQL 8.0
    8. A quick look at the features removed from MySQL 8.0
    9. Summary
  8. Indexing Your Data for High Performance
    1. How does MySQL optimizer think in MySQL 8.0?
    2. What kind of data type should I consider indexing first and why?
    3. Why should I have a good index strategy?
    4. What impact does an index have on MySQL performance?
    5. How to display and analyze a table structure
    6. How to efficiently read MySQL query execution plans
      1. How to effectively read the EXPLAIN results
        1. id (JSON name: select_id)
        2. select_type (JSON name: none)
        3. table (JSON name: table_name)
        4. partitions (JSON name: partitions)
        5. type (JSON name: access_type)
      2. EXPLAIN extra information
    7. How to know when to create an index
    8. Multiple column index versus multiple indexes
    9. How to organize your columns in an index for good performance
      1. Case study 1 – how to use the EXPLAIN plan tool in MySQL 8.0
        1. EXPLAIN options
      2. Case study 2 – how to display and analyze a table structure versus the EXPLAIN plan tool
      3. Case study 3 – how to organize your columns in an index efficiently
        1. Creating a missing index
    10. Tips and techniques
      1. The five general rules for your indexes
      2. Tip 2 – the five general rules to optimize your queries
      3. Tip 3 – understand your material resources
      4. Tip 4 – the configuration is not the only thing to take into consideration
      5. Tip 5 – recommendations on the performance of the MySQL architecture
      6. A technical case study
    11. Summary
  9. Advanced Data Techniques for Large Queries
    1. The most important variables are full-scan indicators
    2. Partitioning a table
      1. An overview of partitioning in MySQL 8.0
      2. Available partitioning type
      3. Horizontally partitioning your data
    3. Managing partitions
      1. RANGE partitioning
      2. LIST partitioning
      3. HASH partitioning
      4. KEY partitioning
    4. Using partitions
      1. Partition pruning
    5. Getting rid of unused and duplicate indexes
      1. Unused indexes
      2. Duplicate indexes
      3. Bonus – potentially missing indexes
    6. The most important query optimizations
      1. Optimizing a query with the WHERE clause
      2. Optimizing a query with a GROUP BY clause
      3. Optimizing a query with the ORDER BY clause
    7. Temporary tables
    8. Case study 1 – an example of how to optimize a complex query
    9. Case study 2 – how to optimize sort indexes
    10. Tips and techniques
      1. Partitions
      2. Optimization
      3. Techniques
      4. A typical use case: time series data
      5. Example of a mass DELETE
    11. Summary
  10. MySQL Data Dictionary in MySQL 8.0
    1. MySQL data dictionary structure in MySQL 8.0
    2. Dictionary object cache
    3. Transactional storage of the data dictionary
    4. Applications of the data dictionary
    5. Removal of file-based storage metadata
    6. Serialized Dictionary Information (SDI)
    7. Limitations of the data dictionary
    8. Tips and techniques
    9. Summary
  11. MySQL Server Settings
    1. Getting started with the most significant variables
    2. MySQL server optimization
      1. Control the types of data change operations
      2. Enabling the adaptive hash indexing function
      3. Set a limit on the number of concurrent threads
      4. Controlling the amount of InnoDB preloading
      5. Increasing the number of background threads
      6. Controlling InnoDB input/output performance in the background
      7. Taking advantage of multicore processors
      8. Preventing punctual operations
      9. Configuring the number and size of instances
      10. The InnoDB buffer pool
    3. The thread cache
      1. Case study 1 – when MySQL uses more than 100% of a CPU
        1. How to detect high usage of the MySQL processor
        2. Correcting the use of the MySQL CPU
        3. How to prevent MySQL from using high CPUs
      2. Case study 2 – when MySQL swaps on disk
    4. Tips and techniques
    5. Summary
  12. Group Replication in MySQL 8.0
    1. High availability and requirements
      1. Scaling
    2. Replication
    3. Group replication
      1. Use cases for group replication
        1. Elastic replication
        2. Highly available shards
        3. Alternative to master – slave replication
        4. Autonomic systems
    4. An overview of MySQL's database replication
      1. Asynchronous replication
      2. Semi-synchronous replication
      3. Delayed replication
      4. Global transaction identifier-based replication
      5. Multi-source replication
    5. MySQL's group replication architecture
      1. Group
      2. Writeset
      3. How group communication works
      4. Certification process
        1. Total order delivery
        2. Detecting failure
          1. Network partitioning
        3. Traditional locking versus optimistic locking
          1. Distributed first commit wins rule
          2. Drawbacks of optimistic locking
      5. Modes of group replication
        1. Single primary mode
        2. Multi-primary
      6. Group replication requirements
      7. Configuring the server
      8. Configuring group replication
      9. Monitoring group replication
        1. Replication_group_members
          1. replication_group_member_stats
        2. Replication_connection_status
        3. Replication_applier_status
          1. Server state
    6. Limitations of group replication
    7. Group replication security
      1. IP address whitelist
      2. SSL
      3. VPN
    8. Operations on an online group
      1. Changing the group mode
      2. Tuning recovery
      3. Combining group replication versions
      4. Performance tuning
      5. Message compression
      6. Flow control
    9. Summary
  13. InnoDB Cluster in MySQL 8.0
    1. What is InnoDB cluster?
      1. InnoDB cluster requirements
        1. Installing MySQL Shell
          1. How to use MySQL Shell
      2. Installing an InnoDB cluster
        1. MySQL InnoDB cluster for a sandbox environment
        2. InnoDB cluster in a production environment
        3. Configuring the router configuration
      3. Managing clusters
        1. Getting details of a cluster
        2. Removing instances from a cluster
        3. Adding instances to a cluster
        4. Restoring a cluster after quorum loss
        5. Rebooting a cluster after a major outage
        6. Rescanning a cluster
        7. Checking instance states
        8. Dissolving an InnoDB cluster
      4. InnoDB cluster limitations
    2. Storage engines
      1. Setting a storage engine
        1. MyISAM storage engine
        2. The MEMORY storage engine
        3. The CSV storage engine
        4. The ARCHIVE storage engine
        5. The BLACKHOLE storage engine
        6. The MERGE storage engine
        7. The FEDERATED storage engine
        8. InnoDB engine
    3. Migrating from master-slave replication to MySQL InnoDB cluster
    4. Summary
  14. Monitoring Your Large Distributed Databases
    1. MONyog
      1. Pros
      2. Cons
      3. Conclusion
    2. Datadog
      1. Getting started
      2. Pros
      3. Cons
      4. Conclusion
    3. Navicat
      1. Pros
      2. Cons
      3. Conclusion
    4. Comparison between monitoring tools
      1. The price
      2. Pros
      3. Cons
      4. Top clients
    5. Tips and techniques
    6. Summary
  15. Authentication and Security Management with MySQL 8.0
    1. MySQL 8.0 security features
    2. Privileges provided by MySQL 8.0
    3. Where are privileges stored in MySQL 8.0?
    4. The differences between dynamic and static privileges
    5. Creating roles and users in MySQL 8.0
    6. Displaying assigned roles using SHOW GRANTS
    7. Troubleshooting connection problems
    8. Tips and techniques
      1. Restricting or disabling remote access to the server
      2. Disabling LOCAL INFILE usage
      3. Changing the username and password for root
    9. Summary
  16. Advanced MySQL Performance Tips and Techniques
    1. Tips/best practices
      1. Optimizing your queries for the query cache
      2. EXPLAIN your SELECT queries
      3. LIMIT 1 when getting a unique row
      4. Indexing for search fields
      5. Indexing strategy for Joins
      6. Avoiding SELECT * and COUNT *
      7. Almost always have an ID field
      8. Using ENUM over VARCHAR
      9. Using prepared statements if and when possible
      10. Splitting the big DELETE or INSERT queries
      11. Avoiding the delete trigger
    2. Techniques
      1. Can MySQL perform queries on billions of rows?
      2. Is InnoDB the right choice for multi-billion rows?
      3. How big can a MySQL database get before the performance starts to degrade?
      4. Why MySQL could be slow with large tables
      5. Is MySQL the best solution for handling blobs?
    3. Summary
  17. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think

Product information

  • Title: Advanced MySQL 8
  • Author(s): Eric Vanier, Birju Shah, Tejaswi Malepati
  • Release date: January 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781788834445