Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying MySQL 8 Cookbook
  • Table Of Contents Toc
  • Feedback & Rating feedback
MySQL 8 Cookbook

MySQL 8 Cookbook

By : Karthik Appigatla
3.5 (4)
Buy this Book
close
close
MySQL 8 Cookbook

MySQL 8 Cookbook

3.5 (4)
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.
Table of Contents (15 chapters)
close
close
close
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Sections
Get in touch
Free Chapter
1
MySQL 8 - Installing and Upgrading
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
2
Using MySQL
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
3
Using MySQL (Advanced)
Using MySQL (Advanced)
Introduction
Using JSON
Common table expressions (CTE)
Generated columns
Window functions
4
Configuring MySQL
Configuring MySQL
Introduction
Using config file
Using global and session variables
Using parameters with startup script
Configuring the parameters
Changing the data directory
5
Transactions
Transactions
Introduction
Performing transactions
Using savepoints
Isolation levels
Locking
6
Binary Logging
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
7
Backups
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
8
Restoring Data
Restoring Data
Introduction
Recovering from mysqldump and mysqlpump
Recovering from mydumper using myloader
Recovering from flat file backup
Performing point-in-time recovery
9
Replication
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
10
Table Maintenance
chevron up
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
11
Managing Tablespace
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
12
Managing Logs
Managing Logs
Introduction
Managing the error log
Managing the general query log and slow query log
Managing the binary logs
13
Performance Tuning
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
14
Security
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

Archiving tables

Sometimes, you do not want to keep older data and wish to delete it. If you want to delete all the rows which were last accessed over a month ago, if the table is small (<10k rows), you can straight away use the following:

DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL -1 MONTH)

What happens if the table is big? You know InnoDB creates an UNDO log to restore failed transactions. So all the deleted rows are saved in the UNDO log space to be used to restore in case the DELETE statement aborts in between. Unfortunately, if the DELETE statement is aborted in between, InnoDB copies the rows from the UNDO log space to table, which can make the table inaccessible.

To overcome this behavior, you can LIMIT the number of rows deleted and COMMIT the transaction, running the same thing in a loop until all the unwanted rows are deleted.

This is an example pseudo code:

WHILE count<=0:
    DELETE FROM <TABLE> WHERE last_accessed<DATE_ADD(NOW(), INTERVAL...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 7
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY