Advanced MySQL 8: Discover the full potential of MySQL and ensure high performance of your database

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