MySQL 8 Cookbook

MySQL 8 Cookbook

By : Karthik Appigatla

Buy this Book

MySQL 8 Cookbook

By: Karthik Appigatla

Buy this Book

Overview of this book

MySQL is one of the most popular and widely used relational databases in the World today. The recently released MySQL 8 version promises to be better and more efficient than ever before. This book contains everything you need to know to be the go-to person in your organization when it comes to MySQL. Starting with a quick installation and configuration of your MySQL instance, the book quickly jumps into the querying aspects of MySQL. It shows you the newest improvements in MySQL 8 and gives you hands-on experience in managing high-transaction and real-time datasets. If you've already worked with MySQL before and are looking to migrate your application to MySQL 8, this book will also show you how to do that. The book also contains recipes on efficient MySQL administration, with tips on effective user management, data recovery, security, database monitoring, performance tuning, troubleshooting, and more. With quick solutions to common and not-so-common problems you might encounter while working with MySQL 8, the book contains practical tips and tricks to give you the edge over others in designing, developing, and administering your database effectively.

Title Page

Dedication

Packt Upsell

Contributors

Preface

Free Chapter

MySQL 8 - Installing and Upgrading

Introduction

Installing MySQL using YUM/APT

Installing MySQL 8.0 using RPM or DEB files

Installing MySQL on Linux using Generic Binaries

Starting or Stopping MySQL 8

Uninstalling MySQL 8

Managing the MySQL Server with systemd

Downgrading from MySQL 8.0

Upgrading to MySQL 8.0

Installing MySQL utilities

Using MySQL

Introduction

Connecting to MySQL using the command-line client

Creating databases

Creating tables

Inserting, updating, and deleting rows

Loading sample data

Selecting data

Sorting results

Grouping results (aggregate functions)

Creating users

Granting and revoking access to users

Selecting data into a file and table

Loading data into a table

Views

Events

Getting information about databases and tables

Using MySQL (Advanced)

Introduction

Using JSON

Common table expressions (CTE)

Configuring MySQL

Using global and session variables

Using parameters with startup script

Configuring the parameters

Changing the data directory

Transactions

Introduction

Performing transactions

Using savepoints

Isolation levels

Locking

Binary Logging

Introduction

Using binary logging

Binary log format

Extracting statements from a binary log

Ignoring databases to write to a binary log

Relocating binary logs

Backups

Introduction

Taking backups using mysqldump

Taking backups using mysqlpump

Taking backups using mydumper

Taking backups using flat files

Taking backups using XtraBackup

Locking instances for backup

Binary log backup

Restoring Data

Introduction

Recovering from mysqldump and mysqlpump

Recovering from mydumper using myloader

Recovering from flat file backup

Performing point-in-time recovery

Replication

Introduction

Setting up replication

Setting up master-master replication

Setting up multi-source replication

Setting up replication filters

Switching slave from master-slave to chain replication

Switching the slave from chain replication to master-slave

Setting up delayed replication

Setting up GTID replication

Setting up semi-synchronous replication

Table Maintenance

Introduction

Installing Percona Toolkit

Altering tables

Moving tables across databases

Altering tables using an online schema change tool

Archiving tables

Cloning tables

Partitioning tables

Partition pruning and selection

Partition management

Partition information

Efficiently managing time to live and soft delete rows

Managing Tablespace

Introduction

Changing the number or size of InnoDB redo log files

Resizing the InnoDB system tablespace

Creating file-per-table tablespaces outside the data directory

Copying file-per-table tablespaces to another instance

Managing UNDO tablespace

Managing general tablespace

Compressing InnoDB tables

Managing Logs

Introduction

Managing the error log

Managing the general query log and slow query log

Managing the binary logs

Performance Tuning

Introduction

The explain plan

Benchmarking queries and the server

Adding indexes

Invisible index

Descending index

Analyzing slow queries using pt-query-digest

Optimizing datatypes

Removing duplicate and redundant indexes

Checking index usage

Controlling the query optimizer

Using index hints

Indexing for JSON using generated columns

Using resource groups

Using performance_schema

Using the sys schema

Security

Introduction

Securing installation

Restricting networks and users

Password-less authentication using mysql_config_editor

Resetting the root password

Setting up encrypted connections using X509

Setting up SSL replication

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Downgrading from MySQL 8.0

If your application is not performing as expected, you can always downgrade to a previous GA release (MySQL 5.7). Before downgrading, it is recommended to take a logical backup (refer to Chapter 7, Backups). Note that you can downgrade by only one previous release. Suppose that you want to downgrade from MySQL 8.0 to MySQL 5.6, you have to downgrade to MySQL 5.7, and then from MySQL 5.7 to MySQL 5.6.

You can do it in two ways:

In-place downgrade (downgrades within MySQL 8)
Logical downgrade

How to do it...

In the following subsections, you will be learning how to handle the installation/uninstallation/upgrade/downgrade using various repositories, bundles, and so on.

In-place Downgrades

For downgrades between the GA status releases within MySQL 8.0 (note that you cannot downgrade to MySQL 5.7 using this method):

Shut down the old MySQL version
Replace the MySQL 8.0 binaries or older binaries
Restart MySQL on the existing data directory
Run the mysql_upgrade utility

Using YUM repositories

Prepare MySQL for a slow shutdown, which ensures that the undo logs are empty and data files are fully prepared in case of file format differences between releases:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Shut down the mysql server as described in the Stopping MySQL 8.0 Server section:

shell> sudo systemctl stop mysqld

Remove the InnoDB redo log files (the ib_logfile* files) from the data directory to avoid downgrade issues related to redo log file format changes that may have occurred between releases:

shell> sudo rm -rf /var/lib/mysql/ib_logfile*

Downgrade MySQL. To downgrade the server, you need to uninstall MySQL 8.0, as described in the Uninstalling MySQL 8 section. The configuration files are automatically stored as backup. List the available versions:

shell> sudo yum list mysql-community-server

Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

Install the older version:

shell> sudo yum install -y mysql-community-server-<version>

Using APT Repositories

Reconfigure MySQL and choose the older version:

shell> sudo dpkg-reconfigure mysql-apt-config

Run apt-get update:

shell> sudo apt-get update

Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y

shell> sudo apt-get autoremove

Install the older version (autoselected since you have reconfigured):

shell> sudo apt-get install -y mysql-server

Using the RPM or APT bundle

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from the MySQL Downloads (refer to the Installing MySQL 8.0 using RPMs or DEB files section).

Using Generic Binaries

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux Using Generic Binaries section):

Start the server as described in the Starting or Stopping MySQL 8 section. Please note that the start procedure is the same for all the versions.
Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

Restart the MySQL server to ensure that any changes made to the system tables take effect:

shell> sudo systemctl restart mysqld

Logical Downgrades

Here is an outline of the steps:

Export existing data from the MySQL 8.0 version using logical backup (refer to Chapter 7, Backups for logical backup methods)
Install MySQL 5.7
Load the dump file into the MySQL 5.7 version (refer to Chapter 8, Restoring Data for restoring methods)
Run the mysql_upgrade utility

Here are the detailed steps:

You need to take logical backup of the database. (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > mysql80.sql

Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
Move the data directory. Instead of restoring the SQL backup (in step 1), we can move back the data directory if you want to keep MySQL 8:

shell> sudo mv /var/lib/mysql /var/lib/mysql80

Downgrade MySQL. To downgrade the server, we need to uninstall MySQL 8. The configuration files are automatically backed up.

Using YUM Repositories

After the uninstallation, install the older version:

Switch the repositories:

shell> sudo yum-config-manager --disable mysql80-community
shell> sudo yum-config-manager --enable mysql57-community

Verify that mysql57-community is enabled:

shell> yum repolist enabled | grep "mysql.*-community.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community                 42
!mysql-tools-community/x86_64      MySQL Tools Community                      53
!mysql57-community/x86_64          MySQL 5.7 Community Server                227

Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

List the available versions:

shell> sudo yum list mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.rackspace.com
 * epel: mirrors.develooper.com
 * extras: centos.s.uw.edu
 * updates: mirrors.syringanetworks.net
Available Packages
mysql-community-server.x86_64   5.7.20-1.el7                         mysql57-community

Install MySQL 5.7:

shell> sudo yum install -y mysql-community-server

Using APT Repositories

Reconfigure apt to switch to MySQL 5.7:

shell> sudo dpkg-reconfigure mysql-apt-config

Run apt-get update:

shell> sudo apt-get update

Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y
shell> sudo apt-get autoremove

Install MySQL 5.7:

shell> sudo apt-get install -y mysql-server

Using RPM or APT bundles

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded fromMySQL Downloads (refer to the Installing MySQL 8 using RPM or DEB files section).

Using Generic Binaries

Once you have downgraded MySQL, you have to restore the backup and run the mysql_upgrade utility:

Start MySQL (refer to the Starting or Stopping MySQL 8 section). You need to reset the password again.
Restore the backup (this may take a long time, depending up on the size of backup). Refer to Chapter 8, Restoring Data, for a quick restoration method called myloader:

shell> mysql -u root -p < mysql80.sql

Run mysql_upgrade:

shell> mysql_upgrade -u root -p

Restart the MySQL server to ensure that any changes made to the system tables take effect. Refer to the Starting or Stopping MySQL 8 section:

shell> sudo /etc/init.d/mysql restart

MySQL 8 Cookbook

By : Karthik Appigatla

MySQL 8 Cookbook

By: Karthik Appigatla

Overview of this book

Related Content you might be interested in

Current Title:

MySQL 8 Cookbook

MySQL 8 Administrator's Guide

Advanced MySQL 8

MySQL 8 for Big Data

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