Book Image

Advanced MySQL 8

By : Eric Vanier, Birju Shah, Tejaswi Malepati

Book Image

Advanced MySQL 8

By: Eric Vanier, Birju Shah, Tejaswi Malepati

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

Free Chapter

Introduction

Why is MySQL 8.0 the next generation?

Why it is so important to have a good MySQL architecture design

MySQL 8's New Features

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

Indexing Your Data for High Performance

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

Advanced Data Techniques for Large Queries

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

MySQL Data Dictionary in MySQL 8.0

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

MySQL Server Settings

MySQL Server Settings

Getting started with the most significant variables

MySQL server optimization

The thread cache

Tips and techniques

Group Replication in MySQL 8.0

Group Replication in MySQL 8.0

High availability and requirements

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

InnoDB Cluster in MySQL 8.0

InnoDB Cluster in MySQL 8.0

What is InnoDB cluster?

Storage engines

Migrating from master-slave replication to MySQL InnoDB cluster

Monitoring Your Large Distributed Databases

Monitoring Your Large Distributed Databases

Comparison between monitoring tools

Tips and techniques

Authentication and Security Management with MySQL 8.0

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

Advanced MySQL Performance Tips and Techniques

Advanced MySQL Performance Tips and Techniques

Tips/best practices

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Limitations of group replication

The following is a list of limitations for group replication:

Group replication works on top of a GTID-based replication, and it depends on transactions. So, we need tables with the InnoDB storage engine, which has transaction support.
CREATE TABLE...SELECT statements are not allowed, because the binlog_format maybe different on the master and slave. If the format is set to STATEMENT, the binary log will have one transaction with one GTID. If the format is set to ROW, the CREATE_TABLE..SELECT statement would result as two transactions with two GTIDs.
When the binary log format is set to STATEMENT, the creation of temporary tables and dropping temporary tables cannot be used inside transactions, functions, or triggers when we use GTID-based replication with auto commit set to 1. From MySQL 8.0.13, if the binary log format is set to ROW or MIXED...