Advanced MySQL 8

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

Eric Vanier, Birju Shah, Tejaswi Malepati

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781788833790
• Edition: 1
• Topic: Informatique
• Subtopic: Traitement des données

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.

InnoDB Cluster in MySQL 8.0

In the previous chapter, we learned about MySQL Group Replication. We discussed Group Replication internals and also looked at its step-by-step configuration. We went through all of the important MySQL variables that can be used to fine-tune the performance of Group Replication. In this chapter, we shall focus on MySQL InnoDB, concentrating on the following topics:

• What InnoDB cluster is
• InnoDB cluster requirements
• Creating a MySQL InnoDB cluster
• Routers in InnoDB cluster
• Managing InnoDB cluster
• InnoDB cluster limitations
• Storage engines

We highly recommend that you refer to Chapter 7, Group Replication in MySQL 8.0, before proceeding with this chapter, to have a thorough understanding of Group Replication.

What is InnoDB cluster?

InnoDB cluster is a complete, high-availability solution provided by MySQL. InnoDB cluster uses a Group Replication mechanism at its heart to replicate data; on top of that, InnoDB cluster provides a built-in failover mechanism, as illustrated in the following diagram:

To ensure high-availability, a seamless failover mechanism and the ability to load-balance are essential. MySQL provides this with MySQL Router. The router is an important component in InnoDB cluster but is a separate component that needs to be downloaded and installed. The router acts as middleware that provides routing between clients and servers. InnoDB cluster can operate in multi-primary mode or single-primary mode. In the case of primary server failure, applications do not need to manage the failover logic, as it is automatically taken care of by MySQL Router. It acts as lightweight middleware that routes READWRITE and READONLY transactions, as well as appropriately routing data requests to either primary or slave servers. This functionality can help to achieve scalability at the MySQL level.

MySQL Router should be installed near to applications or on the same host as applications. After installation, MySQL Router fetches the metadata or configuration from InnoDB cluster and caches it locally. If the availability of servers on InnoDB cluster changes, router updates its local data to reflect any changes where necessary.

Application connections that are shut down are disconnected from the MySQL server and then moved to a closed state. Note that applications are required to reconnect, to re-establish the connection, as the router will not take care of such a failover.

MySQL Router 8.0 supports both MySQL 5.7 and MySQL 8.0. If you are using an older version, such as 2.0 or 2.1, you must upgrade to MySQL Router 8.0.

MySQL Shell is an advanced client and code editor for MySQL server. You can install MySQL Shell separately. Refer to the official documentation of MySQL Shell for more information.

In addition to existing SQL functionality, MySQL Shell provides scripting capabilities for JavaScript and Python. Shell also uses X DevAPI, which is helpful for developers who want to use MySQL as a document store or RDBMS. Shell also includes X AdminAPI for architects who want to manage MySQL InnoDB cluster and do not want to configure and manage everything manually.

By using X AdminAPI, we can do the following:

• Configure, create, and check the status of groups
• Configure, add members, and remove members
• Create sandbox environments for testing

Shell also provides us with a way to configure colors, to customize the look and feel of an application.

Let's now deep dive into these programs and explore which APIs will be useful for us going forward.

By now, you should have downloaded the latest versions of MySQL server, MySQL Shell, and MySQL Router. Let's first try to configure a sandbox environment before going through the production setup. We'll start with identifying cluster requirements.

InnoDB cluster requirements

InnoDB cluster uses Group Replication to replicate data across various machines, so we need to make sure that our server instances meet the minimum requirements necessary; they are as follows:

• Python Version 2.7 or higher is required for MySQL Shell to work, and is required for all machines that are part of a group; for Windows, MySQL Shell bundles Python
• MySQL Router, MySQL Shell, and MySQL server should already be downloaded and installed on the servers before configuring InnoDB cluster
• InnoDB must be used as a storage engine for all tables

Please refer to Chapter 7, Group Replication in MySQL 8.0, for more information on the preceding requirements.

Let's now configure a testing scenario to understand these components and how they work.

Installing MySQL Shell

First, download the zip file for MySQL Shell for Windows (Windows X86, 64-bit) from the official MySQL website, http://dev.mysql.com/downloads/shell/. Extract it, and you should see the mysqlsh.exe file in the bin folder. Configure the environment and path variables so that they can access mysqlsh.exe from the command prompt.

For Linux distributions, either the MySQL APT repository or the MySQL Yum repository is required. Make sure you select MySQL 8.0 as the release. Please follow the official MySQL documentation for more details.

You can install MySQL Shell with the following command:

sudo apt-get update
sudo apt-get install mysql-shell

If you already have a MySQL APT Repository configuration, use the following commands:

> sudo apt-get update
> sudo apt-get install mysql-apt-config

Make sure you choose MySQL 8.0 as the release series before running the following command:

> sudo apt-get install mysql-shell

For Yum repositories, use the following command. Configure the MySQL Yum repository with the new release package, mysql80-community-release. Once complete, run the following command:

> sudo yum install mysql-shell

How to use ...