Book Overview & Buying
Table Of Contents

Advanced MySQL 8

By : Vanier, Shah, Malepati

2.8 (5)

Buy this Book

Advanced MySQL 8

2.8 (5)

By: Vanier, Shah, Malepati

Buy this Book

Overview of this book

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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Free Chapter

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

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

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 know when to create an index

Multiple column index versus multiple indexes

How to organize your columns in an index for good performance

Tips and techniques

Summary

Advanced Data Techniques for Large Queries

The most important variables are full-scan indicators

Partitioning a table

Managing partitions

Using partitions

Getting rid of unused and duplicate indexes

The most important query optimizations

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

Summary

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

MySQL Server Settings

Getting started with the most significant variables

MySQL server optimization

The thread cache

Tips and techniques

Summary

Group Replication in MySQL 8.0

High availability and requirements

Replication

Group replication

An overview of MySQL's database replication

MySQL's group replication architecture

Limitations of group replication

Group replication security

Operations on an online group

Summary

InnoDB Cluster in MySQL 8.0

What is InnoDB cluster?

Storage engines

Migrating from master-slave replication to MySQL InnoDB cluster

Summary

Monitoring Your Large Distributed Databases

MONyog

Datadog

Navicat

Comparison between monitoring tools

Tips and techniques

Summary

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

Summary

Advanced MySQL Performance Tips and Techniques

Tips/best practices

Techniques

Summary

Other Books You May Enjoy

Leave a review - let other readers know what you think

Authentication and Security Management with MySQL 8.0

In this chapter, we will cover the new MySQL 8.0 security features, such as MySQL roles. This is a new security feature that allows DBAs or seniors developers to simplify user management and increase the security of their multi-user environments. You will also learn about the access privileges system, security plugins, and how they affect the security of your MySQL server and related applications.

We will explore the new security features in MySQL 8.0. Privileges provided by MySQL 8.0 also include the differences between dynamic and static security privileges, and an overview of the grant table, and we will also see how to troubleshoot a connection problem and share some tips and techniques.

We will cover the following topics: