Cover

Copyright and Credits

Packt Upsell

Contributors

Table of Contents

Preface

Chapter 1: An Introduction to MYSQL 8

Overview of MySQL

MySQL as a relational database management system

License requirements of MySQL8

Reliability and scalability

Platform compatibility

Releases

Core features in MySQL

Structured database

Database storage engines and types

Overview of InnoDB

Overview of MyISAM

Overview of memory

Overview of archive

Overview of BLACKHOLE as a storage engine

Overview of CSV

Overview of merge

Overview of federated

Overview of the NDB cluster

Improved features in MySQL 8

Transactional data dictionary

Roles

InnoDB auto increment

Invisible indexes

Improving descending indexes

The SET PERSIST variant

Expanded GIS support

Default character set

Extended bit-wise operations

InnoDB Memcached

NOWAIT and SKIP LOCKED

JSON

Cloud

Resource management

Benefits of using MySQL 8

Security

Scalability

An open source relational database management system

High performance

High availability

Cross-platform capabilities

Limitations of MySQL 8

Number of tables or databases

Table size

Joins

Windows platform

Table column count

Row size

InnoDB storage engine

Limitations of InnoDB storage engine

Restrictions

Data dictionary

Limitations of group replication in MySQL8

Limitations of partitioning

Constructs prohibition

Operators

Tables

Use cases of MySQL

Social media

Government

Media and entertainment

Fraud detection

Business mapping

E-commerce

Summary

Chapter 2: Installing and Upgrading MySQL 8

The MySQL 8 installation process

General installation guide

Downloading MySQL 8

Verifying the package integrity

Using MD5 checksums

Using cryptographic signatures

Installing MySQL 8 on Microsoft Windows

Windows-specific considerations

MySQL 8 installation layout

Choosing the right installation package

The MySQL 8 installer

Initial setup information

Installation workflow

InnoDB cluster sandbox test setup

Server configuration

MySQL installer product catalog and dashboard

MySQL installer console

MySQL 8 installation using a ZIP file

Installing MySQL 8 on Linux

Installation using the Yum repository

Installation using the RPM package

Installation using the Debian package

Post-installation setup for MySQL 8

Data directory initialization

Securing the initial MySQL account

Starting and troubleshooting MySQL 8 services

Executing commands to test the server

Upgrading MySQL 8

Upgrading methods

In-place upgrade of MySQL

Logical upgrade for MySQL 8

Upgrading prerequisites for MySQL 5.7

MySQL 8 downgrading

Downgrading methods

Logical downgrade

Manual changes required before downgrading

Summary

Chapter 3: MySQL 8 – Using Programs and Utilities

Overview of MySQL 8 programs

MySQL programs in brief

Startup programs

Installation/upgradation programs

Client programs

Administrative and utilities programs 

Environment variables

MySQL GUI tool

MySQL 8 command-line programs

Executing programs from the command line

Executing MySQL programs

Connecting to the MySQL server

Specifying options for programs

Options on the command line

Modifying program options

Modifying options with files

group

opt_name

opt_name=value

Include directives

Command-line options affecting option file handling

Setting program variables with options

Setting environment variables

Server and server-startup programs

mysqld - the MySQL server program

Options

mysqld_safe - MySQL server startup script

mysql.server - MySQL Server startup script

mysqld_multi - managing multiple MySQL servers

Installation programs

comp_err - compiling the MySQL error msg file

mysql_secure_installation - improving MySQL installation security

mysql_ssl_rsa_setup - creating SSL/RSA files

mysql_tzinfo_to_sql - loading the timezone tables

mysql_upgrade - checking and upgrading MySQL tables

MySQL 8 client programs

mysql - the command-line tool

mysql options

mysql commands

help [arg], \h [arg],\? [arg], ? [arg]

charset charset_name, \C charset_name

clear, \c

connect [db_name host_name], \r [db_name host_name]

edit, \e

exit, \q

prompt [str], \R [str]

quit, \q

