Advanced MySQL 8

4.8 (4 reviews total)
By Eric Vanier , Birju Shah , Tejaswi Malepati
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Introduction

About 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.

Publication date:
January 2019
Publisher
Packt
Pages
286
ISBN
9781788834445

 

Introduction

MySQL is the most popular open- source database system on the market and has been around since 1995. Oracle has owned MySQL since 2010, and MySQL is distributed in two versions: The Community Edition (open source), and the Enterprise Edition, which you must pay for and of course it comes with a series of enhanced features not available in the free version.

It should be mentioned that the Community Edition version (open source) is more popular than the Enterprise version, and can be downloaded from the MySQL website (https://www.mysql.com/). This edition has all the features you need for safe web applications that are reliable and secure. Websites such as Google, Wikipedia, Facebook, YouTube, and so on, are some of the biggest users of MySQL.

I'm curious whether you know what MySQL means.

MySQL is a combination of My (the name of the daughter of co-founder Michael Widenius) and SQL stands for Structured Query Language.

This book is an excellent choice if you want to learn advanced concepts at the performance level. Also, you will learn how to analyze and optimize complex queries with different techniques. You will learn to configure and optimize MySQL servers and then I will teach you how to secure your database, using back-up strategies.

We will also go through all the new features of MySQL 8.0 including the Group Replication and InnoDB cluster. Together, we will look at the best surveillance tools that exist on the market today. Throughout this book, I will share my experiences and techniques to help you better manage MySQL in its everyday management and to have a better understanding of its complex and broad architectures. Finally, I will share some techniques with you, which will allow you to make more efficient MySQL 8.0 databases and which I use with Fortune 500 customers.

In this chapter, we will cover the following topics:

  • Why Oracle decided to go with MySQL 8.0
  • Why MySQL 8.0 is the next generation
  • Why it is so important to have a good architecture design
  • A review of the chapter
 

Why MySQL 8?

In short, Oracle has decided to create a new version of MySQL with the number 8 simply because MySQL 6 already existed in 2007 but was then abandoned.

So why isn't is called MySQL 7, then? Because Oracle already has a version 7, called MySQL Cluster, and this version is 7.5 (GA). So, the next number that is available is 8. At the moment, MySQL version 8.0 is in DMR, which means it is in development and not available for production. However, I encourage you to download and test the new features.

 

Why is MySQL 8.0 the next generation?

As you can guess, MySQL 8.0 will be the next generation of databases because it will include the traditional side of SQL and NoSQL with JSON. The new features are not just those that I just mentioned; you will also find the following:

  • Common table expression (CTE): A type of temporary table associated with a query that will allow you to use the WITH command for recursion.
  • Invisible indexes: These are invisible to the MySQL optimizer; that is, they are maintained by the optimizer but not used by it. This has the big advantage of being able to test the behavior of the MySQL 8.0 server during evaluation and not the index.
    • Oracle has also added to MySQL 8.0 what are called persisting configuration variables, and this new feature has the ability to make persistent changes to the MySQL configuration online.
    • The data dictionary has been optimized, a very good improvement.
    • Great features have been added for replication as well.

Let's not forget the new roles that will facilitate user management—and many other surprises besides.

 

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

It must be said that MySQL is easy to use and its operation is very fast. MySQL requires at first a general knowledge of SQL to work effectively with it. MySQL does not require much more knowledge, but a little knowledge of common relational database management system (RDBMS) is helpful.

However, despite the fact that MySQL comes with a minimal configuration to help your database launch and perform well, MySQL needs more attention to maintain optimal performance, and in general a good understanding of MySQL's configuration becomes more important.

The fact is that the more your database grows with time and demands more hardware resources such as memory, processors, and disk speed, the slower response times will be; this is a sign that your data model need to be revised and adjusted to meet growing and critical demands. The better your data model is at the beginning, the easier it will be to re-adjust your configuration along the way.

The more your business or website grows the more you need a good solid MySQL architecture. During this growth phase, you will notice that you will be asking yourself questions such as the following ones:

  • Do I need to completely rethink my MySQL architecture?
  • Should I start thinking about having a large MySQL infrastructure?
  • Do I need to review my queries?
  • Do I need to create indexes?

Imagine the questions and doubts when we are in the phase where MySQL is likely to under-perform. That's when you desperately start looking at your options on Google and YouTube to see how you can improve your architecture.

This is what I call the critical or gray-zone phase. You will try to make all kinds of changes with the hope of solving your performance problems when the solution was simply to have a good architecture and a logical structure of your data right from the start.

Fortunately, MySQL 8.0 will provide a detailed understanding of high availability with InnoDB Cluster, Global Replication, and all the other features that have been added or improved. I must mention that I have been so far pleasantly surprised by the new performance of MySQL 8.0.

 

Summary

The objective of this book is to progressively give you a detailed understanding of advanced MySQL concepts and how they can be applied to complex situations, such as the following:

  • How to optimize complex queries
  • How to improve your architecture for large databases
  • How to manage complex high availability more effectively
  • The best tips and techniques for complex situations
  • The new features of MySQL 8.0 that can help you make your large databases even more efficient

Do not forget to explore the monitoring tools that let you identify queries that are the slowest and most complex.

I will also share my favorite tips that I use every day to help Fortune 500 companies, and finally I will guide you in this book through the most complex situations that you may face on a daily basis.

About the Authors

  • Eric Vanier

    Eric Vanier is one of the few DBAs worldwide specializing in MySQL performance, and is an expert with large architectures such as those for Air Miles, Cogeco, McGill University, Bombardier Aerospace, and many more. As a senior DBA and instructor for 18 years, he has a track record in performance and troubleshooting on dozens of high-profile MySQL systems, including complex cluster environments.

    Browse publications by this author
  • Birju Shah

    Birju Shah is the principal architect for Endurance International Group. He has a bachelor's degree in computer engineering from Gujarat University. He has the experience and expertise to build scalable products for hosting domains. He is passionate about the latest architectural patterns, tools, and technologies, and he helps organizations to follow best practices. He is also passionate about technical training and sessions.

    Browse publications by this author
  • Tejaswi Malepati

    Tejaswi Malepati is the Cassandra Tech Lead for Target. He has been instrumental in designing and building custom Cassandra integrations, including a web-based SQL interface and data validation frameworks between Oracle and Cassandra. Tejaswi earned a master's degree in computer science from the University of New Mexico, and a bachelor's degree in electronics and communication from Jawaharlal Nehru Technological University in India. He is passionate about identifying and analyzing data patterns in datasets using R, Python, Spark, Cassandra, and MySQL.

    Browse publications by this author

Latest Reviews

(4 reviews total)
Good books with examples.
Nice book that's all ....
The book is good, very updated and good explanations

Recommended For You

Advanced MySQL 8
Unlock this book and the full library for $5 a month*
Start now