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.

Related Content you might be interested in

Current Title:

Small book image
Advanced MySQL 8
Eric Vanier | Birju Shah | Tejaswi Malepati
Jan, 2019 | 286 pages
Small book image
MySQL 8 Administrator's Guide
Hetal Oza | Chintan Mehta | Ankit K Bhavsar | Subhash Shah
Feb, 2018 | 510 pages
Small book image
MySQL 8 for Big Data
Chintan Mehta | Shabbir Challawala | Jaydip Lakhatariya | Kandarp Patel
Oct, 2017 | 296 pages
Small book image
MySQL 8 Cookbook
Karthik Appigatla
Jan, 2018 | 446 pages
Table of Contents (13 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
Free Chapter
1
Introduction
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
2
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
Summary
3
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
Summary
4
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
Summary
5
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
Summary
6
MySQL Server Settings
MySQL Server Settings
Getting started with the most significant variables
MySQL server optimization
The thread cache
Tips and techniques
Summary
7
Group Replication in MySQL 8.0
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
8
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
Summary
9
Monitoring Your Large Distributed Databases
Monitoring Your Large Distributed Databases
MONyog
Datadog
Navicat
Comparison between monitoring tools
Tips and techniques
Summary
10
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
Summary
11
Advanced MySQL Performance Tips and Techniques
Advanced MySQL Performance Tips and Techniques
Tips/best practices
Techniques
Summary
12
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Partitioning a table

First, let's define what partitioning a table is. Partitioning a table in MySQL is how MySQL divides its actual data into separate tables, but it is always treated as a single table by the SQL layer.

An overview of partitioning in MySQL 8.0

When partitioning, it is critical to find a partition key. It is important to make sure that the searches we are going to do in a table go to the correct partition or group of partitions. This means that all commands, for example SELECT, UPDATE, DELETE, must include this column in the WHERE clause to ensure efficiency in the use of these partitions.

Generally, the best practice is to add the partition key to the primary key with auto incrementation enabled, that...

Previous Section
End of Section 3
Next Section