Book Image

MySQL 8 Cookbook

By : Karthik Appigatla
Book Image

MySQL 8 Cookbook

By: Karthik Appigatla

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.

Related Content you might be interested in

Current Title:

Small book image
MySQL 8 Cookbook
Karthik Appigatla
Jan, 2018 | 446 pages
Small book image
MySQL 8 Administrator's Guide
Hetal Oza | Chintan Mehta | Ankit K Bhavsar | Subhash Shah
Feb, 2018 | 510 pages
Small book image
Advanced MySQL 8
Eric Vanier | Tejaswi Malepati | Birju Shah
Jan, 2019 | 286 pages
Small book image
MySQL 8 for Big Data
Chintan Mehta | Shabbir Challawala | Jaydip Lakhatariya | Kandarp Patel
Oct, 2017 | 296 pages
Table of Contents (20 chapters)
Title Page
Title Page
Dedication
Dedication
Packt Upsell
Packt Upsell
Contributors
Contributors
Preface
Preface
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
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
Index
Index

Contributors

About the author

Karthik Appigatla is a highly reputed database architect and is very famous for performance tuning. He has been consulted by many companies all over the world for designing, performance tuning, building database infrastructure, and training. In his decade of experience, he has worked for companies such as Yahoo, Pythian, and Percona. Currently, he is working for LinkedIn, where he has innovated a new way of analyzing queries. He gave a talk about this at SRECon, Dublin in 2017.

I would like to acknowledge the encouragement from my wife, Lalitha, and my brother, Kashyap. This book would not have completed without the cooperation of my little daughter, Samhita.

About the reviewers

Marco Ippolito is an Italian software engineer working as Director of Software Development for Imagining IT. Marco completed his postgraduate in Software Engineering in Oxford and has worked for large corporations such as Intel, HP, Google, Dell, and Oracle (in the acquired MySQL team), as well as for start-ups such as @platformsh and for large production users of MySQL such as @bookingcom (Twitter). He can be reached at [email protected] and has experience working in teams speaking Italian, English, Spanish, Brazilian Portuguese, German, and  French, remotely or onsite.

 

 

Kedar Mohaniraj Vaijanapurkar is a MySQL database consultant with over a decade of experience, ranging from programming to database administration. He aims to spread happiness with the MySQL database systems he works on. Apart from working with MySQL and related open source technologies, he also explores cloud, automation, and NoSQL. He is a 5-year Pythian laureate living with his awesome family in the cultured city of Vadodara, India. You can reach him at [email protected].

 

 

Packt is searching for authors like you

If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.

 

Previous Section
Next Chapter