31,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Lebensstil
- Sprache: Englisch
- Veröffentlichungsjahr: 2018
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 show you how you can leverage them to design a high-performance MySQL solution for your organization.
This book starts with a brief introduction to the new features in MySQL 8, and then 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. There is also 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 any problem you might encounter while administering your MySQL solution.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 682
Ähnliche
MySQL 8 Administrator's Guide
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Amey VarangaonkarAcquisition Editor: Aman SinghContent Development Editor: Aaryaman singhTechnical Editor: Dharmendra YadavCopy Editors: Safis EditingProject Coordinator: Manthan PatelProofreader: Safis EditingIndexer: Tejal Daruwale SoniGraphics: Tania DuttaProduction Coordinator: Aparna Bhagat
First published: February 2018
Production reference: 1140218
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78839-519-9
www.packtpub.com
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the authors
Chintan Mehta is a cofounder of KNOWARTH Technologies (www.knowarth.com) and heads cloud/RIMS/DevOps. He has rich, progressive experience in server administration of Linux, AWS cloud, DevOps, RIMS, and open source technologies. He is an AWS Certified Solutions Architect.
He has authored MySQL 8 for Big Data and Hadoop Backup and Recovery Solutions, and has reviewed Liferay Portal Performance Best Practices and Building Serverless Web Applications.
Ankit Bhavsar is a senior consultant leading a team working on ERP solutions at KNOWARTH Technologies. He received an MCA from North Gujarat university. He has had dynamic roles in the development and maintenance of ERP solutions and astrology portals Content Management that including OOP, technical architecture analysis, design, development as well as database design, development and enhancement process, data and object modeling, in order to provide technical and business solutions to clients.
Hetal Oza an MCA from a reputable institute of India, is working as a lead consultant at KNOWARTH Technologies. She has rich experience in Java-based systems with various databases. Her 10 years of experience covers all stages of software development. She has worked on development of web-based software solutions on various platforms. She has good exposure to integration projects with web-service-based and thread-based architecture. Her knowledge is not bound to any single field because she has worked on wide range of technologies and tools.
Subhash Shah works as a principal consultant at KNOWARTH Technologies. He holds a degree in information technology from a HNGU. He is experienced in developing web-based solutions using various software platforms. He is a strong advocate of open source software development and its use by businesses to reduce risks and costs. His interests include designing sustainable software solutions. His technical skills include requirement analysis, architecture design, project delivery, application setup, and execution processes. He is an admirer of quality code and test-driven development.
About the reviewers
Sahaj Pathak has been involved with backend technologies such as Java, Spring, Hibernate, and databases (MySQL, PostgreSQL, Oracle, and others). His experience also spans frontend technologies (HTML4/5, jQuery, AngularJS, Node.Js, JavaScript, and CSS2/3). He has speedy versatility with any technology and a sharp desire for consistent change.
He works at KNOWARTH Technologies as a software consultant where he deals with big enterprise-product-based projects.
Ravi Shah is a highly versatile IT professional with more than 5 years of experience of handling high-end IT projects, with competencies in conceptualizing and supporting critical IT frameworks and applications. He is a team player, a software engineer with a can-do attitude, and possesses phenomenal time management skills, and strong user focus. He has developed several web applications and mainly specializes in healthcare and insurance.
He is skilled in all phases of software development, an expert in translating business requirements into technical solutions, and devoted to quality, usability, security and scalability. His expertise mainly includes Liferay, Java, Spring, Struts, Hibernate, MySQL, Lucene, Angular, and Agile.
He is a good trainer delivering training on J2EE and the Liferay portal in his organization.
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.
Table of Contents
Title Page
Copyright and Credits
MySQL 8 Administrator's Guide
Packt Upsell
Why subscribe?
PacktPub.com
Contributors
About the authors
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Reviews
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
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
Summary
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 –
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
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
Summary
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
Summary
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
Summary
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
InnoDB and MyISAM index statistics collection
Column-level indexing
Column indexes
Index prefixes
FULLTEXT indexes
Spatial Indexes
Indexes in the MEMORY storage engine
Multiple-column indexes
B-Tree index
Hash index
Index extension
Using an optimizer for indexes
Invisible and descending indexes
Invisible index
Descending index
Summary
Replication in MySQL 8
Overview of replication
What is MySQL replication?
Advantages of MySQL replication
Configuring replication
Binary log file based replication
Replication master configuration
REPLICATION SLAVE configuration
Adding slaves to replication
Global transaction identifiers based replication
MySQL multi-source replication
Replication administration tasks
Implementing replication
Replication formats
Statement-based versus row-based replication
Replication implementation details
Replication channels
Replication relay and status logs
Evaluating replication filtering rules
Group replication
Primary-secondary replication versus group replication
Group replication configuration
Group replication use cases
Replication solutions
Summary
Partitioning in MySQL 8
Overview of partitioning
Types of partitioning
Partitioning management
Partition selection and pruning
Restrictions and limitations in partitioning
Types of partitioning
RANGE partitioning
LIST partitioning
COLUMNS partitioning
RANGE COLUMN partitioning
LIST COLUMN partitioning
HASH partitioning
LINEAR HASH partitioning
KEY partitioning
Subpartitioning
Handling NULL in partitioning
Partition management
RANGE and LIST partition management
HASH and KEY partition management
Partition maintenance
Obtain partition information
Partition selection and pruning
Partition pruning
Partition selection
Restrictions and limitations in partitioning
Partitioning keys, primary keys, and unique keys
Partitioning limitations relating to storage engines
Partitioning limitations relating to functions
Summary
MySQL 8 – Scalability and High Availability
Overview of scalability and high availability in MySQL 8
MySQL replication
MySQL cluster
Oracle MySQL cloud service
MySQL with the Solaris cluster
Scaling MySQL 8
Scaling using cluster
Client node
Application node
Management node
Data node
Data storage and management of disk-based and in-memory data
Automatic and user-defined partitioning of tables or sharding of tables
Synchronous data replication between data nodes
Data retrieval and transactions
Automatic fail over
Automatic re-synchronization for self-healing after failure
Scaling using memcached in MySQL 8
NoSQL APIs
Scaling using replication
Single server dependancy
Performance
Backup and recovery
Load distribution
Asynchronous data replication
Geographical data distribution
GTID replication
ZFS replication
Challenges in scaling MySQL 8
Business type and flexibility
Understand server workload
Read-write operation limit
Maintenance
Master server failure
Synchronization
Database security
Cross node transaction
Growing team for development
Manage change request
Scale-up and scale-out
Achieving high availability
Purpose of high availability
Data availability
Security of data
Synchronization of data
Backup of the data
Competitive market
Performance
Updates in the system
Choosing the solution
Advantages of high availability
Summary
MySQL 8 – Security
Overview of security for MySQL 8
Common security issues
General guidelines
Guidelines for a secure password
Guidelines for end users
Guidelines for administrators
Password and logging
Secure MYSQL 8 against attackers
Security options and variables provided by MySQL 8
Security guidelines for client programming
Access control in MySQL 8
Privileges provided by MySQL 8
Grant tables
Verification of access control stages
Stage 1 - Connection verification
Stage 2 - Request verification
Account management in MySQL 8
Add and remove user accounts
Security using roles
SET ROLE
CREATE ROLE
DROP ROLE
GRANT
REVOKE
SET DEFAULT ROLE
SHOW GRANTS
Password management
Encryption in MySQL 8
Configuring MySQL 8 to use encrypted connections
Server-side configuration for encrypted connections
Client-side configuration for encrypted connections
Command options for encrypted connections
Connect with MySQL 8 remotely from Windows with SSH
Security plugins
Authentication plugins
SHA-2 pluggable authentication
Client-side cleartext pluggable authentication
No-login pluggable authentication
Socket peer-credential pluggable authentication
Test pluggable authentication
The connection-control plugins
CONNECTION_CONTROL
Plugin installation
Variables related to CONNECTION-CONTROL
The password validation plugin
Install password validation plugin
Variables and options related to the password validation plugin
MySQL 8 keyring
Install keyring plugin
System variables related to keyring plugin
Summary
Optimizing MySQL 8
Overview of MySQL 8 optimization
Optimizing the database
Optimizing the hardware
Optimizing MySQL 8 servers and clients
Optimizing disk I/O
Using NFS with MySQL
Optimizing the use of memory
Optimizing use of the network
Optimizing locking operations
Performance benchmarking
Examining thread information
Optimizing database structure
Optimizing data size
Table columns
Row format
Indexes
Joins
Normalization
Optimizing MySQL data types
Optimizing for many tables
Use of an internal temporary table in MySQL
Optimizing queries
Optimizing SQL statements
Optimizing indexes
Query execution plan
Optimizing tables
Optimization for InnoDB tables
Optimization for MyISAM tables
Optimization for MEMORY tables
Leveraging buffering and caching
InnoDB buffer pool optimization
MyISAM key cache
Summary
Extending MySQL 8
An overview of extending MySQL 8
MySQL 8 internals
MySQL 8 plugin API
MySQL 8 services for components and plugins
Adding new functions to MySQL 8
Debugging and porting MySQL 8
Extending plugins and using services to call them
Writing plugins
Component and plugin services
The locking service
The keyring service
Adding new functions
Features of a user-defined function interface
Adding a new user-defined function
Adding a new native function
Debugging and porting
Debugging MySQL server
Debugging MySQL client
The DBUG package
Summary
MySQL 8 Best Practices and Benchmarking
MySQL benchmarking and tools
Resource utilization
Stretching your benchmarking timelines
Replicating production settings
Consistency of throughput and latency
Sysbench can do more
Virtualization world
Concurrency
Hidden workloads
Nerves of your query
Benchmarks
Best practices for memcached
Resource allocation
Operating system architecture
Default configurations
Max object size
Backlog queue limit
Large pages support
Sensitive data
Restricting exposure
Failover
Namespaces
Caching mechanism
Memcached general statistics
Best practices for replication
Throughput in group replication
Infrastructure sizing
Constant throughput
Contradictory workloads
Write scalability
Best practices for data partitioning
Horizontal partitioning
Vertical partitioning
Pruning partitions in MySQL
Best practices for queries and indexing
Data types
Not null
Indexing
Search fields index
Data types and joins
Compound index
Shortening up primary keys
Indexing everything
Fetching all data
Letting the application do the job
Existence of data
Limiting yourself
Analyzing slow queries
Query cost
Summary
Troubleshooting MySQL 8
MySQL 8 common problems
Most common MySQL errors
Access denied
Can't connect to [local] MySQL server
Lost connection to MySQL server
Password fails when entered incorrectly
Host host_name is blocked
Too many connections
Out of memory
Packet too large
The table is full
Can't create/write to file
Commands out of sync
Ignoring user
Table tbl_name doesn't exist
MySQL 8 server errors
Issues with file permissions
Resetting the root password
MySQL crashes prevention
Handling MySQL full disk
MySQL temporary files storage
MySQL Unix socket file
Time zone problems
MySQL 8 client errors
Case sensitivity in string searches
Problems with DATE columns
Problems with NULL values
MySQL 8 troubleshooting approach
Analyzing queries
Real-world scenario
Summary
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
For any system, it is must to manage data in an organized manner. In a large-scale system, it is necessary to handle various configurations for security purposes. MySQL is one of the popular solutions used to handle enterprise-level applications. In this book, we will explain how to configure users, their roles, multiple instances, and much more.
Many organizations use MySQL for their websites or commercial products, and it's very challenging for them to manage data storage and analyze data in accordance with the business requirements. This book will show you how to implement indexing and query optimization for better performance. Along with this, we'll cover how scalability and high availability of the MySQL server can help to manage failure scenarios. In addition to that, replication and partitioning concepts are explained in detail with examples.
The book describes various features of MySQL 8 by targeting different levels of users, from beginners to database administrators. This book starts from the installation with a basic understanding of MySQL 8's concepts. The we proceed to administrative-level features with configuration. At the end of the book, you will have learned about very interesting functionalities, such as optimization, extension, and troubleshooting.
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 basic database concepts is required to get started with this book.
What this book covers
Chapter 1, An Introduction to MySQL 8, serves as an introductory guide to MySQL 8. It briefly defines the core features available in MySQL and newly introduced or enhanced features of MySQL 8. In the later part of the chapter, we highlight the benefits of MySQL 8 along with real-world applications.
Chapter 2, Installing and Upgrading MySQL 8, describes detailed steps for installing MySQL 8 on different platforms. It also explains how to upgrade to or downgrade from MySQL 8.
Chapter 3, MySQL 8 – Using Programs and Utilities, introduces command-line programs for the MySQL 8 server and client. It also provides information on the available GUI tools with its configuration.
Chapter 4, MySQL 8 Data Types, focuses on a detailed explanation of MySQL 8 data types. It also explains data type categorization based on the types of content. We cover data types along with their properties in each category. We also cover storage requirements for data types.
Chapter 5, MySQL 8 Database Management, mainly explores the administration part of MySQL 8. This chapter covers components and plugin management, along with user and role management. In addition, it explains globalization configuration, caching techniques, and different types of logs available in MySQL 8.
Chapter 6, MySQL 8 Storage Engines, explains several types of storage engines and details of the InnoDB storage engine. This chapter provides information on custom storage engine creation, along with steps to make it pluggable in installed MySQL 8.
Chapter 7, Indexing in MySQL 8, explains indexing, along with the possible ways of implementing it. It compares types of indexing.
Chapter 8, Replication in MySQL 8, explains replication and the different types of replication available in MySQL 8. It also describes the configuration and implementation of replication along with different approaches.
Chapter 9, Partitioning in MySQL 8, explains the setting of several types of partitioning, selection, and pruning of partitioning. It also explains how to cope up with restrictions and limitations while partitioning.
Chapter 10, MySQL 8 – Scalability and High Availability, explains how to do scaling and how to handle different challenges during implementation. The reader gets an understanding of diverse ways to achieve high availability in MySQL 8.
Chapter 11, MySQL 8 – Security, focuses on MySQL 8 database security. This chapter covers general factors that affect security, the security of core MySQL 8 files, access control, and securing the database system itself. This chapter also includes details of security plugins.
Chapter 12, Optimizing MySQL 8, explains how to configure MySQL 8 for better performance. This chapter also describes use cases with a few performance results to validate. This will help you know various touch points to look out for when dealing with optimizing MySQL 8.
Chapter 13, Extending MySQL 8, shows how to extend MySQL 8 and add new functions, along with debugging and porting to MySQL 8.
Chapter 14, MySQL 8 Best Practices and Benchmarking, explains the best practices of using MySQL. It also explains various benchmarkings done for MySQL 8.
Chapter 15, Troubleshooting MySQL 8, explains many common and real-world scenarios of troubleshooting for MySQL 8.
To get the most out of this book
We recommend that you get some basic knowledge of MySQL (any version) and SQL commands before you start reading this book.
This book also covers practical scenarios and command execution, so if possible, install a tool for easy execution of MySQL commands.
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packtpub.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub athttps://github.com/PacktPublishing/MySQL-8-Administrators-Guide. We also have other code bundles from our rich catalog of books and videos available athttps://github.com/PacktPublishing/. Check them out!
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "It will download winMD5Sum.exe onto your computer."
Any command-line input or output is written as follows:
CREATE TABLE working_days (
year INT,
week INT,
days BIT(7),
PRIMARY KEY (year, week));
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Click on the Download WinMD5Sum option on the page."
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
An Introduction to MYSQL 8
MySQL is a well-known open source structured database because of its performance, easiness to use, and reliability. This is the most common choice of web applications for a relational database. In the current market, thousands of web-based applications rely on MySQL including giant industries such as Facebook, Twitter, and Wikipedia. It has also proven to be the database choice for Software as a Service (SaaS) based applications such as Twitter, YouTube, SugarCRM, Supply Dynamics, Workday, RightNow, Omniture, Zimbra, and many more. We will discuss this in detail in the use cases of MySQL section later in the chapter. MySQL was developed by MySQL AB, a Swedish company, and now it is distributed and supported by Oracle Corporation. MySQL carries a valuable history with it.
MySQL has continued to improve in order to become an enterprise-level database management system. MySQL 8 is expected to be a game-changer as today we are in the age of digitization. MySQL 8 is all tuned to serve many new use cases that in prior versions were difficult to achieve. Some of the use cases an enormous amount of data is produced are social networking, e-commerce, bank/credit card transactions, emails, data stored on the cloud, and so on. Analysis of all such structured, unstructured, or semi-structured ubiquitous data helps to discover hidden patterns, market trends, correlations, personal preferences.
Let's take an in-depth look at MySQL 8 new features, benefits, use cases along with a few limitations of MySQL 8 after we have an overview of MySQL. This is going to be exciting, let's get prepared.
Overview of MySQL
Structured Query Language (SQL) is used to manipulate, retrieve, insert, update, and delete data in relational database management system (RDBMS). To make it simpler, SQL tells the database what to do and exactly what it needs. SQL is a standard language that all RDBMS systems such as MySQL, MS Access, MS SQL, Oracle, Postgres, and others use.
SQL allows users to access data from MySQL and define and manipulate the data. To embed within other languages, you can leverage SQL modules, libraries, and precompilers, which can help you create/drop databases and tables, allow users to create the view, and stored procedures, functions, and so on, in a database. It can do various other operations such as allowing users to set permissions on tables, procedures, and views.
MySQL as a relational database management system
Data in a relational database is stored in an organized format so that information can be retrieved easily. Data will be stored in different tables made up of rows and columns. However, the relationship can also be built between different tables that efficiently store huge data and effectively retrieve the selected data. This provides database operations with tremendous speed and flexibility.
As a relational database, MySQL has capabilities to establish relationships with different tables such as one to many, many to one, and one to one by providing primary keys, foreign keys, and indexes. It can also perform joins between tables to retrieve exact information such as inner joins and outer joins.
SQL is used as an interface to interact with the relational data in MySQL. SQL is an American National Standard Institute (ANSI) standard language which we can operate with data such as creation, deletion, updating, and retrieval.
License requirements of MySQL8
Many industries prefer open source technology because of the technology's flexibility and cost-saving features, while MySQL has put its footprint in the market by becoming the most popular relational database for web applications. Open source means that you can view the source of MySQL and customize it based on your needs without any cost. You can download the source or binary files from its site and use them accordingly.
The MySQL server is covered under the General Public License (GNU), which means that we can freely use it for web applications, study its source code, and modify it to suit our needs. It also has the Enterprise Edition as well with advanced features included. Many enterprises still purchase the support contract from MySQL to get assistance on various issues.
Reliability and scalability
MySQL has great reliability to perform well without requiring extensive troubleshooting due to bottlenecks or other slowdowns. It also incorporates a number of performance enhanced mechanisms such as index support, load utilities, and memory caches. MySQL uses InnoDB as a storage engine, which provides highly efficient ACID compliant transactional capabilities that assure high performance and scalability. To handle the rapidly growing database, MySQL Replication and cluster help scale out the database.
Platform compatibility
MySQL has great cross-platform availability that makes it more popular. It is flexible to run on major platforms such as RedHat, Fedora, Ubuntu, Debian, Solaris, Microsoft Windows, and Apple macOS. It also provides Application Programming Interface (APIs) to interconnect with various programming languages such as C, C++, C#, PHP, Java, Ruby, Python, and Perl.
Releases
Here is a list of major releases of MySQL so far:
Version 5.0 GA was released on 19th October, 2005
Version 5.1 GA was released on 14th November, 2008
Version 5.5 GA was released on 3rd December, 2010
Version 5.6 GA was released on 5th February, 2013
Version 5.7 GA was released on 21st October, 2015
Now it's time for the major version release--MySQL 8--which was announced on 12th September, 2016 and is still in the development milestone mode.
Core features in MySQL
Let's look back and quickly glance through some of the core features in MySQL. We will be discussing various features throughout the book in detail as we progress.
Structured database
Structured databases are traditional databases that have been used by many enterprises for more than 40 years. However, in the modern world, data volume is becoming bigger and bigger and a common need has taken its place--data analytics. Analytics is becoming difficult with structured databases as the volume and velocity of digital data grow faster by the day; we need to find a way to achieve such needs in an effective and efficient way. The most common database that is used as a structured database in the open source world is MySQL.
Many organizations use a structured database to store their data in an organized way with the formatted repository. Basically, data in a structured database has a fixed field, a predefined data length, and defines what kind of data is to be stored such as numbers, dates, time, addresses, currencies, and so on. In short, the structure is already defined before data gets inserted, which gives a clearer idea of what data can reside there. The key advantage of using a structured database is that data being easily stored, queried, and analyzed.
An unstructured database is the opposite of this; it has no identifiable internal structure. It can have a massive unorganized agglomerate or various objects. Mainly, the source of structured data is machine-generated, which means information is generated from the machine and without human intervention, whereas unstructured data is human-generated data. Organizations use structured databases for data such as ATM transactions, airline reservations, inventory systems, and so on. In the same way, some organizations use unstructured data such as emails, multimedia content, word processing documents, web pages, business documents, and so on.
Database storage engines and types
Let's now look at an overview of different MySQL storage engines. This is an important section that gives a brief of different database storage engines; we will be discussing this in detail inChapter 6, MySQL 8 Storage Engines. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables. When you create a table, MySQL stores the table definition in .frm with the same name as the table name. You can use the SHOW TABLE STATUS command to show information about your table:
mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ***************************
Name: admin_user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 3
Create_time: 2017-06-19 14:46:49
Update_time: 2017-06-19 15:15:08
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: Admin User Table
1 row in set (0.00 sec)
This command shows that this is an InnoDB table with the column name Engine. There is additional information that you can refer to for other purposes such as the number of rows, index length, and so on.
The storage engine is the way to handle SQL operations for different table types. Each storage engine has its own advantages and disadvantages. It is important to understand each storage engine's features and choose the most appropriate one for your tables to maximize the performance of the database. InnoDB is the default storage engine when we create a new table in MySQL 8.
The MySQL server uses a plug-and-play storage engine architecture. You can load the required storage engine and unload unnecessary storage engines from the MySQL server with the help of the SHOW ENGINEScommand as follows:
mysql> SHOW ENGINES \G;
*************************** 1. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
Overview of InnoDB
InnoDB is the default storage engine broadly used out of all other available storage engines. It was released with MySQL 5.1 as a plugin in 2008. MySQL 5.5 and later has InnoDB as a default storage engine. It has been taken over by Oracle Corporation in October 2005, from the Innobase Oy, which is a Finland-based company.
InnoDB tables support ACID-compliant commits, rollback, and crash recovery capabilities to protect user data. It also supports row-level locking, which helps with better concurrency and performance. It stores data in clustered indexes to reduce I/O operations for all SQL select queries based on the primary key. It also supports FOREIGN KEY constraints that allow better data integrity for the database. The maximum size of an InnoDB table can scale up to 64 TB, which should be good enough to serve many real-world use cases.
Overview of MyISAM
MyISAM was the default storage engine for MySQLprior to 5.5 1. MyISAM storage engine tables do not support ACID-compliant as opposed to InnoDB. MyISAM tables support table-level locking only, so MyISAM tables are not transaction-safe; however, they are optimized for compression and speed. It is generally used when you need to have primarily read operations with minimal transaction data. The maximum size of a MyISAM table can grow up to 256 TB, which helps in use cases such as data analytics.MyISAM supports full-text indexing, which can help in complex search operations. Using full-text indexes, we can index data stored in BLOB and TEXT data types.
Overview of memory
A memory storage engine is generally known as a heap storage engine. It is used to access data extremely quickly. This storage engine stores data in the RAM so it wouldn't need I/O operation. As it stores data in the RAM, all data is lost upon server restart. This table is basically used for temporary tables or the lookup table. This engine supports table-level locking, which limits high write concurrency.
Important notes about memory tables are as follows:
Because memory table stores data in the RAM, which has a very limited storage capacity; if you try to write too much data into the memory table, it will start swapping data into the disk and then you lose the benefits of the memory storage engine
These tables don't support
TEXT
and
BLOB
data types, and it is not even required as it has limited storage capacity
This storage engine can be used to cache the results; lookup tables, for example, or postal codes and the names of states
Memory tables support B-tree indexes and Hash indexes
Overview of archive
This storage engine is used to store large amounts of historical data without any indexes. Archive tables do not have any storage limitations. The archive storage engine is optimized for high insert operations and also supports row-level locking. These tables store data in a compressed and small format. The archive engine does not support DELETE or UPDATE operations; it only allows INSERT, REPLACE, and SELECT operations.
Overview of BLACKHOLE as a storage engine
This storage engine accepts data but does not store it. It discards data after every INSERTinstead of storing it.
Now, what is the use of this storage engine; why would anybody use it? Why would we run an INSERT query that doesn't insert anything into the table?
This engine is useful for replication with large number of servers. A BLACKHOLE storage engine acts as a filter server between the master and slave server, which do not store any data, but only apply replicate-do-* and replicate-ignore-* rules and write a binlogs. These binlogs are used to perform replication in slave servers. We will discuss this in detail inChapter 8, Replication in MySQL 8.
Overview of CSV
The comma separated values (CSV) engine stores data in the .csv file type using the comma-separated values format. This engine extracts data from the database and copies it to .csv out of the database. If you create a CSV file from the spreadsheet and copy it into the MYSQL data folder server, it can read the data using the select query. Similarly, if you write data in the table, an external program can read it from the CSV file. This storage engine is used for the exchange of data between software or applications. A CSV table does not support indexing and partitioning. All columns in the CSV storage engine need to be defined with the NOT NULL attribute to avoid errors during table creation.
Overview of merge
This storage engine is also known as an MRG_MyISAM storage engine. This storage engine merges a MyISAM table and creates it to be referred to a single view. For a merge table, all columns are listed in the same order. These tables are good for data warehousing environments.
The table is used to manage log-related tables, generally. You can create different months of logs in separate MyISAM tables and merge these tables using the merge storage engine.
MyISAM tables have storage limit for the operating system, but a collection of MyISAM (merge) tables do not have storage limits. So using a merge table would allow you to split data into multiple MyISAM tables, which can help in overcoming storage limits.
Merge tables do not support partitioning. Also, you cannot partition a merge table or any of a merge table's underlying MyISAM tables in a different partition.
Overview of federated
This storage engine allows you to create a single database on a multiple physical server. It opens a client connection to another server and executes queries against a table there, retrieving and sending rows as needed. It was originally marketed as a competitive feature that supported many enterprise-grade proprietary database servers, such as Microsoft SQL Server and Oracle, but that was always a stretch, to say the least. Although it seemed to enable a lot of flexibility and neat tricks, it has proven to be a source of many problems and is disabled by default. This storage engine is disabled by default in MySQL; to enable it, you need to start the MySQL server binary using the federated option.
Overview of the NDB cluster
NDB cluster (also known as NDB) is an in-memory storage engine offering high availability and data persistence features.
The NDB cluster storage engine can be configured with a range of failover and load balancing options, but it is easiest to start with the storage engine at the cluster level. NDB cluster uses the NDB storage engine that contains a complete set of data, which is dependent only on other datasets available within the cluster.
The cluster portion of the NDB cluster is configured independently of the MySQL servers. In an NDB cluster, each part of the cluster is considered to be a node.
Each storage engine has its own advantage and usability, as follows:
Search Engine
:
NDBCluster
Transactions
data
:
InnoDB
Session data
:
MyISAM
or NDBCluster
Localized calculations
:
Memory
Dictionary
:
MyISAM
The following diagram will help you understand which store engine you need to use for your requirement:
Now you have a better idea about various storage engines along with different use cases, which will help you to make a decision based on your needs.
It's time to move on to our next topic where we will look at delightful new features available in MySQL 8.
Improved features in MySQL 8
The MySQL database development team has recently announced its major release as MySQL 8 Development Milestone Release (DMR). It contains significant updates and fixes for problems that were much needed.
You might be wondering why it's 8 after 5.7! Were the intermediate versions, that is, 6 and 7, miss out? Of course not! Actually, 6.0 was preserved as part of the changeover to a more frequent and timely release, while 7.0 for the clustering version of MySQL.
Let's see some exciting features that have been introduced in this latest version, as depicted in the following diagram:
It's time to look at MySQL 8 features in detail, which makes us excited and convinced about the reasons for a major version upgrade of MySQL.
Transactional data dictionary
Up until the previous version, the MySQL data dictionary was stored in different metadata files and non-transactional tables, but from this version onwards, it will have a transactional data dictionary to store the information about the database. No more .frm, .trg, or .par files. All information will be stored in the database, which removes the cost of performing heavy file operations. There were numerous issues with filesystem metadata storage such as the vulnerability of the filesystem, exorbitant file operations, difficult to handle crash recovery failures, or replication; it was also difficult to add new feature-related metadata. Now this upgrade has made it simple by storing information in a centralized manner, and will have improved performance as this data dictionary object can be cached in memory, similar to other database objects.
This data dictionary will have data that is needed for SQL query execution such as catalog information, character sets, collations, column types, indexes, database information, tables, stored procedures, functions and triggers, and so on.
Roles
In MySQL 8, the privileges module has been improved by introducing roles, which means a collection of permissions. Now we can create roles with a number of privileges and assign them to multiple users.
The problem with the previous version was that we were not able to define generic permissions for a group of users and each user has individual privileges. Suppose if there are 1,000 users already existing that have common privileges, and you want to remove the write permissions for these 1,000 users, what would you have done in the previous version? You would have had to take the time-consuming approach of updating each user, right? Arrgh! That's a long, long task.
Now with MySQL 8, it is easy to update any change in privileges. Roles will define all the required privileges and this role will be assigned to those 1,000 users. We just need to make any privilege changes in the role and all users will automatically inherit the respective privileges.
Roles can be created, deleted, grant or revoke permission, grant or revoke from the user account, and can specify the default role within the current session.
InnoDB auto increment
MySQL 8 has changed the auto-increment counter value store mechanism. Previously, it was stored in the memory, which was quite difficult to manage during server restarts or server crashes. However, now the auto-increment counter value is written into the redo log whenever the value gets changed and, on each checkpoint, it will be saved in the system table, which makes it persistent across the server restart.
With the previous version, update of the auto-increment value may have caused duplicate entry errors. Suppose if you updated the value of auto-increment in the middle of the sequence with a larger than the current maximum value, but then subsequent insert operations could not identify the unused values, which could cause a duplicate entry issue. This has been prevented by persisting the auto-increment value, hence subsequent insert operations can get the new value and allocate it properly.
If server restart happened, the auto-increment value was lost with the previous version as it was stored in memory and InnoDB needed to execute a query to find out the maximum used value. This has been changed, as the newer version has the capability to persist its value across the server restart. During the server restart, InnoDB initializes the counter value in memory using the maximum value stored in the data dictionary table. In case of server crashes, InnoDB initializes the auto-increment counter value that is bigger than the data dictionary table and the redo log.
Invisible indexes
MySQL 8 provides you with a feature to make indexes invisible. These kinds of indexes cannot be used by the optimizer. In case you want to test the query performance without indexes, using this feature you can do so by making them invisible rather than dropping and re-adding an index. This is a handy feature when indexing is supposed to be dropped and recreated on huge datasets.
All indexes are visible by default. To make them invisible or visible, INVISIBLE and VISIBLE keywords are used respectively, as described in the following code snippet:
ALTER TABLE table1 ALTER INDEX ix_table1_col1 INVISIBLE;
ALTER TABLE table1 ALTER INDEX ix_table1_col1 VISIBLE;
Improving descending indexes
Descending indexes existed in version 5.7 too, but they were scanned in reverse order, which caused performance barriers. To improve performance, MySQL 8 has optimized this and scanned descending indexes in forward order, which has drastically improved performance. It also brings multiple column indexes for the optimizer when the most efficient scan order has ascending order for some columns, and descending order for other columns.
Expanded GIS support
Until the previous version, it supported only one coordinate system, a unitless 2D place that was not referenced to a position on earth. Now MySQL 8 has added support for a Spatial Reference System (SRS) with geo-referenced ellipsoids and 2D projections. SRS helps assign coordinates to a location and establishes relationships between sets of such coordinates. This spatial data can be managed in data dictionary storage as the ST_SPATIAL_REFERENCE_SYSTEMS table.
Default character set
The default character set has been changed from latin1 to UTF8. UTF8 is the dominating character set, though it hadn't been a default one in previous versions of MySQL. Along with the character set default, collation has been changed from latin1_swedish_ci to utf8mb4_800_ci_ai. With these changes globally accepted, character sets and collations are now based on UTF8; one of the common reasons is because there are around 21 different languages supported by UTF8, which makes systems provide multilingual support.
Extended bit-wise operations
In MySQL 5.7, bit-wise operations and functions were working for BIGINT (64-bit integer) data types only. We needed to pass BIGINT as an argument and it would return the result as BIGINT. In short, it had maximum range up to 64 bits to perform operations. A user needs to do conversion to the BIGINT data type in case they want to perform it on other data types. This typecasting was not feasible for data types larger than 64 bits as it would truncate the actual value, which resulted in inaccuracy.
MySQL 8 has improved bit-wise operations by enabling support for other binary data types such as Binary, VarBinary, and BLOB. This makes it possible to perform bit-wise operations on larger than 64-bit data. No more typecasting needed! This allows the taking of arguments and returning results larger than 64 bits.
InnoDB Memcached
Multiple get operations are now possible with the InnoDB memcached plugin, which will really help in improving the read performance. Now, multiple key value pairs can be fetched in a single memcached query. Frequent communication traffic has also been minimized as we can get multiple data in a single shot.
Range queries are also supported by the InnoDB Memcached plugin. It simplifies range searches by specifying a particular range and retrieves values within this range.
