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

Using global and session variables

As you have seen in the previous chapters, you can set the parameters by connecting to MySQL and executing the SET command.

There are two types of variables based on the scope of the variable:

  • Global: Applies to all the new connections
  • Session: Applies only to the current connection (session)

How to do it...

For example, if you want to log all queries that are slower than one second, you can execute:

mysql> SET GLOBAL long_query_time = 1;

To make the changes persistent across restarts use:

mysql> SET PERSIST long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)

Or:

mysql> SET @@persist.long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

The persisted global system variable settings are stored in mysqld-auto.cnf which is located in data directory.

Suppose you want to log queries only for this session and not for all the connections. You can use the following command:

mysql> SET SESSION long_query_time = 1;
Previous Section
End of Section 4
Next Section