status, \s

use db_name, \u db_name

mysql logging

mysql server-side help

Executing sql from text files

mysqladmin - client for administering a MySQL server

mysqlcheck - a table maintenance program

mysqldump - a database backup program

Performance and scalability

mysqlimport - a data import program

mysqlpump - a database backup program

mysqlsh - the MySQL Shell

mysqlshow - showing database, table, and column information

mysqlslap - load emulation client

MySQL 8 administrative programs

ibdsdi - InnoDB tablespace SDI extraction utility

innochecksum - offline InnoDB file checksum utility

myisam_ftdump - displaying full-text index utility

myisamchk - MyISAM table-maintenance utility

myisamlog - displaying MyISAM log file content

myisampack - generating compressed, read-only MyISAM tables

mysql_config_editor - MySQL configuration utility

mysqlbinlog - utility for processing binary log files

mysqldumpslow - summarizing slow query log files.

MySQL 8 environment variables

MySQL GUI tools

MySQL Workbench

MySQL Notifier

MySQL Notifier usage

Summary

Chapter 4: MySQL 8 Data Types

Overview of MySQL 8 data types

Numeric data types

Integer types

Fixed point types

Floating point types

Problems with floating point values

Bit value type

Bit value literals

Practical uses of BIT

Type attributes

Overflow handling

Date and time data types

DATE, DATETIME, and TIMESTAMP types

MySQL DATETIME functions

TIME type

Time functions

YEAR type

Migrating YEAR(2) to YEAR(4)

String data types

CHAR and VARCHAR data types

BINARY and VARBINARY data types

BLOB and TEXT data types

ENUM data type

SET data type

JSON data type

Partial updates of JSON values

Storage requirements for data types

Choosing the right data type for column

Summary

Chapter 5: MySQL 8 Database Management

MySQL 8 server administration

Server options and different types of variables

Server SQL modes

Setting the SQL mode

The available SQL modes

Combination SQL modes

Strict SQL mode

The IGNORE keyword

IPv6 support        

Server side help

The server shutdown process 

Data directory

The system database

Data dictionary tables

Grant system tables

Object information system tables

Log system tables

The server-side help system tables

Time zone system tables

Replication system tables

Optimizer system tables

Other miscellaneous system tables

Running multiple instances on a single machine

Setting up multiple data directories

Running multiple MySQL instances on Windows

Components and plugin management

MySQL 8 server plugins

Installing the plugins

Activate plugin

Uninstall plugin

Getting information about the installed plugins

Roles and permissions

Caching techniques

Globalization

Character sets

Character set support

Adding the character set

Configuring the character sets

Language selection

Time zone settings for MySQL8

Locale support

MySQL 8 server logs

The error log

Component configuration

Default error log destination configuration 

Default error log destination on Windows

Default error log destination on Unix and  Unix-Like systems

The general query log

The binary log

The slow query log

The DDL log

Server log maintenance

Summary

Chapter 6: MySQL 8 Storage Engines

Overview of storage engines

MySQL storage engine architecture

Several types of storage engine

Overview of the InnoDB storage engine

Custom storage engine

Several types of storage engines

Pluggable storage engine architecture

The common database server layer

Setting the storage engine

The MyISAM storage engine

The MEMORY storage engine

The CSV storage engine

The ARCHIVE storage engine

The BLACKHOLE storage engine

The MERGE storage engine

The FEDERATED storage engine

The EXAMPLE storage engine

The InnoDB storage engine

ACID model

Multiversioning

Architecture

Locking and transaction model

Configuration

Tablespaces

Tables and indexes

INFORMATION_SCHEMA tables 

Memcached plugin

Creating a custom storage engine

Creating storage engine source files

Adding engine-specific variables and parameters

Creating the handlerton

Handling handler installation

Defining filename extensions

Creating tables

Opening a table

Implementing basic table scanning

Closing a table

Reference for advanced custom storage engine

Summary

Chapter 7: Indexing in MySQL 8

