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

Installing MySQL 8.0 using RPM or DEB files

Installing MySQL using repositories requires access to public internet. As a security measure, most of the production machines are not connected to the internet. In that case, you can download the RPM or DEB files on the system administrationand copy them to the production machine.

There are mainly two types of installation files:

RPM (CentOS, Red Hat, Fedora, and Oracle Linux)
DEB (Debian, Ubuntu)

There are multiple packages that you need to install. Here is a list and short description of each one:

mysql-community-server: Database server and related tools.
mysql-community-client: MySQL client applications and tools.
mysql-community-common: Common files for server and client libraries.
mysql-community-devel: Development header files and libraries for MySQL database client applications, such as the Perl MySQL module.
mysql-community-libs: The shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL.
mysql-community-libs-compat: The shared libraries for older releases. Install this package if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.

How to do it...

Let's look at how to do it using the following types of bundles:

Using the RPM bundle

Download the MySQL RPM tar bundle from the MySQL Downloads page, http://dev.mysql.com/downloads/mysql/, choosing your OS and CPU architecture. At the time of writing, MySQL 8.0 is not GA. If it is still in the development series, select the Development Releases tab for getting MySQL 8.0 and the choose the OS and version:

shell> wget 'https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar'
~
Saving to: ‘mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar’
~

Untar the package:

shell> tar xfv mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar

Install MySQL:

shell> sudo rpm -i mysql-community-{server-8,client,common,libs}*

RPM cannot solve the dependency issues and the installation process might run issues. If you are facing such issues, use the yum command listed here (you should have access to dependent packages):

shell> sudo yum install mysql-community-{server-8,client,common,libs}* -y

Verify the installation:

shell> rpm -qa | grep -i mysql-community
mysql-community-common-8.0.3-0.1.rc.el7.x86_64
mysql-community-libs-compat-8.0.3-0.1.rc.el7.x86_64
mysql-community-libs-8.0.3-0.1.rc.el7.x86_64
mysql-community-server-8.0.3-0.1.rc.el7.x86_64
mysql-community-client-8.0.3-0.1.rc.el7.x86_64

Using the APT bundle

Download the MySQL APT TAR from the MySQL Downloads page, http://dev.mysql.com/downloads/mysql/:

shell> wget "https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar"
~
Saving to: ‘mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar’
~

Untar the packages:

shell> tar -xvf mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar

Install the dependencies. You may need to install the libaio1 package if it is not already installed:

shell> sudo apt-get install -y libaio1

Upgrade libstdc++6 to the latest:

shell> sudo add-apt-repository ppa:ubuntu-toolchain-r/test
shell> sudo apt-get update
shell> sudo apt-get upgrade -y libstdc++6

Upgrade libmecab2 to the latest. If universe is not included, then add the following line to the end of the file (for example, zesty):

shell> sudo vi /etc/apt/sources.list
deb http://us.archive.ubuntu.com/ubuntu zesty main universe

shell> sudo apt-get update
shell> sudo apt-get install libmecab2

Preconfigure the MySQL server package with the following command. It asks you to set the root password:

shell> sudo dpkg-preconfigure mysql-community-server_*.deb

Install the database common files package, the client package, the client metapackage, the server package, and the server metapackage (in that order); you can do that with a single command:

shell> sudo dpkg -i mysql-{common,community-client-core,community-client,client,community-server-core,community-server,server}_*.deb

Install the shared libraries:

shell> sudo dpkg -i libmysqlclient21_8.0.1-dmr-1ubuntu16.10_amd64.deb

Verify the installation:

shell> dpkg -l | grep -i mysql
ii  mysql-client                8.0.3-rc-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version
ii  mysql-common                8.0.3-rc-1ubuntu14.04 amd64 MySQL Common
ii  mysql-community-client      8.0.3-rc-1ubuntu14.04 amd64 MySQL Client
ii  mysql-community-client-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client Core Binaries
ii  mysql-community-server      8.0.3-rc-1ubuntu14.04 amd64 MySQL Server
ii  mysql-community-server-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Server Core Binaires
ii  mysql-server                8.0.3-rc-1ubuntu16.04 amd64 MySQL Server meta package depending on latest version

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

Installing MySQL 8.0 using RPM or DEB files

How to do it...

Using the RPM bundle

Using the APT bundle