MySQL 8 Cookbook电子书

Title Page

Copyright and Credits

MySQL 8 Cookbook

Dedication

Packt Upsell

Why subscribe?

PacktPub.com

Contributors

About the author

About the reviewers

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Conventions used

Sections

Getting ready

How to do it…

How it works…

There's more…

See also

Get in touch

Reviews

MySQL 8 - Installing and Upgrading

Introduction

Installing MySQL using YUM/APT

How to do it...

Using YUM repositories

Using APT repositories

Installing MySQL 8.0 using RPM or DEB files

How to do it...

Using the RPM bundle

Using the APT bundle

Installing MySQL on Linux using Generic Binaries

How to do it...

There's more...

Starting or Stopping MySQL 8

How to do it...

Starting the MySQL 8.0 server

Stopping the MySQL 8.0 server

Checking the status of the MySQL 8.0 server

Uninstalling MySQL 8

How to do it...

On YUM-based systems

On APT-based systems

Uninstalling Binaries

Managing the MySQL Server with systemd

How to do it...

Downgrading from MySQL 8.0

How to do it...

In-place Downgrades

Using YUM repositories

Using APT Repositories

Using the RPM or APT bundle

Using Generic Binaries

Logical Downgrades

Using YUM Repositories

Using APT Repositories

Using RPM or APT bundles

Using Generic Binaries

Upgrading to MySQL 8.0

Getting ready

How to do it...

In-place upgrades

YUM-based systems

APT-based systems

Using RPM or APT bundles

Using Generic Binaries

Logical Upgrades

Installing MySQL utilities

How to do it...

On YUM-based systems

On APT-based systems

Using MySQL

Introduction

Connecting to MySQL using the command-line client

Getting ready

How to do it...

See also

Creating databases

How to do it...

See also

Creating tables

How to do it...

Cloning table structure

See also

Inserting, updating, and deleting rows

How to do it...

Inserting

Updating

Deleting

REPLACE, INSERT, ON DUPLICATE KEY UPDATE

Truncating tables

Loading sample data

How to do it...

Selecting data

How to do it...

Selecting columns

Count

Filter based on condition

Operators

Simple pattern matching

Regular expressions

Limiting results

Using the table alias

Sorting results

How to do it...

Grouping results (aggregate functions)

How to do it...

COUNT

SUM

AVERAGE

DISTINCT

Filtering using HAVING

See also

Creating users

How to do it...

See also

Granting and revoking access to users

How to do it...

Granting privileges

Checking grants

Revoking grants

Modifying the mysql.user table

Setting password expiry for users

Locking users

Creating roles for users

Selecting data into a file and table

How to do it...

Saving as a file

Saving as a table

Loading data into a table

How to do it...

Joining tables

How to do it...

Identifying Duplicates using SELF JOIN

Using SUB queries

Finding mismatched rows between tables

Stored procedures

How to do it...

There's more...

See also

Functions

How to do it...

Inbuilt functions

See also

Triggers

How to do it...

See also

Views

How to do it...

Events

How to do it...

Access control

See also

Getting information about databases and tables

How to do it...

TABLES

COLUMNS

FILES

INNODB_SYS_TABLESPACES

INNODB_TABLESTATS

PROCESSLIST

See also

Using MySQL (Advanced)

Introduction

Using JSON

How to do it...

Insert JSON

Retrieve JSON

JSON functions

Pretty view

Searching

Modifying

Removing

Other functions

See also

Common table expressions (CTE)

How to do it...

Non-recursive CTE

Recursive CTE

Generated columns

How to do it...

Window functions

How to do it...

Row number

Partition results

Named windows

First, last, and nth values

Configuring MySQL

Introduction

Using config file

How to do it...

Using global and session variables

How to do it...

Using parameters with startup script

How to do it...

Configuring the parameters

How to do it...

data directory

innodb_buffer_pool_size

innodb_buffer_pool_instances

innodb_log_file_size

Changing the data directory

How to do it...

Transactions

Introduction

Performing transactions

How to do it...

Autocommit

Using savepoints

How to do it...

Isolation levels

How to do it...

Read uncommitted

Read committed

Repeatable read

Serializable

Locking

How to do it...

Locking queue

Binary Logging

Introduction

Using binary logging

How to do it...

Enabling binary logs

Disabling binary logs for a session

Move to the next log

Expire binary logs

Binary log format

How to do it...

See also

Extracting statements from a binary log

Getting ready

How to do it...

Observations

Extracting based on time and position

Extracting based on the database

Extracting a row event display

Rewriting a database name

Disabling a binary log for recovery

Displaying events in a binary log file

Ignoring databases to write to a binary log

How to do it...

Example 1

Example 2

Relocating binary logs

How to do it...

Backups

Introduction

Taking backups using mysqldump

How to do it...

Full backup of all databases

Point-in-time recovery

Dumping master binary coordinates

Specific databases and tables

Ignore tables

Specific rows

Backup from a remote server

Backup to rebuild another server with a different schema

Only schema and no data

Only data and no schema

Backup for merging data with other server

REPLACE with new data

IGNORE data

Taking backups using mysqlpump

How to do it...

Parallel processing

Exclude/include database objects using regex

Backup users

Compressed backups

Faster reload

Taking backups using mydumper

How to do it...

Installation

