MySQL 8 administrator's guide : effective guide to administering high-performance MySQL 8 solutions / Chintan Mehta [and three others].

Format:

Book

Author/Creator:

Mehta, Chintan, author.

Language:

English

Subjects (All):

MySQL (Electronic resource).

SQL (Computer program language).

Physical Description:

1 online resource (510 pages)

Edition:

1st edition

Other Title:

My Structured Query Language eight administrator's guide

Place of Publication:

Birmingham, England ; Mumbai, [India] : Packt, 2018.

System Details:

text file

Summary:

Step by step guide to monitor, manage, and secure your database engine About This Book Your companion to master all the administration-related tasks in MySQL 8 Ensure high performance and high availability of your MySQL solution using effective replication and backup techniques A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Who This Book Is For This book is intended for MySQL administrators who are looking for a handy guide covering all the MySQL administration-related tasks. If you are a DBA looking to get started with MySQL administration, this book will also help you. Knowledge of the basic database concepts is required to get started with this book. What You Will Learn Understanding different MySQL 8 data types based on type of contents and storage requirements Best practices for optimal use of features in MySQL 8 Explore globalization configuration and caching techniques to improve performance Create custom storage engine as per system requirements Learn various ways of index implementation for flash memory storages Configure and implement replication along with approaches to use replication as solution Understand how to make your MySQL 8 solution highly available Troubleshoot common issues and identify error codes while using MySQL 8 In Detail MySQL is one of the most popular and widely used relational databases in the world today. The recently released version 8.0 brings along some major advancements in the way your MySQL solution can be administered. This handbook will be your companion to understand the newly introduced features in MySQL and how you can leverage them to design a high-performance MySQL solution for your organization. This book starts with a brief introduction to the newly introduced features in MySQL 8, followed by quickly jumping onto the crucial administration topics that you will find useful in your day to day work. Topics such as migrating to MySQL 8, MySQL benchmarking, achieving high performance by implementing the indexing techniques, and optimizing your queries are covered in this book. You will also learn how to perform replication, scale your MySQL solution and implement effective security techniques. A special section on the common and not so common troubleshooting techniques for effective MySQL administration is also covered in this book. By the end of this highly practical book, you will have all the knowledge you need to tackle an...

Contents:

Cover

Copyright and Credits

Packt Upsell

Contributors

Table of Contents

Preface

Chapter 1: An Introduction to MYSQL 8

Overview of MySQL

MySQL as a relational database management system

License requirements of MySQL8

Reliability and scalability

Platform compatibility

Releases

Core features in MySQL

Structured database

Database storage engines and types

Overview of InnoDB

Overview of MyISAM

Overview of memory

Overview of archive

Overview of BLACKHOLE as a storage engine

Overview of CSV

Overview of merge

Overview of federated

Overview of the NDB cluster

Improved features in MySQL 8

Transactional data dictionary

Roles

InnoDB auto increment

Invisible indexes

Improving descending indexes

The SET PERSIST variant

Expanded GIS support

Default character set

Extended bit-wise operations

InnoDB Memcached

NOWAIT and SKIP LOCKED

JSON

Cloud

Resource management

Benefits of using MySQL 8

Security

Scalability

An open source relational database management system

High performance

High availability

Cross-platform capabilities

Limitations of MySQL 8

Number of tables or databases

Table size

Joins

Windows platform

Table column count

Row size

InnoDB storage engine

Limitations of InnoDB storage engine

Restrictions

Data dictionary

Limitations of group replication in MySQL8

Limitations of partitioning

Constructs prohibition

Operators

Tables

Use cases of MySQL

Social media

Government

Media and entertainment

Fraud detection

Business mapping

E-commerce

Summary

Chapter 2: Installing and Upgrading MySQL 8

The MySQL 8 installation process

General installation guide

Downloading MySQL 8

Verifying the package integrity

Using MD5 checksums.

Using cryptographic signatures

Installing MySQL 8 on Microsoft Windows

Windows-specific considerations

MySQL 8 installation layout

Choosing the right installation package

The MySQL 8 installer

Initial setup information

Installation workflow

InnoDB cluster sandbox test setup

Server configuration

MySQL installer product catalog and dashboard

MySQL installer console

MySQL 8 installation using a ZIP file

Installing MySQL 8 on Linux

Installation using the Yum repository

Installation using the RPM package

Installation using the Debian package

Post-installation setup for MySQL 8

Data directory initialization

Securing the initial MySQL account

Starting and troubleshooting MySQL 8 services

Executing commands to test the server

Upgrading MySQL 8

Upgrading methods

In-place upgrade of MySQL

Logical upgrade for MySQL 8

Upgrading prerequisites for MySQL 5.7

MySQL 8 downgrading

Downgrading methods

Logical downgrade

Manual changes required before downgrading

Chapter 3: MySQL 8 - Using Programs and Utilities

Overview of MySQL 8 programs

MySQL programs in brief

Startup programs

Installation/upgradation programs

Client programs

Administrative and utilities programs

Environment variables

MySQL GUI tool

MySQL 8 command-line programs

Executing programs from the command line

Executing MySQL programs

Connecting to the MySQL server

Specifying options for programs

Options on the command line

Modifying program options