An overview on indexing

Uses of indexes in MySQL 8

SQL commands related to indexes

Creating an INDEX command

Spatial index characteristics

Non-spatial index characteristics

Drop index command

SPATIAL index creation and optimization

InnoDB and MyISAM index statistics collection

Column-level indexing

Column indexes

Index prefixes

FULLTEXT indexes

Spatial Indexes

Indexes in the MEMORY storage engine

Multiple-column indexes

B-Tree index

Hash index

Index extension

Using an optimizer for indexes

Invisible and descending indexes

Invisible index

Descending index

Summary

Chapter 8: Replication in MySQL 8

Overview of replication

What is MySQL replication?

Advantages of MySQL replication

Configuring replication

Binary log file based replication

Replication master configuration

REPLICATION SLAVE configuration

Adding slaves to replication

Global transaction identifiers based replication

MySQL multi-source replication

Replication administration tasks

Implementing replication

Replication formats

Statement-based versus row-based replication

Replication implementation details

Replication channels

Replication relay and status logs

Evaluating replication filtering rules

Group replication

Primary-secondary replication versus group replication

Group replication configuration

Group replication use cases

Replication solutions

Summary

Chapter 9: Partitioning in MySQL 8

Overview of partitioning

Types of partitioning

Partitioning management

Partition selection and pruning

Restrictions and limitations in partitioning

Types of partitioning

RANGE partitioning

LIST partitioning

COLUMNS partitioning

RANGE COLUMN partitioning

LIST COLUMN partitioning

HASH partitioning

LINEAR HASH partitioning

KEY partitioning

Subpartitioning

Handling NULL in partitioning

Partition management

RANGE and LIST partition management

HASH and KEY partition management

Partition maintenance

Obtain partition information

Partition selection and pruning

Partition pruning

Partition selection

Restrictions and limitations in partitioning

Partitioning keys, primary keys, and unique keys

Partitioning limitations relating to storage engines

Partitioning limitations relating to functions

Summary

Chapter 10: MySQL 8 – Scalability and High Availability

Overview of scalability and high availability in MySQL 8

MySQL replication

MySQL cluster

Oracle MySQL cloud service

MySQL with the Solaris cluster

Scaling MySQL 8

Scaling using cluster

Client node

Application node

Management node

Data node

Data storage and management of disk-based and in-memory data

Automatic and user-defined partitioning of tables or sharding of tables

Synchronous data replication between data nodes

Data retrieval and transactions

Automatic fail over

Automatic re-synchronization for self-healing after failure

Scaling using memcached in MySQL 8

NoSQL APIs

Scaling using replication

Single server dependancy

Performance

Backup and recovery

Load distribution

Asynchronous data replication

Geographical data distribution

GTID replication

ZFS replication

Challenges in scaling MySQL 8

Business type and flexibility

Understand server workload

Read-write operation limit

Maintenance

Master server failure

Synchronization

Database security

Cross node transaction

Growing team for development

Manage change request

Scale-up and scale-out

Achieving high availability

Purpose of high availability

Data availability

Security of data

Synchronization of data

Backup of the data

Competitive market

Performance

Updates in the system

Choosing the solution

Advantages of high availability

Summary

Chapter 11: MySQL 8 – Security

Overview of security for MySQL 8

Common security issues

General guidelines

 Guidelines for a secure password

Guidelines for end users

Guidelines for administrators

Password and logging

Secure MYSQL 8 against attackers

Security options and variables provided by MySQL 8

Security guidelines for client programming

Access control in MySQL 8

Privileges provided by MySQL 8

Grant tables

Verification of access control stages

Stage 1 - Connection verification

Stage 2 - Request verification

Account management in MySQL 8

Add and remove user accounts

Security using roles

SET ROLE

CREATE ROLE

DROP ROLE 

GRANT

REVOKE

SET DEFAULT ROLE

SHOW GRANTS

Password management

Encryption in MySQL 8

Configuring MySQL 8 to use encrypted connections