Full backup

Consistent backup

Backup of a single table

Backup of specific databases using regex

Taking backup of a big table using mydumper

Non-blocking backup

Compressed backups

Backing up only data

Taking backups using flat files

How to do it...

Taking backups using XtraBackup

How to do it...

Installation

On CentOS/Red Hat/Fedora

On Debian/Ubuntu

Locking instances for backup

How to do it...

Binary log backup

How to do it...

Restoring Data

Introduction

Recovering from mysqldump and mysqlpump

How to do it...

There's more...

Recovering from mydumper using myloader

How to do it...

Recovering full database

Recover a single database

Recovering a single table

Recovering from flat file backup

How to do it...

Performing point-in-time recovery

How to do it...

mysqldump or mysqlpump

mydumper

Replication

Introduction

Setting up replication

How to do it...

Setting up master-master replication

How to do it...

Setting up multi-source replication

How to do it...

Setting up replication filters

How to do it...

Replicate a database only

Replicate specific tables

Ignore a database

Ignore specific tables

See also

Switching slave from master-slave to chain replication

How to do it...

Switching the slave from chain replication to master-slave

How to do it...

Setting up delayed replication

How to do it...

Setting up GTID replication

How to do it...

Setting up semi-synchronous replication

How to do it...

Table Maintenance

Introduction

Installing Percona Toolkit

How to do it...

On Debian/Ubuntu

On CentOS/Red Hat/Fedora

Altering tables

How to do it...

Moving tables across databases

How to do it...

Altering tables using an online schema change tool

How it works...

How to do it...

Archiving tables

How to do it...

Purging data

Archiving data

Copying data

See also

Cloning tables

How to do it...

Partitioning tables

How to do it...

RANGE partitioning

Removing partitioning

RANGE COLUMNS partitioning

LIST and LIST COLUMNS partitioning

HASH and LINEAR HASH partitioning

KEY and LINEAR KEY partitioning

Subpartitioning

Partition pruning and selection

How to do it...

Partition pruning

Partition selection

Partition management

How to do it...

ADD partitions

Reorganizing partitions

DROP partitions

TRUNCATE partitions

Managing HASH and KEY partitions

Other operations

Partition information

How to do it...

Using SHOW CREATE TABLE

Using SHOW TABLE STATUS

Using EXPLAIN

Querying the INFORMATION_SCHEMA.PARTITIONS table

Efficiently managing time to live and soft delete rows

How it works...

How to do it...

Managing Tablespace

Introduction

Changing the number or size of InnoDB redo log files

How to do it...

Resizing the InnoDB system tablespace

How to do it...

Increasing the InnoDB system tablespace

Shrinking the InnoDB system tablespace

Creating file-per-table tablespaces outside the data directory

How to do it...

Copying file-per-table tablespaces to another instance

How to do it...

Copy full table

Copying individual partitions of a table

See also

Managing UNDO tablespace

How to do it...

Managing general tablespace

How to do it...

Create a general tablespace

Adding tables to a general tablespace

Moving non-partitioned tables between tablespaces

Managing partitioned tables in a general tablespace

Dropping general tablespace

Compressing InnoDB tables

How to do it...

Enabling Compression for file_per_table Tables

Disabling Compression for file_per_table Tables

Enabling Compression for General Tablespace

Managing Logs

Introduction

Managing the error log

How to do it...

Configuring the error log

Rotating the error log

Using the system log for logging

Error logging in JSON format

Managing the general query log and slow query log

How to do it...

General query log

Slow query log

Selecting query log output destinations

Managing the binary logs

How to do it...

Performance Tuning

Introduction

The explain plan

How to do it...

Using EXPLAIN

Using EXPLAIN JSON

Using EXPLAIN for connection

Benchmarking queries and the server

How to do it...

Adding indexes

Primary key (clustered index) and secondary indexes

How to do it...

Add index

UNIQUE index

Prefix index

Drop index

Index on generated columns

Invisible index

How to do it...

Descending index

How to do it...

Analyzing slow queries using pt-query-digest

How to do it...

Slow query log

General query log

Process list

Binary log

TCP dump

See also

Optimizing datatypes

How to do it...

Removing duplicate and redundant indexes

How to do it...

pt-duplicate-key-checker

mysqlindexcheck

Checking index usage

How to do it...

Controlling the query optimizer

How to do it...

optimizer_search_depth

How to know that the query is spending time in evaluating plans?

optimizer_switch

Optimizer hints

Adjusting the optimizer cost model

Using index hints

How to do it...

Indexing for JSON using generated columns

How to do it...

Using resource groups

How to do it...

Alter and drop resource group

Using performance_schema

How to do it...

Enable/disable performance_schema

Enable/disable consumers and instruments

performance_schema tables

Using the sys schema

How to do it...

Statement by type (INSERT and SELECT) from each host

Statement by type from each user

Redundant indexes

Unused indexes

Statements executed from each host

Table statistics

Table statistics with buffer

Statement analysis

Security

Introduction

Securing installation

How to do it...

The FILE privilege

Restricting networks and users

How to do it...

Password-less authentication using mysql_config_editor

How to do it...

Resetting the root password

How to do it...

Using init-file

Using --skip-grant-tables

Setting up encrypted connections using X509

How to do it...

Setting up SSL replication

How to do it...