Modifying options with files

group

opt_name

opt_name=value

Include directives

Command-line options affecting option file handling

Setting program variables with options

Setting environment variables

Server and server-startup programs

mysqld - the MySQL server program

Options.

mysqld_safe - MySQL server startup script

mysql.server - MySQL Server startup script

mysqld_multi - managing multiple MySQL servers

Installation programs

comp_err - compiling the MySQL error msg file

mysql_secure_installation - improving MySQL installation security

mysql_ssl_rsa_setup - creating SSL/RSA files

mysql_tzinfo_to_sql - loading the timezone tables

mysql_upgrade - checking and upgrading MySQL tables

MySQL 8 client programs

mysql - the command-line tool

mysql options

mysql commands

help [arg], \h [arg],\? [arg], ? [arg]

charset charset_name, \C charset_name

clear, \c

connect [db_name host_name], \r [db_name host_name]

edit, \e

exit, \q

prompt [str], \R [str]

quit, \q

status, \s

use db_name, \u db_name

mysql logging

mysql server-side help

Executing sql from text files

mysqladmin - client for administering a MySQL server

mysqlcheck - a table maintenance program

mysqldump - a database backup program

Performance and scalability

mysqlimport - a data import program

mysqlpump - a database backup program

mysqlsh - the MySQL Shell

mysqlshow - showing database, table, and column information

mysqlslap - load emulation client

MySQL 8 administrative programs

ibdsdi - InnoDB tablespace SDI extraction utility

innochecksum - offline InnoDB file checksum utility

myisam_ftdump - displaying full-text index utility

myisamchk - MyISAM table-maintenance utility

myisamlog - displaying MyISAM log file content

myisampack - generating compressed, read-only MyISAM tables

mysql_config_editor - MySQL configuration utility

mysqlbinlog - utility for processing binary log files

mysqldumpslow - summarizing slow query log files.

MySQL 8 environment variables

MySQL GUI tools

MySQL Workbench

MySQL Notifier

MySQL Notifier usage.

Summary

Chapter 4: MySQL 8 Data Types

Overview of MySQL 8 data types

Numeric data types

Integer types

Fixed point types

Floating point types

Problems with floating point values

Bit value type

Bit value literals

Practical uses of BIT

Type attributes

Overflow handling

Date and time data types

DATE, DATETIME, and TIMESTAMP types

MySQL DATETIME functions

TIME type

Time functions

YEAR type

Migrating YEAR(2) to YEAR(4)

String data types

CHAR and VARCHAR data types

BINARY and VARBINARY data types

BLOB and TEXT data types

ENUM data type

SET data type

JSON data type

Partial updates of JSON values

Storage requirements for data types

Choosing the right data type for column

Chapter 5: MySQL 8 Database Management

MySQL 8 server administration

Server options and different types of variables

Server SQL modes

Setting the SQL mode

The available SQL modes

Combination SQL modes

Strict SQL mode

The IGNORE keyword

IPv6 support

Server side help

The server shutdown process

Data directory

The system database

Data dictionary tables

Grant system tables

Object information system tables

Log system tables

The server-side help system tables

Time zone system tables

Replication system tables

Optimizer system tables

Other miscellaneous system tables

Running multiple instances on a single machine

Setting up multiple data directories

Running multiple MySQL instances on Windows

Components and plugin management

MySQL 8 server plugins

Installing the plugins

Activate plugin

Uninstall plugin

Getting information about the installed plugins

Roles and permissions

Caching techniques

Globalization

Character sets

Character set support

Adding the character set.

Configuring the character sets

Language selection

Time zone settings for MySQL8

Locale support

MySQL 8 server logs

The error log

Component configuration

Default error log destination configuration

Default error log destination on Windows

Default error log destination on Unix and Unix-Like systems

The general query log

The binary log

The slow query log

The DDL log

Server log maintenance

Chapter 6: MySQL 8 Storage Engines

Overview of storage engines

MySQL storage engine architecture

Several types of storage engine

Overview of the InnoDB storage engine

Custom storage engine

Several types of storage engines

Pluggable storage engine architecture

The common database server layer

Setting the storage engine

The MyISAM storage engine

The MEMORY storage engine

The CSV storage engine

The ARCHIVE storage engine

The BLACKHOLE storage engine

The MERGE storage engine

The FEDERATED storage engine

The EXAMPLE storage engine

The InnoDB storage engine

ACID model

Multiversioning

Architecture

Locking and transaction model

Configuration

Tablespaces

Tables and indexes

INFORMATION_SCHEMA tables

Memcached plugin

Creating a custom storage engine

Creating storage engine source files

Adding engine-specific variables and parameters

Creating the handlerton

Handling handler installation

Defining filename extensions

Creating tables

Opening a table

Implementing basic table scanning

Closing a table

Reference for advanced custom storage engine

Chapter 7: Indexing in MySQL 8

An overview on indexing

Uses of indexes in MySQL 8

SQL commands related to indexes

Creating an INDEX command

Spatial index characteristics

Non-spatial index characteristics

Drop index command.

SPATIAL index creation and optimization.

Notes:

Includes index.

Description based on online resource; title from PDF title page (EBC, viewed March 8, 2018).

OCLC:

1024270453