MySQL 8 Cookbook

Design and administer enterprise-grade MySQL 8 solutions
Preview in Mapt
Code Files

MySQL 8 Cookbook

Karthik Appigatla

1 customer reviews
Design and administer enterprise-grade MySQL 8 solutions
Mapt Subscription
FREE
$29.99/m after trial
eBook
$20.00
RRP $39.99
Save 49%
Print + eBook
$49.99
RRP $49.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$20.00
$49.99
$29.99 p/m after trial
RRP $39.99
RRP $49.99
Subscription
eBook
Print + eBook
Start 14 Day Trial

Frequently bought together


MySQL 8 Cookbook Book Cover
MySQL 8 Cookbook
$ 39.99
$ 20.00
Mastering PostgreSQL 10 Book Cover
Mastering PostgreSQL 10
$ 31.99
$ 16.00
Buy 2 for $33.50
Save $38.48
Add to Cart

Book Details

ISBN 139781788395809
Paperback446 pages

Book Description

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.

Table of Contents

Chapter 1: 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
Chapter 2: 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
Joining tables
Stored procedures
Functions
Triggers
Views
Events
Getting information about databases and tables
Chapter 3: Using MySQL (Advanced)
Introduction
Using JSON
Common table expressions (CTE)
Generated columns
Window functions
Chapter 4: Configuring MySQL
Introduction
Using config file
Using global and session variables
Using parameters with startup script
Configuring the parameters
Changing the data directory
Chapter 5: Transactions
Introduction
Performing transactions
Using savepoints
Isolation levels
Locking
Chapter 6: 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
Chapter 7: 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
Chapter 8: Restoring Data
Introduction
Recovering from mysqldump and mysqlpump
Recovering from mydumper using myloader
Recovering from flat file backup
Performing point-in-time recovery
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: Managing Logs
Introduction
Managing the error log
Managing the general query log and slow query log
Managing the binary logs
Chapter 13: 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
Chapter 14: 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

What You Will Learn

  • Install and configure your MySQL 8 instance without any hassle
  • Get to grips with new features of MySQL 8 like CTE, Window functions and many more
  • Perform backup tasks, recover data and set up various replication topologies for your database
  • Maximize performance by using new features of MySQL 8 like descending indexes, controlling query optimizer and resource groups
  • Learn how to use general table space to suit the SaaS or multi-tenant applications
  • Analyze slow queries using performance schema, sys schema and third party tools
  • Manage and monitor your MySQL instance and implement efficient performance-tuning tasks

Authors

Table of Contents

Chapter 1: 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
Chapter 2: 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
Joining tables
Stored procedures
Functions
Triggers
Views
Events
Getting information about databases and tables
Chapter 3: Using MySQL (Advanced)
Introduction
Using JSON
Common table expressions (CTE)
Generated columns
Window functions
Chapter 4: Configuring MySQL
Introduction
Using config file
Using global and session variables
Using parameters with startup script
Configuring the parameters
Changing the data directory
Chapter 5: Transactions
Introduction
Performing transactions
Using savepoints
Isolation levels
Locking
Chapter 6: 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
Chapter 7: 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
Chapter 8: Restoring Data
Introduction
Recovering from mysqldump and mysqlpump
Recovering from mydumper using myloader
Recovering from flat file backup
Performing point-in-time recovery
Chapter 9: 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
Chapter 10: 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
Chapter 11: 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
Chapter 12: Managing Logs
Introduction
Managing the error log
Managing the general query log and slow query log
Managing the binary logs
Chapter 13: 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
Chapter 14: 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

Book Details

ISBN 139781788395809
Paperback446 pages
Read More
From 1 reviews

Read More Reviews

Recommended for You

Mastering PostgreSQL 10 Book Cover
Mastering PostgreSQL 10
$ 31.99
$ 16.00
MySQL 8 for Big Data Book Cover
MySQL 8 for Big Data
$ 35.99
$ 18.00
Nginx HTTP Server - Fourth Edition Book Cover
Nginx HTTP Server - Fourth Edition
$ 35.99
$ 18.00
Cloud Native Development Patterns and Best Practices Book Cover
Cloud Native Development Patterns and Best Practices
$ 35.99
$ 18.00
Django RESTful Web Services Book Cover
Django RESTful Web Services
$ 31.99
$ 16.00
Microservice Patterns and Best Practices Book Cover
Microservice Patterns and Best Practices
$ 35.99
$ 18.00