Server-side configuration for encrypted connections

Client-side configuration for encrypted connections

Command options for encrypted connections

Connect with MySQL 8 remotely from Windows with SSH

Security plugins

Authentication plugins

SHA-2 pluggable authentication

Client-side cleartext pluggable authentication

No-login pluggable authentication

Socket peer-credential pluggable authentication

Test pluggable authentication

The connection-control plugins

CONNECTION_CONTROL

Plugin installation

Variables related to CONNECTION-CONTROL

The password validation plugin

Install password validation plugin

Variables and options related to the password validation plugin

MySQL 8 keyring

Install keyring plugin

System variables related to keyring plugin

Summary

Chapter 12: Optimizing MySQL 8

Overview of MySQL 8 optimization

Optimizing the database

Optimizing the hardware

Optimizing MySQL 8 servers and clients

Optimizing disk I/O

Using NFS with MySQL

Optimizing the use of memory

Optimizing use of the network

Optimizing locking operations

Performance benchmarking

Examining thread information

Optimizing database structure

Optimizing data size

Table columns

Row format

Indexes

Joins

Normalization

Optimizing MySQL data types

Optimizing for many tables

Use of an internal temporary table in MySQL

Optimizing queries

Optimizing SQL statements

Optimizing indexes

Query execution plan

Optimizing tables

Optimization for InnoDB tables

Optimization for MyISAM tables

Optimization for MEMORY tables

Leveraging buffering and caching

InnoDB buffer pool optimization

MyISAM key cache

Summary

Chapter 13: Extending MySQL 8

An overview of extending MySQL 8

MySQL 8 internals

MySQL 8 plugin API 

MySQL 8 services for components and plugins

Adding new functions to MySQL 8

Debugging and porting MySQL 8

Extending plugins and using services to call them

Writing plugins

Component and plugin services

The locking service

The keyring service

Adding new functions

Features of a user-defined function interface

Adding a new user-defined function

Adding a new native function

Debugging and porting

Debugging MySQL server

Debugging MySQL client

The DBUG package

Summary

Chapter 14: MySQL 8 Best Practices and Benchmarking

MySQL benchmarking and tools

Resource utilization

Stretching your benchmarking timelines

Replicating production settings

Consistency of throughput and latency

Sysbench can do more

Virtualization world

Concurrency

Hidden workloads

Nerves of your query

Benchmarks

Best practices for memcached

Resource allocation

Operating system architecture

Default configurations

Max object size

Backlog queue limit

Large pages support

Sensitive data

Restricting exposure

Failover

Namespaces

Caching mechanism

Memcached general statistics

Best practices for replication

Throughput in group replication

Infrastructure sizing

Constant throughput

Contradictory workloads

Write scalability

Best practices for data partitioning

Horizontal partitioning

Vertical partitioning

Pruning partitions in MySQL

Best practices for queries and indexing

Data types

Not null

Indexing

Search fields index

Data types and joins

Compound index

Shortening up primary keys

Indexing everything

Fetching all data

Letting the application do the job

Existence of data

Limiting yourself

Analyzing slow queries

Query cost

Summary

Chapter 15: Troubleshooting MySQL 8

MySQL 8 common problems

Most common MySQL errors

Access denied

Can't connect to [local] MySQL server

Lost connection to MySQL server

Password fails when entered incorrectly

Host host_name is blocked

Too many connections

Out of memory

Packet too large

The table is full

Can't create/write to file

Commands out of sync

Ignoring user

Table tbl_name doesn't exist

MySQL 8 server errors

Issues with file permissions

Resetting the root password

MySQL crashes prevention

Handling MySQL full disk

MySQL temporary files storage

MySQL Unix socket file

Time zone problems

MySQL 8 client errors

Case sensitivity in string searches

Problems with DATE columns

Problems with NULL values

MySQL 8 troubleshooting approach

Analyzing queries

Real-world scenario

Summary

Other Books You May Enjoy

Index